/// <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);
}
/// <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);
}
private void onReaderEvent(ReaderEvent readerEvent)
{
log(string.Format("EVENT : {0}", readerEvent.ToString()));
}
private void onReaderEvent(ReaderEvent readerEvent)
{
log(string.Format("EVENT : {0}", readerEvent.ToString()));
}
public void FrameRead(ReaderEvent evt)
{
}
public void OnReportReaderEvent(ReaderEvent p0)
{
System.Diagnostics.Debug.WriteLine($"*** OnReportReaderEvent {p0} ***");
}
/// <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);
}
