/// <summary>
/// Compose a PERSREP query.
/// </summary>
private string ComposePersrepQuery(bool hasCompanyFilter, JFilter j1, JFilter j2)
{
return("SELECT " + (hasCompanyFilter ? "Logi2.Ryhm" : "Logi2.Kompanii") + @",
SUM(IIF(Yksus.KKV = 'O', 1, 0)) AS Ohvitsere,
SUM(IIF(Yksus.KKV = 'AO', 1, 0)) AS Allohvitsere,
SUM(IIF(Yksus.KKV = 'S', 1, 0)) AS Sodureid,
SUM(IIF(Yksus.KKV = 'TSIV', 1, 0)) AS Tsiviliste
FROM (SELECT
Yksus.Isikukood, Yksus.Eesnimi, Yksus.Perekonnanimi,
Yksus.Kompanii, Yksus.Ryhm, Yksus.KKV, COUNT(Logi.Kellaaeg) AS Kohal
FROM Yksus
LEFT OUTER JOIN Logi
ON Logi.Isikukood = Yksus.Isikukood
WHERE Logi.Kellaaeg >= @start
AND Logi.Kellaaeg <= @end
" + (hasCompanyFilter ? " AND Yksus.Kompanii = @company" : "")
+ (j1.enabled ? (j1.desiredValue == null ? " AND (Yksus.J1 IS NULL)" : " AND Yksus.J1 = @j1") : "")
+ (j2.enabled ? (j2.desiredValue == null ? " AND (Yksus.J2 IS NULL)" : " AND Yksus.J2 = @j2") : "")
+ @" GROUP BY Yksus.Isikukood, Yksus.Eesnimi, Yksus.Perekonnanimi,
Yksus.Kompanii, Yksus.Ryhm, Yksus.Ametikoht, Yksus.KKV
ORDER BY Yksus.Kompanii, Yksus.Ryhm ASC) Logi2
WHERE Logi2.Kohal > 0
GROUP BY " + (hasCompanyFilter ? "Logi2.Ryhm" : "Logi2.Kompanii") + ";");
}
/// <summary>
/// Collect personnel file data and feed them to the ReportWriter.
/// For PERSREP (settings.reportType == PERSREP), the filter is a name of a company.
/// For ATTENDANCE, the filter is the name of a platoon.
/// Respectively, only members of the company / platoon are included.
/// </summary>
/// <seealso cref="IReportWriter"/>
/// <seealso cref="PersrepReportWriter"/>
/// <seealso cref="AttendanceReportWriter"/>
private bool GenerateReport()
{
Debug.Assert(conn != null, "Database connection was null in GenerateReport()");
Debug.Assert(personnelReader != null, "personnel reader not initialized in generateReport()");
reportReady = false;
reportLoading = true;
UpdateValidity();
if (timeFilterEnabledCheckbox.Checked)
{
settings.startOfReport = dataSelectionStartDate.Value;
settings.endOfReport = dataSelectionEndDate.Value;
}
else
{
settings.startOfReport = DateTime.MinValue;
settings.endOfReport = DateTime.MaxValue;
}
if (reportWriter != null)
{
reportWriter.CloseExcel();
reportWriter = null;
}
DestroyAllExcel();
if (!File.Exists(settings.reportTemplate))
{
progressStatusLabel.Text = "Raporti põhi on puudu.";
Debug.Print("Report template missing. {0}", settings.reportTemplate);
reportReady = false;
reportLoading = false;
UpdateValidity();
return(false);
}
// Support J1 and J2 filters
JFilter j1 = new JFilter();
j1.enabled = false;
if (j1FilterEnabled.Checked)
{
int x;
j1.enabled = true;
if (j1Filter.Text != J_EMPTY_VALUE)
{
if (int.TryParse(j1Filter.Text, out x))
{
j1.desiredValue = x;
}
else
{
MessageBox.Show("J1 parameetris on viga. Ei saanud teisendada numbriks. Jätan arvestamata.", "Viga raporti genereerimisel");
}
}
else
{
j1.desiredValue = null;
Debug.Print("J1 must be empty");
}
}
JFilter j2 = new JFilter();
j2.enabled = false;
if (j2FilterEnabled.Checked)
{
j2.enabled = true;
int x;
if (j2Filter.Text != J_EMPTY_VALUE)
{
if (int.TryParse(j2Filter.Text, out x))
{
j2.desiredValue = x;
}
else
{
MessageBox.Show("J2 parameetris on viga. Ei saanud teisendada numbriks. Jätan arvestamata.", "Viga raporti genereerimisel");
}
}
else
{
Debug.Print("J2 must be empty");
j2.desiredValue = null;
}
}
// Support Company filter
if (companyFilterEnabled.Enabled && companyFilterEnabled.Checked && settings.companyFilter == null)
{
settings.companyFilter = companyFilter.Text;
}
// Support Platoon filter
bool success = false;
if (settings.reportType == ReportType.PERSREP)
{
progressStatusLabel.Text = "Koostan PERSREPi...";
reportWriter = new PersrepReportWriter(settings.reportTemplate);
success = reportWriter.WriteReport(cardLogReader.ReadPersrepData(settings.startOfReport, settings.endOfReport, j1, j2, settings.companyFilter));
if (success)
{
progressStatusLabel.Text = "PERSREP koostatud!";
}
}
else if (settings.reportType == ReportType.ATTENDANCE)
{
progressStatusLabel.Text = "Koostan kohalolekukontrolli...";
reportWriter = new AttendanceReportWriter(settings.reportTemplate);
success = reportWriter.WriteReport(cardLogReader.ReadAttendanceData(settings.startOfReport, settings.endOfReport, j1, j2, settings.companyFilter, settings.platoonFilter));
if (success)
{
progressStatusLabel.Text = "Kohalolekukontroll koostatud!";
}
}
// Collect unknown people if no other filters than time are enabled
if (settings.companyFilter == null && settings.platoonFilter == null && !j2.enabled && !j1.enabled)
{
progressStatusLabel.Text = "Leian tundmatud inimesed...";
var unknowns = cardLogReader.ReadUnknownPeople(settings.startOfReport, settings.endOfReport);
reportWriter.HandleUnknownPeople(unknowns);
progressStatusLabel.Text = "Tundmatud leitud!";
}
reportReady = true;
reportLoading = false;
settings.companyFilter = settings.platoonFilter = null;
UpdateValidity();
progressStatusLabel.Text = "Raport on valmis salvestamiseks.";
return(success);
}
public List <AttendanceItem> ReadAttendanceData(DateTime start, DateTime end, JFilter j1, JFilter j2, string companyFilter = null, string platoonFilter = null)
{
Debug.Assert(start != null, "ReadAttendanceData: start time was null");
Debug.Assert(end != null, "ReadAttendanceData: end time was null");
var cursor = databaseConnection.CreateCommand();
cursor.CommandText = ComposeAttendanceQuery(companyFilter != null, platoonFilter != null, j1, j2);
Debug.Print("ReadAttendanceData: {0}", cursor.CommandText);
Debug.Print("ReadAttendanceData: Start: {0}, End: {1} ", start, end);
Debug.Print("ReadAttendanceData: company filter: {0}", companyFilter);
Debug.Print("ReadAttendanceData: platoon filter: {0}", platoonFilter);
Debug.Print("ReadAttendanceData: j1 filter: {0}", j1.enabled);
Debug.Print("ReadAttendanceData: j2 filter: {0}", j2.enabled);
cursor.Parameters.Add(new OleDbParameter("@start", OleDbType.Date));
cursor.Parameters[0].Value = start;
cursor.Parameters.Add(new OleDbParameter("@end", OleDbType.Date));
cursor.Parameters[1].Value = end;
var data = new List <AttendanceItem>();
if (companyFilter != null)
{
Debug.Print("ReadAttendanceData: Using company filter");
var param = new OleDbParameter("@company", OleDbType.VarWChar, companyFilter.Length);
param.Value = companyFilter;
cursor.Parameters.Add(param);
}
if (platoonFilter != null)
{
Debug.Assert(companyFilter != null, "Company filter cannot be null when platoon filter is active");
Debug.Print("ReadAttendanceData: Using attendance for platoon: '{0}'", platoonFilter);
var param = new OleDbParameter("@platoon", OleDbType.VarWChar, platoonFilter.Length);
param.Value = platoonFilter;
cursor.Parameters.Add(param);
}
if (j1.enabled && j1.desiredValue != null)
{
Debug.Print("Using J1 filter in attendance");
var param = new OleDbParameter("@j1", OleDbType.Integer);
if (j1.desiredValue == null)
{
param.Value = DBNull.Value;
}
else
{
param.Value = j1.desiredValue;
}
cursor.Parameters.Add(param);
}
if (j2.enabled && j2.desiredValue != null)
{
Debug.Print("Using J2 filter in attendance");
var param = new OleDbParameter("@j2", OleDbType.Integer);
if (j2.desiredValue == null)
{
param.Value = DBNull.Value;
}
else
{
param.Value = j2.desiredValue;
}
cursor.Parameters.Add(param);
}
CardLogEntry entry = null;
OleDbDataReader reader = null;
try
{
cursor.Prepare();
reader = cursor.ExecuteReader();
}
catch (InvalidOperationException ex)
{
Debug.Print(ex.ToString());
return(data);
}
catch (OleDbException ex)
{
if ((uint)ex.HResult == 0x80040E37)
{
MessageBox.Show("Logide tabelit '" + TABLE_NAME + "' ei eksisteeri.\nVeateade:\n" + ex.Message, "Viga Accessi andmebaasis");
}
Debug.Print(ex.ToString());
return(data);
}
if (reader == null)
{
Debug.Print("Something went wrong while reading.");
return(data);
}
if (reader.HasRows)
{
while (reader.Read())
{
string name = reader.GetString(reader.GetOrdinal("Eesnimi")) + " " + reader.GetString(reader.GetOrdinal("Perekonnanimi"));
string platoon = reader.GetString(reader.GetOrdinal("Ryhm"));
if (data.Exists((item) => item.name == name))
{
Debug.Print("{0} on juba nimekirjas", name);
continue;
}
data.Add(new AttendanceItem()
{
name = name,
platoon = platoon
});
}
}
return(data);
}
/// <summary>
/// Read data for the PERSREP.
/// Uses the PERSREP_QUERY.
/// The PERSREP report can be limited by the start & end dates, and by string matching a company.
/// </summary>
/// <param name="start">The minimum signin date, inclusive</param>
/// <param name="end">The maximum signin date, exclusive</param>
/// <param name="companyFilter">If present, limit people to only one company.</param>
/// <returns>List of PERSREP columns.</returns>
public List <PersrepItem> ReadPersrepData(DateTime start, DateTime end, JFilter j1, JFilter j2, string companyFilter = null)
{
Debug.Assert(start != null, "ReadPersrepData: start time was null");
Debug.Assert(end != null, "ReadPersrepData: end time was null");
var cursor = databaseConnection.CreateCommand();
Debug.Print("ReadPersrepData: start");
var data = new List <PersrepItem>();
cursor.CommandText = ComposePersrepQuery(companyFilter != null, j1, j2);
Debug.Print(cursor.CommandText);
Debug.Print("company filter: {0}", companyFilter);
Debug.Print("j1 filter: {0}", j1.enabled);
Debug.Print("j2 filter: {0}", j2.enabled);
// NOTE: Parameters are order-specific only in access SQL. the @names don't matter, but are good for clarification.
cursor.Parameters.Add(new OleDbParameter("@start", OleDbType.Date));
cursor.Parameters[0].Value = start;
cursor.Parameters.Add(new OleDbParameter("@end", OleDbType.Date));
cursor.Parameters[1].Value = end;
if (companyFilter != null)
{
Debug.Print("Company filter active");
var param = new OleDbParameter("@company", OleDbType.VarWChar, companyFilter.Length);
param.Value = companyFilter;
cursor.Parameters.Add(param);
}
if (j1.enabled && j1.desiredValue != null)
{
Debug.Print("J1 filter active");
var param = new OleDbParameter("@j1", OleDbType.Integer);
param.Value = j1.desiredValue;
cursor.Parameters.Add(param);
}
if (j2.enabled && j2.desiredValue != null)
{
Debug.Print("J2 filter active");
var param = new OleDbParameter("@j1", OleDbType.Integer);
if (j2.desiredValue == null)
{
param.Value = DBNull.Value;
}
else
{
param.Value = j2.desiredValue;
}
cursor.Parameters.Add(param);
}
CardLogEntry entry = null;
OleDbDataReader reader = null;
try
{
cursor.Prepare();
reader = cursor.ExecuteReader();
}
catch (InvalidOperationException ex)
{
Debug.Print(ex.ToString());
return(data);
}
catch (OleDbException ex)
{
if ((uint)ex.HResult == 0x80040E37)
{
MessageBox.Show("Logide tabelit '" + TABLE_NAME + "' ei eksisteeri.\nVeateade:\n" + ex.Message, "Viga Accessi andmebaasis");
}
Debug.Print(ex.ToString());
return(data);
}
if (reader == null)
{
Debug.Print("Something went wrong while reading.");
return(data);
}
if (reader.HasRows)
{
Debug.Print("ReadPersrepData: has rows");
while (reader.Read())
{
string company;
if (companyFilter == null)
{
company = reader.GetString(reader.GetOrdinal("Kompanii"));
}
else
{
company = reader.GetString(reader.GetOrdinal("Ryhm"));
}
int ohvitsere = (int)Math.Round(reader.GetDouble(reader.GetOrdinal("Ohvitsere")));
int allohvitsere = (int)Math.Round(reader.GetDouble(reader.GetOrdinal("Allohvitsere")));
int sodureid = (int)Math.Round(reader.GetDouble(reader.GetOrdinal("Sodureid")));
int tsiviliste = (int)Math.Round(reader.GetDouble(reader.GetOrdinal("Tsiviliste")));
Debug.Print("PERSREP Kompanii: {0} O {1} AO {2} S {3} TSIV {4}", company, ohvitsere, allohvitsere, sodureid, tsiviliste);
data.Add(new PersrepItem()
{
company = company,
ohvitsere = ohvitsere,
allohvitsere = allohvitsere,
sodureid = sodureid,
tsiviliste = tsiviliste
});
}
}
else
{
Debug.Print("ReadPersrepData: no rows");
return(data);
}
return(data);
}
/// <summary>
/// Compose an attendance query.
/// </summary>
private string ComposeAttendanceQuery(bool hasCompanyFilter, bool hasPlatoonFilter, JFilter j1, JFilter j2)
{
return(@"SELECT
Yksus.Eesnimi, Yksus.Perekonnanimi, Yksus.Ryhm
FROM Yksus
INNER JOIN Logi
ON Logi.Isikukood = Yksus.Isikukood
WHERE Logi.Kellaaeg >= @start
AND Logi.Kellaaeg <= @end"
+ (hasCompanyFilter ? " AND Yksus.Kompanii = @company" : "")
+ (hasPlatoonFilter ? " AND Yksus.Ryhm = @platoon" : "")
+ (j1.enabled ? (j1.desiredValue == null ? " AND Yksus.J1 IS NULL" : " AND Yksus.J1 = @j1") : "")
+ (j2.enabled ? (j2.desiredValue == null ? " AND Yksus.J2 IS NULL" : " AND Yksus.J2 = @j2") : "")
+ " ORDER BY Logi.Kellaaeg ASC;");
}
