private async Task <int> GetNextPatientId()
{
try
{
await conn.OpenAsync();
var cmd = new OleDbCommand
{
Connection = conn,
CommandText = "SELECT MAX(Id) FROM [Patienten$]"
};
var res = await cmd.ExecuteScalarAsync();
var maxId = res == DBNull.Value ? 0 : Convert.ToInt32(res);
return(maxId + 1);
}
catch (Exception ex)
{
throw new Exception($"Error occured while searching next patients id.", ex);
}
finally
{
if (conn.State == System.Data.ConnectionState.Open)
{
conn.Close();
}
}
}
/// <summary>
/// Tests the connection asynchronously.
/// </summary>
/// <returns></returns>
public override async Task TestConnectionAsync()
{
using (var cmd = new OleDbCommand("SELECT 1", m_Connection))
{
cmd.Transaction = m_Transaction;
await cmd.ExecuteScalarAsync();
}
}
internal async Task ReloadAsync(OleDbConnection connection, OleDbTransaction transaction)
{
using (var cmd = new OleDbCommand("SELECT @@Options")
{
Connection = connection, Transaction = transaction
})
m_Options = (int)(await cmd.ExecuteScalarAsync());
}
public async Task <DateTime> GetTimestampAsync()
{
using (await _lock.LockAsync())
{
var command = new OleDbCommand("select v from Timestam", _conn);
return(DateTime.Parse((string)await command.ExecuteScalarAsync()));
}
}
public async Task <string> GetTrackAsync()
{
using (await _lock.LockAsync())
{
var command = new OleDbCommand("select name from Track", _conn);
return((string)await command.ExecuteScalarAsync());
}
}
/// <summary>
/// Tests the connection asynchronously.
/// </summary>
/// <returns></returns>
public override async Task TestConnectionAsync()
{
using (var cmd = new OleDbCommand("SELECT 1", m_Connection))
{
if (m_Transaction != null)
{
cmd.Transaction = m_Transaction;
}
await cmd.ExecuteScalarAsync().ConfigureAwait(false);
}
}
/// <summary>
/// Получение списка выплат и удержаний.
/// </summary>
private async Task <List <Pay> > GetPayAsync(OleDbConnection oleDbConnection, CancellationToken token)
{
token.ThrowIfCancellationRequested();
var sql = $"SELECT Snu_rn, Num, Code, Nick, Name FROM Zsnu";
var result = new List <Pay>();
using (OleDbCommand cmd = new OleDbCommand()
{
CommandText = "SELECT COUNT(*) FROM Zsnu", Connection = oleDbConnection
})
{
AddCount = 0;
int allRecords = Convert.ToInt32(await cmd.ExecuteScalarAsync());
LoadData?.Invoke(this, new Tuple <string, int, bool>("Выплаты и удержания:", allRecords, true));
}
using (OleDbCommand cmd = new OleDbCommand()
{
CommandText = sql, Connection = oleDbConnection
})
{
using (var reader = await cmd.ExecuteReaderAsync(token).ConfigureAwait(false))
{
while (await reader.ReadAsync(token).ConfigureAwait(false))
{
var rn = reader[0].ToString().Trim();
var number = Convert.ToInt32(reader[1]);
var mnemo = reader[2].ToString().Trim();
var abbreviatedName = reader[3].ToString().Trim();
var name = reader[4].ToString().Trim();
result.Add(new Pay()
{
Rn = rn,
Number = number,
Memo = mnemo,
AbbreviatedName = abbreviatedName,
Name = name
});
AddCount++;
ReaderEvent?.Invoke(this, AddCount);
}
}
}
LoadData?.Invoke(this, new Tuple <string, int, bool>(string.Empty, 0, false));
return(result);
}
public static async Task <long> NrRows(string pathToExcel, string SheetName = "[Sheet1$]")
{
using (var m = new OleDbConnection())
{
m.ConnectionString = ExcelHelpers.BuildExcelConnectionString(pathToExcel, false);
await m.OpenAsync();
var query = @"Select count(*) From " + SheetName;
using (var cmd = new OleDbCommand(query, m))
{
var dr = await cmd.ExecuteScalarAsync();
return(long.Parse(dr.ToString()));
}
}
}
/// <summary>
/// Получение Категорий ФОТ.
/// </summary>
private async Task <List <string> > GetPayrollsAsync(OleDbConnection oleDbConnection, CancellationToken token)
{
token.ThrowIfCancellationRequested();
var sql = $"SELECT Code FROM Zkatfzp";
var result = new List <string>();
using (OleDbCommand cmd = new OleDbCommand()
{
CommandText = "SELECT COUNT(*) FROM Zkatfzp", Connection = oleDbConnection
})
{
AddCount = 0;
int allRecords = Convert.ToInt32(await cmd.ExecuteScalarAsync());
LoadData?.Invoke(this, new Tuple <string, int, bool>("Категорий ФОТ:", allRecords, true));
}
using (OleDbCommand cmd = new OleDbCommand()
{
CommandText = sql, Connection = oleDbConnection
})
{
using (var reader = await cmd.ExecuteReaderAsync(token).ConfigureAwait(false))
{
while (await reader.ReadAsync(token).ConfigureAwait(false))
{
var mnemo = reader[0].ToString().Trim();
result.Add(mnemo);
AddCount++;
ReaderEvent?.Invoke(this, AddCount);
}
}
}
LoadData?.Invoke(this, new Tuple <string, int, bool>(string.Empty, 0, false));
return(result);
}
/// <summary>
/// Получение фактически отработанного рабочего времени.
/// </summary>
private async Task <decimal?> GetActualHoursWorked(string fcacRn, int year, Month month, CancellationToken token)
{
token.ThrowIfCancellationRequested();
var dateSince = new DateTime(year, (int)month, 1);
var dateTo = dateSince.AddMonths(1).AddDays(-1);
var starDate = $"DATE({dateSince.Year}, {dateSince.Month}, {dateSince.Day})";
var endDate = $"DATE({dateTo.Year}, {dateTo.Month}, {dateTo.Day})";
var result = 0.00;
foreach (var item in ReportSettings.settings.TypeOfDays)
{
var sql = $"SELECT SUM(hourqnt) AS ssum FROM zfcacwth " +
$"WHERE fcac_rn = '{fcacRn}' AND hrtype_rn = '{item.Substring(0, 4)}' AND date >= {starDate} AND date <= {endDate} " +
$"AND date NOT IN (SELECT date FROM zfcacwtd WHERE fcac_rn = '{fcacRn}' AND date >= {starDate} AND date <= {endDate}) AND NOT DELETED()";
using (OleDbCommand cmd = new OleDbCommand()
{
CommandText = sql, Connection = dbConnectionAsync
})
{
var execute = await cmd.ExecuteScalarAsync();
if (execute == DBNull.Value)
{
continue;
}
result = result + Convert.ToDouble(execute);
}
}
if (result == 0)
{
return(default);
/// <summary>
/// Executes the specified operation asynchronously.
/// </summary>
/// <param name="executionToken"></param>
/// <param name="implementation"></param>
/// <param name="cancellationToken"></param>
/// <param name="state"></param>
/// <returns></returns>
protected override async Task <int?> ExecuteAsync(CommandExecutionToken <OleDbCommand, OleDbParameter> executionToken, CommandImplementationAsync <OleDbCommand> implementation, CancellationToken cancellationToken, object?state)
{
if (executionToken == null)
{
throw new ArgumentNullException(nameof(executionToken), $"{nameof(executionToken)} is null.");
}
if (implementation == null)
{
throw new ArgumentNullException(nameof(implementation), $"{nameof(implementation)} is null.");
}
var currentToken = executionToken as AccessCommandExecutionToken;
if (currentToken == null)
{
throw new ArgumentNullException(nameof(executionToken), "only AccessCommandExecutionToken is supported.");
}
var startTime = DateTimeOffset.Now;
try
{
using (var con = await CreateConnectionAsync(cancellationToken).ConfigureAwait(false))
{
int?rows = null;
while (currentToken != null)
{
OnExecutionStarted(currentToken, startTime, state);
using (var cmd = new OleDbCommand())
{
cmd.Connection = con;
currentToken.PopulateCommand(cmd, DefaultCommandTimeout);
if (currentToken.ExecutionMode == AccessCommandExecutionMode.Materializer)
{
rows = await implementation(cmd).ConfigureAwait(false);
}
else if (currentToken.ExecutionMode == AccessCommandExecutionMode.ExecuteScalarAndForward)
{
if (currentToken.ForwardResult == null)
{
throw new InvalidOperationException("currentToken.ExecutionMode is ExecuteScalarAndForward, but currentToken.ForwardResult is null.");
}
currentToken.ForwardResult(await cmd.ExecuteScalarAsync().ConfigureAwait(false));
}
else
{
rows = await cmd.ExecuteNonQueryAsync().ConfigureAwait(false);
}
executionToken.RaiseCommandExecuted(cmd, rows);
OnExecutionFinished(currentToken, startTime, DateTimeOffset.Now, rows, state);
}
currentToken = currentToken.NextCommand;
}
return(rows);
}
}
catch (Exception ex)
{
if (cancellationToken.IsCancellationRequested) //convert Exception into a OperationCanceledException
{
var ex2 = new OperationCanceledException("Operation was canceled.", ex, cancellationToken);
OnExecutionError(executionToken, startTime, DateTimeOffset.Now, ex2, state);
throw ex2;
}
else
{
OnExecutionError(executionToken, startTime, DateTimeOffset.Now, ex, state);
throw;
}
}
}
/// <summary>
/// Execute stream as an asynchronous operation.
/// </summary>
/// <param name="executionToken">The execution token.</param>
/// <param name="implementation">The implementation.</param>
/// <param name="cancellationToken">The cancellation token that can be used by other objects or threads to receive notice of cancellation.</param>
/// <param name="state">The state.</param>
/// <returns>A Task<StreamingCommandCompletionToken> representing the asynchronous operation.</returns>
/// <exception cref="System.ArgumentNullException">executionToken</exception>
/// <exception cref="System.ArgumentNullException">implementation</exception>
/// <exception cref="System.ArgumentNullException">executionToken - only AccessCommandExecutionToken is supported.</exception>
/// <exception cref="System.InvalidOperationException">currentToken.ExecutionMode is ExecuteScalarAndForward, but currentToken.ForwardResult is null.</exception>
public override async Task <StreamingCommandCompletionToken> ExecuteStreamAsync(CommandExecutionToken <OleDbCommand, OleDbParameter> executionToken, StreamingCommandImplementationAsync <OleDbCommand> implementation, CancellationToken cancellationToken, object?state)
{
if (executionToken == null)
{
throw new ArgumentNullException(nameof(executionToken), $"{nameof(executionToken)} is null.");
}
if (implementation == null)
{
throw new ArgumentNullException(nameof(implementation), $"{nameof(implementation)} is null.");
}
var currentToken = executionToken as AccessCommandExecutionToken;
if (currentToken == null)
{
throw new ArgumentNullException(nameof(executionToken), "only AccessCommandExecutionToken is supported.");
}
var startTime = DateTimeOffset.Now;
OleDbConnection?con = null;
try
{
con = await CreateConnectionAsync(cancellationToken).ConfigureAwait(false);
OleDbCommand?cmdToReturn = null;
while (currentToken != null)
{
OnExecutionStarted(currentToken, startTime, state);
using (var cmd = new OleDbCommand())
{
cmd.Connection = con;
currentToken.PopulateCommand(cmd, DefaultCommandTimeout);
if (currentToken.ExecutionMode == AccessCommandExecutionMode.Materializer)
{
await implementation(cmd).ConfigureAwait(false);
cmdToReturn = cmd;
}
else if (currentToken.ExecutionMode == AccessCommandExecutionMode.ExecuteScalarAndForward)
{
if (currentToken.ForwardResult == null)
{
throw new InvalidOperationException("currentToken.ExecutionMode is ExecuteScalarAndForward, but currentToken.ForwardResult is null.");
}
currentToken.ForwardResult(await cmd.ExecuteScalarAsync().ConfigureAwait(false));
}
else
{
await cmd.ExecuteNonQueryAsync().ConfigureAwait(false);
}
}
currentToken = currentToken.NextCommand;
}
return(new StreamingCommandCompletionToken(this, executionToken, startTime, state, cmdToReturn, con));
}
catch (Exception ex)
{
#if NET6_0_OR_GREATER
if (con != null)
{
await con.DisposeAsync().ConfigureAwait(false);
}
#else
con?.Dispose();
#endif
if (cancellationToken.IsCancellationRequested) //convert Exception into a OperationCanceledException
{
var ex2 = new OperationCanceledException("Operation was canceled.", ex, cancellationToken);
OnExecutionError(executionToken, startTime, DateTimeOffset.Now, ex2, state);
throw ex2;
}
else
{
OnExecutionError(executionToken, startTime, DateTimeOffset.Now, ex, state);
throw;
}
}
}
/// <summary>
/// Executes the specified operation asynchronously.
/// </summary>
/// <param name="executionToken"></param>
/// <param name="implementation"></param>
/// <param name="cancellationToken"></param>
/// <param name="state"></param>
/// <returns></returns>
protected override async Task <int?> ExecuteAsync(CommandExecutionToken <OleDbCommand, OleDbParameter> executionToken, CommandImplementationAsync <OleDbCommand> implementation, CancellationToken cancellationToken, object state)
{
if (executionToken == null)
{
throw new ArgumentNullException("executionToken", "executionToken is null.");
}
if (implementation == null)
{
throw new ArgumentNullException("implementation", "implementation is null.");
}
var currentToken = executionToken as AccessCommandExecutionToken;
if (currentToken == null)
{
throw new ArgumentNullException("executionToken", "only AccessCommandExecutionToken is supported.");
}
var startTime = DateTimeOffset.Now;
try
{
using (var con = await CreateConnectionAsync(cancellationToken).ConfigureAwait(false))
{
int?rows = null;
while (currentToken != null)
{
OnExecutionStarted(currentToken, startTime, state);
using (var cmd = new OleDbCommand())
{
cmd.Connection = con;
if (DefaultCommandTimeout.HasValue)
{
cmd.CommandTimeout = (int)DefaultCommandTimeout.Value.TotalSeconds;
}
cmd.CommandText = currentToken.CommandText;
cmd.CommandType = currentToken.CommandType;
foreach (var param in currentToken.Parameters)
{
cmd.Parameters.Add(param);
}
currentToken.ApplyCommandOverrides(cmd);
if (currentToken.ExecutionMode == AccessCommandExecutionMode.Materializer)
{
rows = await implementation(cmd);
}
else if (currentToken.ExecutionMode == AccessCommandExecutionMode.ExecuteScalarAndForward)
{
currentToken.ForwardResult(await cmd.ExecuteScalarAsync());
}
else
{
rows = await cmd.ExecuteNonQueryAsync();
}
executionToken.RaiseCommandExecuted(cmd, rows);
OnExecutionFinished(currentToken, startTime, DateTimeOffset.Now, rows, state);
}
currentToken = currentToken.NextCommand;
}
return(rows);
}
}
catch (Exception ex)
{
if (cancellationToken.IsCancellationRequested) //convert Exception into a OperationCanceledException
{
var ex2 = new OperationCanceledException("Operation was canceled.", ex, cancellationToken);
OnExecutionError(executionToken, startTime, DateTimeOffset.Now, ex2, state);
throw ex2;
}
else
{
OnExecutionError(executionToken, startTime, DateTimeOffset.Now, ex, state);
throw;
}
}
}
/// <summary>
/// Tests the connection asynchronously.
/// </summary>
/// <returns></returns>
public override async Task TestConnectionAsync()
{
using (var con = await CreateConnectionAsync())
using (var cmd = new OleDbCommand("SELECT 1", con))
await cmd.ExecuteScalarAsync();
}
/// <summary>
/// Получение списка лицевых счетов с начислениями.
/// </summary>
public async Task <List <Employee> > GetEmployeesAsync(int year, Month month, CancellationToken token)
{
token.ThrowIfCancellationRequested();
var result = new List <Employee>();
var dateSince = new DateTime(year, (int)month, 1);
var dateTo = dateSince.AddMonths(1).AddDays(-1);
var accrual = GetAccrualAsync(year, month, token).Result;
var sqlCount = $"SELECT COUNT(*) " +
$"FROM Zfcac AS Fcac " +
$"JOIN Zank AS Ank ON Fcac.Ank_rn = Ank.Ank_rn " +
$"JOIN Zsubdiv AS Subdiv ON Fcac.Subdiv_rn = Subdiv.Subdiv_rn " +
$"JOIN Zvidisp AS Vid ON Fcac.Vidisp_rn = Vid.Vidisp_rn " +
$"JOIN Ztipdol AS Dol ON Fcac.Tipdol_rn = Dol.Tipdol_rn " +
$"JOIN Orgbase AS Org ON Org.Rn = Ank.Orgbase_rn " +
$"JOIN Person AS P ON P.Orbase_rn = Org.Rn " +
$"WHERE Fcac.Startdate <= DATE({dateTo.Year}, {dateTo.Month}, {dateTo.Day}) AND Fcac.Enddate >= DATE({dateSince.Year}, {dateSince.Month}, {dateSince.Day})";
var sql = $"SELECT P.Surname, P.Firstname, P.Secondname, Subdiv.Nameablative, Ank.Pf_id, Dol.Name, Vid.Name, Fcac.Fcac_rn, Fcac.Startdate, Fcac.Enddate, Subdiv.Note, Zf.Stqnt " +
$"FROM Zfcac AS Fcac " +
$"JOIN Zank AS Ank ON Fcac.Ank_rn = Ank.Ank_rn " +
$"JOIN Zsubdiv AS Subdiv ON Fcac.Subdiv_rn = Subdiv.Subdiv_rn " +
$"JOIN Zvidisp AS Vid ON Fcac.Vidisp_rn = Vid.Vidisp_rn " +
$"JOIN Ztipdol AS Dol ON Fcac.Tipdol_rn = Dol.Tipdol_rn " +
$"JOIN Orgbase AS Org ON Org.Rn = Ank.Orgbase_rn " +
$"JOIN Person AS P ON P.Orbase_rn = Org.Rn " +
$"LEFT JOIN Zfcacch AS Zf ON Zf.Fcacbs_rn = Fcac.Fcac_rn " +
$"WHERE Fcac.Startdate <= DATE({dateTo.Year}, {dateTo.Month}, {dateTo.Day}) AND Fcac.Enddate >= DATE({dateSince.Year}, {dateSince.Month}, {dateSince.Day})";
using (OleDbCommand cmd = new OleDbCommand()
{
CommandText = sqlCount, Connection = dbConnectionAsync
})
{
AddCount = 0;
int allRecords = Convert.ToInt32(await cmd.ExecuteScalarAsync());
LoadData?.Invoke(this, new Tuple <string, int, bool>("Лицевые счета:", allRecords, true));
}
using (OleDbCommand cmd = new OleDbCommand()
{
CommandText = sql, Connection = dbConnectionAsync
})
{
using (var reader = await cmd.ExecuteReaderAsync(token).ConfigureAwait(false))
{
while (await reader.ReadAsync(token).ConfigureAwait(false))
{
var rn = reader[7].ToString().Trim();
if (accrual.Any(a => string.Compare(a.EmployeeId, rn, StringComparison.Ordinal) == 0))
{
var name = reader[0].ToString().Trim();
var surname = reader[1].ToString().Trim();
var patronymic = reader[2].ToString().Trim();
var subdivision = reader[3].ToString().Trim();
var snails = reader[4].ToString().Trim();
var position = reader[5].ToString().Trim();
var subdivisionOid = reader[10].ToString().Trim();
var rate = reader[11].ToString().Trim();
var typePersonalAccount = reader[6].ToString().Trim();
typePersonalAccount = $"{char.ToUpper(typePersonalAccount[0])}{typePersonalAccount.Substring(1).ToLower()}";
//var sourceOfFinancing = reader[7].ToString().Trim();
var employee = new Employee()
{
rn = rn,
Name = name,
Surname = surname,
Patronymic = patronymic,
Subdivision = subdivision,
Snails = snails,
Position = position,
TypePersonalAccount = typePersonalAccount,
SubdivisionOid = subdivisionOid,
Rate = rate,
WorkingTime = GetWorkingTime(rn, year, month, token).Result,
ActualHoursWorked = GetActualHoursWorked(rn, year, month, token).Result
};
employee.Accruals = new List <Accrual>();
employee.Accruals.AddRange(accrual.Where(w => string.Compare(w.EmployeeId, rn, StringComparison.Ordinal) == 0));
result.Add(employee);
AddCount++;
ReaderEvent?.Invoke(this, AddCount);
}
}
}
}
LoadData?.Invoke(this, new Tuple <string, int, bool>(string.Empty, 0, false));
return(result);
}
