private HashSet <UFRule.UnidadeFisicaInfo> GetUFsInfo(string appendWhereClause, IDbConnection conn)
{
var result = new HashSet <UFRule.UnidadeFisicaInfo>();
using (SqlCommand command = new SqlCommand(string.Empty, (SqlConnection)conn))
{
command.CommandText = string.Format(@"
SELECT n.ID, nED.Codigo + '/' + n.Codigo, nd.Designacao, ta.Designacao, COALESCE(df.MedidaAltura, 0),
COALESCE(df.MedidaLargura, 0), COALESCE(df.MedidaProfundidade, 0), COALESCE(tm.Designacao, ''), c.Cota,
COALESCE(dt.FimAno, ''), COALESCE(dt.FimMes, ''), COALESCE(dt.FimDia, ''), COALESCE(dt.InicioAno, ''),
COALESCE(dt.InicioMes, ''), COALESCE(dt.InicioDia, ''), COALESCE(nuf.Eliminado, 0)
FROM NivelUnidadeFisica nuf
INNER JOIN NivelDesignado nd ON nd.ID = nuf.ID AND nd.isDeleted = 0
INNER JOIN Nivel n ON n.ID = nd.ID AND n.isDeleted = 0
INNER JOIN RelacaoHierarquica rh ON rh.ID = n.ID AND rh.isDeleted = 0
INNER JOIN Nivel nED ON nED.ID = rh.IDUpper AND nED.isDeleted = 0
INNER JOIN FRDBase frd ON frd.IDNivel = n.ID AND frd.isDeleted = 0
LEFT JOIN NivelUnidadeFisicaDeposito ON NivelUnidadeFisicaDeposito.IDNivelUnidadeFisica = nuf.ID AND NivelUnidadeFisicaDeposito.isDeleted = 0
LEFT JOIN SFRDDatasProducao dt ON dt.IDFRDBase = frd.ID AND dt.isDeleted = 0
LEFT JOIN SFRDUFDescricaoFisica df ON df.IDFRDBase = frd.ID AND df.isDeleted = 0
LEFT JOIN TipoAcondicionamento ta ON ta.ID = df.IDTipoAcondicionamento AND ta.isDeleted = 0
LEFT JOIN TipoMedida tm ON tm.ID = df.IDTipoMedida AND df.isDeleted = 0
LEFT JOIN SFRDUFCota c ON c.IDFRDBase = frd.ID AND c.isDeleted = 0
WHERE nuf.isDeleted = 0 {0}", appendWhereClause);
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
var uf = new UFRule.UnidadeFisicaInfo();
uf.ID = reader.GetInt64(0);
uf.Codigo = reader.GetString(1);
uf.Designacao = reader.GetString(2);
uf.Tipo = reader.GetString(3);
uf.Altura = System.Convert.ToDecimal(reader.GetValue(4));
uf.Largura = System.Convert.ToDecimal(reader.GetValue(5));
uf.Profundidade = System.Convert.ToDecimal(reader.GetValue(6));
uf.Medida = reader.GetString(7);
uf.Cota = reader.GetString(8);
uf.FimAno = reader.GetString(9);
uf.FimMes = reader.GetString(10);
uf.FimDia = reader.GetString(11);
uf.InicioAno = reader.GetString(12);
uf.InicioMes = reader.GetString(13);
uf.InicioDia = reader.GetString(14);
uf.Eliminado = System.Convert.ToInt16(reader.GetValue(15)) == 0 ? false : true;
result.Add(uf);
}
reader.Close();
}
return(result);
}
private HashSet<UFRule.UnidadeFisicaInfo> GetUFsInfo(string appendWhereClause, IDbConnection conn)
{
var result = new HashSet<UFRule.UnidadeFisicaInfo>();
using (SqlCommand command = new SqlCommand(string.Empty, (SqlConnection)conn))
{
command.CommandText = string.Format(@"
SELECT n.ID, nED.Codigo + '/' + n.Codigo, nd.Designacao, ta.Designacao, COALESCE(df.MedidaAltura, 0),
COALESCE(df.MedidaLargura, 0), COALESCE(df.MedidaProfundidade, 0), COALESCE(tm.Designacao, ''), c.Cota,
COALESCE(dt.FimAno, ''), COALESCE(dt.FimMes, ''), COALESCE(dt.FimDia, ''), COALESCE(dt.InicioAno, ''),
COALESCE(dt.InicioMes, ''), COALESCE(dt.InicioDia, ''), COALESCE(nuf.Eliminado, 0)
FROM NivelUnidadeFisica nuf
INNER JOIN NivelDesignado nd ON nd.ID = nuf.ID AND nd.isDeleted = 0
INNER JOIN Nivel n ON n.ID = nd.ID AND n.isDeleted = 0
INNER JOIN RelacaoHierarquica rh ON rh.ID = n.ID AND rh.isDeleted = 0
INNER JOIN Nivel nED ON nED.ID = rh.IDUpper AND nED.isDeleted = 0
INNER JOIN FRDBase frd ON frd.IDNivel = n.ID AND frd.isDeleted = 0
LEFT JOIN NivelUnidadeFisicaDeposito ON NivelUnidadeFisicaDeposito.IDNivelUnidadeFisica = nuf.ID AND NivelUnidadeFisicaDeposito.isDeleted = 0
LEFT JOIN SFRDDatasProducao dt ON dt.IDFRDBase = frd.ID AND dt.isDeleted = 0
LEFT JOIN SFRDUFDescricaoFisica df ON df.IDFRDBase = frd.ID AND df.isDeleted = 0
LEFT JOIN TipoAcondicionamento ta ON ta.ID = df.IDTipoAcondicionamento AND ta.isDeleted = 0
LEFT JOIN TipoMedida tm ON tm.ID = df.IDTipoMedida AND df.isDeleted = 0
LEFT JOIN SFRDUFCota c ON c.IDFRDBase = frd.ID AND c.isDeleted = 0
WHERE nuf.isDeleted = 0 {0}", appendWhereClause);
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
var uf = new UFRule.UnidadeFisicaInfo();
uf.ID = reader.GetInt64(0);
uf.Codigo = reader.GetString(1);
uf.Designacao = reader.GetString(2);
uf.Tipo = reader.GetString(3);
uf.Altura = System.Convert.ToDecimal(reader.GetValue(4));
uf.Largura = System.Convert.ToDecimal(reader.GetValue(5));
uf.Profundidade = System.Convert.ToDecimal(reader.GetValue(6));
uf.Medida = reader.GetString(7);
uf.Cota = reader.GetString(8);
uf.FimAno = reader.GetString(9);
uf.FimMes = reader.GetString(10);
uf.FimDia = reader.GetString(11);
uf.InicioAno = reader.GetString(12);
uf.InicioMes = reader.GetString(13);
uf.InicioDia = reader.GetString(14);
uf.Eliminado = System.Convert.ToInt16(reader.GetValue(15)) == 0 ? false : true;
result.Add(uf);
}
reader.Close();
}
return result;
}
