private decimal ChooseUnit(FrequencyMeasure measure, Pipe pipe = null)
{
decimal ret = 0;
switch (measure)
{
case FrequencyMeasure.Meters:
ret = pipe == null ? initialPipeLength : pipe.Length;
break;
case FrequencyMeasure.Tons:
ret = pipe == null ? initialPipeWeight : pipe.Weight;
break;
case FrequencyMeasure.Pipes:
ret = 1;
break;
default:
log.Error("Notifications: unrecognized type of unit at ChooseUnit: " + measure);
break;
}
return(ret);
}
/// <summary>
/// Find last date of inspection operation result and read all "unitsProducedSinceLastDate" for not required inspection operations.
/// </summary>
/// <param name="testId">
/// Id of not required inspection operations
/// </param>
/// <param name="measure">
/// Frequency measure of not required inspection operations
/// </param>
/// <returns>
/// KeyValuePair contains id of not required inspection operations and units left since last inspection result
/// </returns>
public KeyValuePair<Guid, decimal> GetUnitsProducedSinceLastDateTest(Guid testId, FrequencyMeasure measure)
{
CreateConnection();
KeyValuePair<Guid, decimal> unitsProducedSinceLastDate = new KeyValuePair<Guid, decimal>();
DateTime date=DateTime.MinValue;
try
{
using (SqlCommand command = new System.Data.SqlClient.SqlCommand())
{
connection.Open();
command.Connection = connection;
command.Parameters.AddWithValue("@testId", testId);
command.CommandText = String.Format(@"Select Max(r.Date) From PipeTestResult r Where r.pipeTestId = @testId
and r.status not in('{0}')", PipeTestResultStatus.Scheduled.ToString());
SqlDataReader dr = command.ExecuteReader();
while (dr.Read())
{
date = dr[0]== System.DBNull.Value ? (DateTime)(new DateTime(1950, 6, 10, 15, 24, 16)) : (DateTime)dr[0];
}
command.Parameters.AddWithValue("@testId", testId);
command.Parameters.AddWithValue("@date", date);
if (measure == FrequencyMeasure.Pipes)
{
command.CommandText = @"Select count(Distinct(p.number)) amount, t.id
From Pipe p, PipeTest t where t.pipeMillSizeTypeId=p.typeId and
t.id =@testId and p.productionDate>@maxDate and p.isActive=1
group by t.id ";
while (dr.Read())
{
unitsProducedSinceLastDate = new KeyValuePair<Guid, decimal>(
testId,
dr[0] == System.DBNull.Value ? 0 : (decimal)(int)dr[0]
);
}
}
else if (measure == FrequencyMeasure.Tons)
{
command.CommandText = @" select sum (w.amount) from (Select Distinct(p.weight) amount, t.id
From Pipe p, PipeTest t where t.pipeMillSizeTypeId=p.typeId and
t.id =@testId and p.productionDate>@maxDate and p.isActive=1
group by t.id, p.weight ) w group by w.id ";
while (dr.Read())
{
unitsProducedSinceLastDate = new KeyValuePair<Guid, decimal>(
testId,
dr[0] == System.DBNull.Value ? 0 : (decimal)(double)dr[0]
);
}
}
else if (measure == FrequencyMeasure.Meters)
{
command.CommandText = @" select sum (w.amount) from (Select Distinct(p.length) amount, t.id
From Pipe p, PipeTest t where t.pipeMillSizeTypeId=p.typeId and
t.id =@testId and p.productionDate>@maxDate and p.isActive=1
group by t.id, p.weight ) w group by w.id ";
while (dr.Read())
{
unitsProducedSinceLastDate = new KeyValuePair<Guid, decimal>
(testId,
dr[0] == System.DBNull.Value ? 0 : (decimal)(int)dr[0]);
}
}
else
{
command.CommandText = @" Select 0 amount, t.id
From Pipe p, PipeTest t, PipeTestResult r
where t.pipeMillSizeTypeId=p.typeId and t.id =@testId
and p.isActive=1 and t.id=r.pipeTestId and p.productionDate>@date
group by t.id ";
while (dr.Read())
{
unitsProducedSinceLastDate = new KeyValuePair<Guid, decimal>
(testId,
dr[0] == System.DBNull.Value ? 0 : (decimal)(int)dr[0]);
}
}
}
}
catch (SqlException ex)
{
throw new RepositoryException("Get all units produced fr current test", ex);
}
finally
{
if (connection.State == System.Data.ConnectionState.Open)
{
connection.Close();
}
}
return unitsProducedSinceLastDate;
}
/// <summary>
/// Read all "unitsProducedSinceLastDate" for not required inspection operations.
/// </summary>
/// <param name="testId">
/// Id of not required inspection operations
/// </param>
/// <param name="maxDate">
/// date of last result of not required inspection operations
/// </param>
/// <param name="measure">
/// Frequency measure of not required inspection operations
/// </param>
/// <returns>
/// KeyValuePair contains id of not required inspection operations and units left since last inspection result
/// </returns>
public KeyValuePair<Guid, decimal> GetAllUnitsProducedSinceLastDate(Guid testId, DateTime maxDate, FrequencyMeasure measure)
{
CreateConnection();
KeyValuePair<Guid, decimal> unitsProducedSinceLastDate = new KeyValuePair<Guid,decimal>();
try
{
using (SqlCommand command = new System.Data.SqlClient.SqlCommand())
{
connection.Open();
command.Connection = connection;
command.Parameters.AddWithValue("@testId", testId);
command.Parameters.AddWithValue("@maxDate", maxDate);
if (measure == FrequencyMeasure.Pipes)
{
command.CommandText = @"Select count(Distinct(p.number)) amount, t.id
From Pipe p, PipeTest t where t.pipeMillSizeTypeId=p.typeId and
t.id =@testId and p.productionDate>@maxDate and p.isActive=1
group by t.id ";
SqlDataReader dr = command.ExecuteReader();
while (dr.Read())
{
unitsProducedSinceLastDate = new KeyValuePair<Guid, decimal>(
(Guid)dr[1],
dr[0] == System.DBNull.Value ? 0 : (decimal)(int)dr[0]
);
}
}
else if (measure == FrequencyMeasure.Tons)
{
command.CommandText = @"select sum (w.amount) from (Select Distinct(p.weight) amount, t.id
From Pipe p, PipeTest t where t.pipeMillSizeTypeId=p.typeId and
t.id =@testId and p.productionDate>@maxDate and p.isActive=1
group by t.id, p.weight ) w group by w.id";
SqlDataReader dr = command.ExecuteReader();
while (dr.Read())
{
unitsProducedSinceLastDate = new KeyValuePair<Guid, decimal>(
(Guid)dr[1],
dr[0] == System.DBNull.Value ? 0 : (decimal)dr[0]
);
}
}
else if (measure == FrequencyMeasure.Meters)
{
command.CommandText = @"select sum (w.amount) from (Select Distinct(p.length) amount, t.id
From Pipe p, PipeTest t where t.pipeMillSizeTypeId=p.typeId and
t.id =@testId and p.productionDate>@maxDate and p.isActive=1
group by t.id, p.weight ) w group by w.id";
SqlDataReader dr = command.ExecuteReader();
while (dr.Read())
{
unitsProducedSinceLastDate = new KeyValuePair<Guid, decimal>
((Guid)dr[1],
dr[0] == System.DBNull.Value ? 0 : (decimal)(int)dr[0]);
}
}
else
{
command.CommandText = @"Select 0 amount, t.id
From Pipe p, PipeTest t where t.pipeMillSizeTypeId=p.typeId and
t.id =@testId and p.productionDate>@maxDate and p.isActive=1
group by t.id ";
SqlDataReader dr = command.ExecuteReader();
while (dr.Read())
{
unitsProducedSinceLastDate = new KeyValuePair<Guid, decimal>
((Guid)dr[1],
dr[0] == System.DBNull.Value ? 0 : (decimal)(int)dr[0]);
}
}
}
}
catch (SqlException ex)
{
throw new RepositoryException("Get all not required operation", ex);
}
finally
{
if (connection.State == System.Data.ConnectionState.Open)
{
connection.Close();
}
}
return unitsProducedSinceLastDate;
}
private decimal ChooseUnit(FrequencyMeasure measure, Pipe pipe = null)
{
decimal ret = 0;
switch (measure)
{
case FrequencyMeasure.Meters:
ret =pipe == null ? initialPipeLength : pipe.Length;
break;
case FrequencyMeasure.Tons:
ret = pipe == null ? initialPipeWeight : pipe.Weight;
break;
case FrequencyMeasure.Pipes:
ret = 1;
break;
default:
log.Error("Notifications: unrecognized type of unit at ChooseUnit: " + measure);
break;
}
return ret;
}
/// <summary>
/// Find last date of inspection operation result and read all "unitsProducedSinceLastDate" for not required inspection operations.
/// </summary>
/// <param name="testId">
/// Id of not required inspection operations
/// </param>
/// <param name="measure">
/// Frequency measure of not required inspection operations
/// </param>
/// <returns>
/// KeyValuePair contains id of not required inspection operations and units left since last inspection result
/// </returns>
public KeyValuePair <Guid, decimal> GetUnitsProducedSinceLastDateTest(Guid testId, FrequencyMeasure measure)
{
CreateConnection();
KeyValuePair <Guid, decimal> unitsProducedSinceLastDate = new KeyValuePair <Guid, decimal>();
DateTime date = DateTime.MinValue;
try
{
using (SqlCommand command = new System.Data.SqlClient.SqlCommand())
{
connection.Open();
command.Connection = connection;
command.Parameters.AddWithValue("@testId", testId);
command.CommandText = String.Format(@"Select Max(r.Date) From PipeTestResult r Where r.pipeTestId = @testId
and r.status not in('{0}')", PipeTestResultStatus.Scheduled.ToString());
SqlDataReader dr = command.ExecuteReader();
while (dr.Read())
{
date = dr[0] == System.DBNull.Value ? (DateTime)(new DateTime(1950, 6, 10, 15, 24, 16)) : (DateTime)dr[0];
}
command.Parameters.AddWithValue("@testId", testId);
command.Parameters.AddWithValue("@date", date);
if (measure == FrequencyMeasure.Pipes)
{
command.CommandText = @"Select count(Distinct(p.number)) amount, t.id
From Pipe p, PipeTest t where t.pipeMillSizeTypeId=p.typeId and
t.id =@testId and p.productionDate>@maxDate and p.isActive=1
group by t.id ";
while (dr.Read())
{
unitsProducedSinceLastDate = new KeyValuePair <Guid, decimal>(
testId,
dr[0] == System.DBNull.Value ? 0 : (decimal)(int)dr[0]
);
}
}
else if (measure == FrequencyMeasure.Tons)
{
command.CommandText = @" select sum (w.amount) from (Select Distinct(p.weight) amount, t.id
From Pipe p, PipeTest t where t.pipeMillSizeTypeId=p.typeId and
t.id =@testId and p.productionDate>@maxDate and p.isActive=1
group by t.id, p.weight ) w group by w.id ";
while (dr.Read())
{
unitsProducedSinceLastDate = new KeyValuePair <Guid, decimal>(
testId,
dr[0] == System.DBNull.Value ? 0 : (decimal)(double)dr[0]
);
}
}
else if (measure == FrequencyMeasure.Meters)
{
command.CommandText = @" select sum (w.amount) from (Select Distinct(p.length) amount, t.id
From Pipe p, PipeTest t where t.pipeMillSizeTypeId=p.typeId and
t.id =@testId and p.productionDate>@maxDate and p.isActive=1
group by t.id, p.weight ) w group by w.id ";
while (dr.Read())
{
unitsProducedSinceLastDate = new KeyValuePair <Guid, decimal>
(testId,
dr[0] == System.DBNull.Value ? 0 : (decimal)(int)dr[0]);
}
}
else
{
command.CommandText = @" Select 0 amount, t.id
From Pipe p, PipeTest t, PipeTestResult r
where t.pipeMillSizeTypeId=p.typeId and t.id =@testId
and p.isActive=1 and t.id=r.pipeTestId and p.productionDate>@date
group by t.id ";
while (dr.Read())
{
unitsProducedSinceLastDate = new KeyValuePair <Guid, decimal>
(testId,
dr[0] == System.DBNull.Value ? 0 : (decimal)(int)dr[0]);
}
}
}
}
catch (SqlException ex)
{
throw new RepositoryException("Get all units produced fr current test", ex);
}
finally
{
if (connection.State == System.Data.ConnectionState.Open)
{
connection.Close();
}
}
return(unitsProducedSinceLastDate);
}
/// <summary>
/// Read all "unitsProducedSinceLastDate" for not required inspection operations.
/// </summary>
/// <param name="testId">
/// Id of not required inspection operations
/// </param>
/// <param name="maxDate">
/// date of last result of not required inspection operations
/// </param>
/// <param name="measure">
/// Frequency measure of not required inspection operations
/// </param>
/// <returns>
/// KeyValuePair contains id of not required inspection operations and units left since last inspection result
/// </returns>
public KeyValuePair <Guid, decimal> GetAllUnitsProducedSinceLastDate(Guid testId, DateTime maxDate, FrequencyMeasure measure)
{
CreateConnection();
KeyValuePair <Guid, decimal> unitsProducedSinceLastDate = new KeyValuePair <Guid, decimal>();
try
{
using (SqlCommand command = new System.Data.SqlClient.SqlCommand())
{
connection.Open();
command.Connection = connection;
command.Parameters.AddWithValue("@testId", testId);
command.Parameters.AddWithValue("@maxDate", maxDate);
if (measure == FrequencyMeasure.Pipes)
{
command.CommandText = @"Select count(Distinct(p.number)) amount, t.id
From Pipe p, PipeTest t where t.pipeMillSizeTypeId=p.typeId and
t.id =@testId and p.productionDate>@maxDate and p.isActive=1
group by t.id ";
SqlDataReader dr = command.ExecuteReader();
while (dr.Read())
{
unitsProducedSinceLastDate = new KeyValuePair <Guid, decimal>(
(Guid)dr[1],
dr[0] == System.DBNull.Value ? 0 : (decimal)(int)dr[0]
);
}
}
else if (measure == FrequencyMeasure.Tons)
{
command.CommandText = @"select sum (w.amount) from (Select Distinct(p.weight) amount, t.id
From Pipe p, PipeTest t where t.pipeMillSizeTypeId=p.typeId and
t.id =@testId and p.productionDate>@maxDate and p.isActive=1
group by t.id, p.weight ) w group by w.id";
SqlDataReader dr = command.ExecuteReader();
while (dr.Read())
{
unitsProducedSinceLastDate = new KeyValuePair <Guid, decimal>(
(Guid)dr[1],
dr[0] == System.DBNull.Value ? 0 : (decimal)dr[0]
);
}
}
else if (measure == FrequencyMeasure.Meters)
{
command.CommandText = @"select sum (w.amount) from (Select Distinct(p.length) amount, t.id
From Pipe p, PipeTest t where t.pipeMillSizeTypeId=p.typeId and
t.id =@testId and p.productionDate>@maxDate and p.isActive=1
group by t.id, p.weight ) w group by w.id";
SqlDataReader dr = command.ExecuteReader();
while (dr.Read())
{
unitsProducedSinceLastDate = new KeyValuePair <Guid, decimal>
((Guid)dr[1],
dr[0] == System.DBNull.Value ? 0 : (decimal)(int)dr[0]);
}
}
else
{
command.CommandText = @"Select 0 amount, t.id
From Pipe p, PipeTest t where t.pipeMillSizeTypeId=p.typeId and
t.id =@testId and p.productionDate>@maxDate and p.isActive=1
group by t.id ";
SqlDataReader dr = command.ExecuteReader();
while (dr.Read())
{
unitsProducedSinceLastDate = new KeyValuePair <Guid, decimal>
((Guid)dr[1],
dr[0] == System.DBNull.Value ? 0 : (decimal)(int)dr[0]);
}
}
}
}
catch (SqlException ex)
{
throw new RepositoryException("Get all not required operation", ex);
}
finally
{
if (connection.State == System.Data.ConnectionState.Open)
{
connection.Close();
}
}
return(unitsProducedSinceLastDate);
}
