165 lines
7.1 KiB
C#
165 lines
7.1 KiB
C#
namespace Stichproben.Cordonel
|
|
{
|
|
using LaaPackages.SqlClient;
|
|
|
|
using System.Linq;
|
|
|
|
internal class StichprobenDbContext
|
|
{
|
|
private readonly int leakStationId;
|
|
private readonly SqlConnection sqlConnection;
|
|
|
|
public StichprobenDbContext(int leakStationId)
|
|
{
|
|
this.leakStationId = leakStationId;
|
|
this.sqlConnection = SqlConnection.CreateSqlConnection("Server=SLASQL01.emea.sensus.net; Database=Auftrag; User ID=sa; Password=sqlserver;");
|
|
}
|
|
|
|
public void AddStichprobe(Stichprobe stichprobe)
|
|
=> this.sqlConnection
|
|
.CreateCommand($@"
|
|
INSERT INTO Stichproben
|
|
( PcbId
|
|
, LineId
|
|
, Nummer
|
|
, Prozentsatz
|
|
, IstStichprobe)
|
|
VALUES (@{nameof(stichprobe.PcbId)}
|
|
, @{nameof(this.leakStationId)}
|
|
, @{nameof(stichprobe.Nummer)}
|
|
, @{nameof(stichprobe.Prozentsatz)}
|
|
, @{nameof(stichprobe.IstStichprobe)})")
|
|
.SetParameter(nameof(stichprobe.PcbId), stichprobe.PcbId)
|
|
.SetParameter(nameof(this.leakStationId), this.leakStationId)
|
|
.SetParameter(nameof(stichprobe.Nummer), stichprobe.Nummer)
|
|
.SetParameter(nameof(stichprobe.Prozentsatz), stichprobe.Prozentsatz)
|
|
.SetParameter(nameof(stichprobe.IstStichprobe), stichprobe.IstStichprobe)
|
|
.ExecuteNonQuery();
|
|
|
|
public StichprobenRule FindRuleOrMin(int prozentsatz)
|
|
=> this.sqlConnection
|
|
.CreateCommand($@"
|
|
SELECT TOP 1
|
|
Id
|
|
, LineId
|
|
, [%]
|
|
, Count
|
|
FROM StichprobenRegeln
|
|
WHERE LineId = @{nameof(this.leakStationId)}
|
|
AND ([%] = @{nameof(prozentsatz)} OR 1 = 1)
|
|
ORDER BY [%]")
|
|
.SetParameter(nameof(this.leakStationId), this.leakStationId)
|
|
.SetParameter(nameof(prozentsatz), prozentsatz)
|
|
.FirstOrDefault(x => new StichprobenRule
|
|
{
|
|
Id = x.GetValue<int>(0),
|
|
LineId = x.GetValue<int>(1),
|
|
Percent = x.GetValue<int>(2),
|
|
Count = x.GetValue<int>(3),
|
|
});
|
|
|
|
public StichprobenRule GetMaxRule()
|
|
=> this.sqlConnection
|
|
.CreateCommand($@"
|
|
SELECT TOP 1
|
|
Id
|
|
, LineId
|
|
, [%]
|
|
, Count
|
|
FROM StichprobenRegeln
|
|
WHERE LineId = @{nameof(this.leakStationId)}
|
|
ORDER BY [%] DESC")
|
|
.SetParameter(nameof(this.leakStationId), this.leakStationId)
|
|
.FirstOrDefault(x => new StichprobenRule
|
|
{
|
|
Id = x.GetValue<int>(0),
|
|
LineId = x.GetValue<int>(1),
|
|
Percent = x.GetValue<int>(2),
|
|
Count = x.GetValue<int>(3),
|
|
});
|
|
|
|
public StichprobenRule GetMinRule()
|
|
=> this.sqlConnection
|
|
.CreateCommand($@"
|
|
SELECT TOP 1
|
|
Id
|
|
, LineId
|
|
, [%]
|
|
, Count
|
|
FROM StichprobenRegeln
|
|
WHERE LineId = @{nameof(this.leakStationId)}
|
|
ORDER BY [%]")
|
|
.SetParameter(nameof(this.leakStationId), this.leakStationId)
|
|
.FirstOrDefault(x => new StichprobenRule
|
|
{
|
|
Id = x.GetValue<int>(0),
|
|
LineId = x.GetValue<int>(1),
|
|
Percent = x.GetValue<int>(2),
|
|
Count = x.GetValue<int>(3),
|
|
});
|
|
|
|
public StichprobenRule GetNextRuleOrMin(int prozentsatz)
|
|
=> this.sqlConnection
|
|
.CreateCommand($@"
|
|
SELECT TOP 1
|
|
Id
|
|
, LineId
|
|
, [%]
|
|
, Count
|
|
FROM StichprobenRegeln
|
|
WHERE LineId = @{nameof(this.leakStationId)}
|
|
AND ([%] < @{nameof(prozentsatz)} OR 1 = 1)
|
|
ORDER BY [%]")
|
|
.SetParameter(nameof(this.leakStationId), this.leakStationId)
|
|
.SetParameter(nameof(prozentsatz), prozentsatz)
|
|
.FirstOrDefault(x => new StichprobenRule
|
|
{
|
|
Id = x.GetValue<int>(0),
|
|
LineId = x.GetValue<int>(1),
|
|
Percent = x.GetValue<int>(2),
|
|
Count = x.GetValue<int>(3),
|
|
});
|
|
|
|
public Stichprobe GetLastStichprobeOrDefault()
|
|
=> this.sqlConnection
|
|
.CreateCommand($@"
|
|
SELECT Id
|
|
, PcbId
|
|
, LineId
|
|
, Nummer
|
|
, Prozentsatz
|
|
, IstStichprobe
|
|
, IstDicht
|
|
, Datum
|
|
FROM Stichproben
|
|
WHERE LineId = @{nameof(this.leakStationId)}
|
|
ORDER BY Id DESC")
|
|
.SetParameter(nameof(this.leakStationId), this.leakStationId)
|
|
.FirstOrDefault(x => new Stichprobe
|
|
{
|
|
Id = x.GetValue<int>(0),
|
|
LineId = x.GetValue<int>(1),
|
|
PcbId = x.GetValue<int>(2),
|
|
Nummer = x.GetValue<int>(3),
|
|
Prozentsatz = x.GetValue<int>(4),
|
|
IstStichprobe = x.GetValue<bool>(5),
|
|
IstDicht = x.GetValue<bool?>(6),
|
|
});
|
|
|
|
public bool IsAnyStichprobeUndicht()
|
|
=> this.sqlConnection
|
|
.CreateCommand($@"
|
|
UPDATE Stichproben
|
|
SET IstDicht = d.Dicht
|
|
OUTPUT inserted.IstDicht
|
|
FROM Stichproben AS s
|
|
LEFT JOIN Druckpruefung AS d
|
|
ON s.PcbId = d.FabNr
|
|
WHERE s.LineId = @{nameof(this.leakStationId)}
|
|
AND s.IstDicht IS NULL")
|
|
.SetParameter(nameof(this.leakStationId), this.leakStationId)
|
|
.ExecuteReader(x => x.GetValue<bool?>(0))
|
|
.Any(x => x == false);
|
|
}
|
|
}
|