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); }