public override ArrayList GetItems(DataSet currentDataSet, int pageNr, int itemsPerPage, IDbConnection conn)
{
ArrayList rows = new ArrayList();
using (SqlCommand command = new SqlCommand("", (SqlConnection)conn))
{
command.CommandText = "CREATE TABLE #ItemsID (IDDeposito BIGINT, Designacao NVARCHAR(200)";
command.ExecuteNonQuery();
command.CommandText = "INSERT INTO #ItemsID SELECT IDDeposito, Designacao FROM #OrderedItems WHERE seq_id >= @seq_id1 AND seq_id <= @seq_id2 ORDER BY seq_id";
command.Parameters.AddWithValue("@seq_id1", (pageNr - 1) * itemsPerPage + 1);
command.Parameters.AddWithValue("@seq_id2", pageNr * itemsPerPage);
command.ExecuteNonQuery();
using (SqlDataAdapter da = new SqlDataAdapter(command))
{
command.Parameters.AddWithValue("@isDeleted", 0);
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["Deposito"],
"INNER JOIN #ItemsID ON #ItemsID.IDDeposito = Deposito.ID ");
da.Fill(currentDataSet, "Deposito");
}
command.CommandText = "SELECT * FROM #ItemsID";
SqlDataReader reader = command.ExecuteReader();
ArrayList row;
while (reader.Read())
{
row = new ArrayList();
row.Add(reader.GetInt64(0));
row.Add(reader.GetString(1));
rows.Add(row);
}
reader.Close();
}
return(rows);
}
public override void LoadDepositosPermissionsData(DataSet currentDataSet, long trusteeID, IDbConnection conn)
{
using (SqlCommand command = SqlSyntax.CreateSelectCommandWithNoDeletedRowsParam((SqlConnection)conn))
using (SqlDataAdapter da = new SqlDataAdapter(command))
{
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["Trustee"]);
da.Fill(currentDataSet, "Trustee");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["TrusteeUser"]);
da.Fill(currentDataSet, "TrusteeUser");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["TrusteeGroup"]);
da.Fill(currentDataSet, "TrusteeGroup");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["UserGroups"]);
da.Fill(currentDataSet, "UserGroups");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["Deposito"]);
da.Fill(currentDataSet, "Deposito");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["TrusteeDepositoPrivilege"]);
da.Fill(currentDataSet, "TrusteeDepositoPrivilege");
}
}
private void LoadDataRows(DataSet currentDataSet, long docID, IDbConnection conn)
{
using (SqlCommand command = SqlSyntax.CreateSelectCommandWithNoDeletedRowsParam((SqlConnection)conn))
using (SqlDataAdapter da = new SqlDataAdapter(command))
{
command.Parameters.AddWithValue("@SFRDImagemTipo", "Fedora");
// carregar toda a informação referente aos objectos digitais do documento actual e seus subdocumentos
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["SFRDImagemVolume"],
"INNER JOIN ( " +
"SELECT DISTINCT IDSFDImagemVolume " +
"FROM SFRDImagem " +
"INNER JOIN FRDBase ON FRDBase.ID = SFRDImagem.IDFRDBase " +
"INNER JOIN #niveisTemp ON #niveisTemp.ID = FRDBase.IDNivel " +
"WHERE SFRDImagem.Tipo = @SFRDImagemTipo" +
") img ON img.IDSFDImagemVolume = SFRDImagemVolume.ID");
da.Fill(currentDataSet, "SFRDImagemVolume");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["SFRDImagem"],
"INNER JOIN FRDBase ON FRDBase.ID = SFRDImagem.IDFRDBase " +
"INNER JOIN #niveisTemp ON #niveisTemp.ID = FRDBase.IDNivel " +
"WHERE SFRDImagem.Tipo = @SFRDImagemTipo");
da.Fill(currentDataSet, "SFRDImagem");
command.CommandText = @"
INSERT INTO #odsTemp
SELECT IDObjetoDigital
FROM SFRDImagemObjetoDigital
INNER JOIN SFRDImagem ON SFRDImagem.IDFRDBase = SFRDImagemObjetoDigital.IDFRDBase AND SFRDImagem.idx = SFRDImagemObjetoDigital.idx AND SFRDImagem.isDeleted = @isDeleted
INNER JOIN FRDBase ON FRDBase.ID = SFRDImagem.IDFRDBase AND FRDBase.isDeleted = @isDeleted
INNER JOIN #niveisTemp ON #niveisTemp.ID = FRDBase.IDNivel
WHERE SFRDImagem.Tipo = 'Fedora' AND SFRDImagemObjetoDigital.isDeleted = @isDeleted";
command.ExecuteNonQuery();
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["ObjetoDigital"],
"INNER JOIN #odsTemp ON #odsTemp.ID = ObjetoDigital.ID");
da.Fill(currentDataSet, "ObjetoDigital");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["SFRDImagemObjetoDigital"],
"INNER JOIN SFRDImagem ON SFRDImagem.IDFRDBase = SFRDImagemObjetoDigital.IDFRDBase AND SFRDImagem.idx = SFRDImagemObjetoDigital.idx " +
"INNER JOIN FRDBase ON FRDBase.ID = SFRDImagem.IDFRDBase " +
"INNER JOIN #niveisTemp ON #niveisTemp.ID = FRDBase.IDNivel " +
"WHERE SFRDImagem.Tipo = @SFRDImagemTipo");
da.Fill(currentDataSet, "SFRDImagemObjetoDigital");
// carregar permissões
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["TrusteeObjetoDigitalPrivilege"],
"INNER JOIN #odsTemp ON #odsTemp.ID = TrusteeObjetoDigitalPrivilege.IDObjetoDigital");
da.Fill(currentDataSet, "TrusteeObjetoDigitalPrivilege");
// carregar ODs e respetivas permissões sem um nivel documental correspondente
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["ObjetoDigital"],
"INNER JOIN ObjetoDigitalRelacaoHierarquica ON ObjetoDigitalRelacaoHierarquica.ID = ObjetoDigital.ID " +
"INNER JOIN #odsTemp ON #odsTemp.ID = ObjetoDigitalRelacaoHierarquica.IDUpper ");
da.Fill(currentDataSet, "ObjetoDigital");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["ObjetoDigitalRelacaoHierarquica"],
"INNER JOIN #odsTemp ON #odsTemp.ID = ObjetoDigitalRelacaoHierarquica.IDUpper");
da.Fill(currentDataSet, "ObjetoDigitalRelacaoHierarquica");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["TrusteeObjetoDigitalPrivilege"],
"INNER JOIN ObjetoDigitalRelacaoHierarquica ON ObjetoDigitalRelacaoHierarquica.ID = TrusteeObjetoDigitalPrivilege.IDObjetoDigital " +
"INNER JOIN #odsTemp ON #odsTemp.ID = ObjetoDigitalRelacaoHierarquica.IDUpper ");
da.Fill(currentDataSet, "TrusteeObjetoDigitalPrivilege");
}
}
public override void LoadObjDigitalPermissoesSimples(DataSet currentDataSet, long nRowIDUpper, IDbConnection conn)
{
using (SqlCommand command = new SqlCommand(string.Empty, (SqlConnection)conn))
using (SqlDataAdapter da = new SqlDataAdapter(command))
{
command.CommandText = "CREATE TABLE #temp (ID BIGINT)";
command.ExecuteNonQuery();
command.Parameters.AddWithValue("@isDeleted", 0);
command.Parameters.AddWithValue("@nRowIDUpper", nRowIDUpper);
command.Parameters.AddWithValue("@IDTipoNivelRelacionado", 10);
command.CommandText = @"INSERT INTO #temp
SELECT rh.ID
FROM FRDBase frdUpper
INNER JOIN RelacaoHierarquica rh ON rh.IDUpper = frdUpper.IDNivel AND rh.IDTipoNivelRelacionado = @IDTipoNivelRelacionado AND rh.isDeleted = @isDeleted
WHERE frdUpper.IDNivel = @nRowIDUpper AND frdUpper.isDeleted = @isDeleted";
command.ExecuteNonQuery();
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["Trustee"]);
da.Fill(currentDataSet, "Trustee");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["TrusteeUser"]);
da.Fill(currentDataSet, "TrusteeUser");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["TrusteeGroup"]);
da.Fill(currentDataSet, "TrusteeGroup");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["UserGroups"]);
da.Fill(currentDataSet, "UserGroups");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["RelacaoHierarquica"],
"INNER JOIN #temp ON #temp.ID = RelacaoHierarquica.ID");
da.Fill(currentDataSet, "RelacaoHierarquica");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["Nivel"],
"INNER JOIN #temp ON #temp.ID = Nivel.ID");
da.Fill(currentDataSet, "Nivel");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["FRDBase"],
"INNER JOIN #temp ON #temp.ID = FRDBase.IDNivel");
da.Fill(currentDataSet, "FRDBase");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["SFRDImagem"],
"INNER JOIN FRDBase ON FRDBase.ID = SFRDImagem.IDFRDBase " +
"INNER JOIN #temp ON #temp.ID = FRDBase.IDNivel");
da.Fill(currentDataSet, "SFRDImagem");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["ObjetoDigital"],
"INNER JOIN SFRDImagemObjetoDigital ON SFRDImagemObjetoDigital.IDObjetoDigital = ObjetoDigital.ID " +
"INNER JOIN SFRDImagem ON SFRDImagem.IDFRDBase = SFRDImagemObjetoDigital.IDFRDBase AND SFRDImagem.idx = SFRDImagemObjetoDigital.idx " +
"INNER JOIN FRDBase ON FRDBase.ID = SFRDImagem.IDFRDBase " +
"INNER JOIN #temp ON #temp.ID = FRDBase.IDNivel");
da.Fill(currentDataSet, "ObjetoDigital");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["SFRDImagemObjetoDigital"],
"INNER JOIN SFRDImagem ON SFRDImagem.IDFRDBase = SFRDImagemObjetoDigital.IDFRDBase AND SFRDImagem.idx = SFRDImagemObjetoDigital.idx " +
"INNER JOIN FRDBase ON FRDBase.ID = SFRDImagem.IDFRDBase " +
"INNER JOIN #temp ON #temp.ID = FRDBase.IDNivel");
da.Fill(currentDataSet, "SFRDImagemObjetoDigital");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["SFRDImagemVolume"]);
da.Fill(currentDataSet, "SFRDImagemVolume");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["TrusteeObjetoDigitalPrivilege"],
"INNER JOIN ObjetoDigital ON ObjetoDigital.ID = TrusteeObjetoDigitalPrivilege.IDObjetoDigital " +
"INNER JOIN SFRDImagemObjetoDigital ON SFRDImagemObjetoDigital.IDObjetoDigital = ObjetoDigital.ID " +
"INNER JOIN SFRDImagem ON SFRDImagem.IDFRDBase = SFRDImagemObjetoDigital.IDFRDBase AND SFRDImagem.idx = SFRDImagemObjetoDigital.idx " +
"INNER JOIN FRDBase ON FRDBase.ID = SFRDImagem.IDFRDBase " +
"INNER JOIN #temp ON #temp.ID = FRDBase.IDNivel");
da.Fill(currentDataSet, "TrusteeObjetoDigitalPrivilege");
command.CommandText = "DROP TABLE #temp";
command.ExecuteNonQuery();
}
}
public override void LoadStaticDataTables(DataSet CurrentDataSet, IDbConnection conn)
{
using (SqlCommand command = SqlSyntax.CreateSelectCommandWithNoDeletedRowsParam((SqlConnection)conn))
using (SqlDataAdapter da = new SqlDataAdapter(command))
{
try
{
// Normas de países, línguas, caligrafia...
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(CurrentDataSet.Tables["Iso15924"]);
da.Fill(CurrentDataSet, "Iso15924");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(CurrentDataSet.Tables["Iso3166"]);
da.Fill(CurrentDataSet, "Iso3166");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(CurrentDataSet.Tables["Iso639"]);
da.Fill(CurrentDataSet, "Iso639");
//Configuracoes
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(CurrentDataSet.Tables["GlobalConfig"]);
da.Fill(CurrentDataSet, "GlobalConfig");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(CurrentDataSet.Tables["ConfigAlfabeto"]);
da.Fill(CurrentDataSet, "ConfigAlfabeto");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(CurrentDataSet.Tables["ConfigLingua"]);
da.Fill(CurrentDataSet, "ConfigLingua");
//Conjunto de Privilégios da Aplicação
//Identificam acesso a Módulos da aplicação ou funcionalidades dentro destes, não a dados.
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(CurrentDataSet.Tables["TipoFunctionGroup"]);
da.Fill(CurrentDataSet, "TipoFunctionGroup");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(CurrentDataSet.Tables["TipoFunction"]);
da.Fill(CurrentDataSet, "TipoFunction");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(CurrentDataSet.Tables["TipoOperation"]);
da.Fill(CurrentDataSet, "TipoOperation");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(CurrentDataSet.Tables["NivelTipoOperation"]);
da.Fill(CurrentDataSet, "NivelTipoOperation");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(CurrentDataSet.Tables["DepositoTipoOperation"]);
da.Fill(CurrentDataSet, "DepositoTipoOperation");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(CurrentDataSet.Tables["ObjetoDigitalTipoOperation"]);
da.Fill(CurrentDataSet, "ObjetoDigitalTipoOperation");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(CurrentDataSet.Tables["FunctionOperation"]);
da.Fill(CurrentDataSet, "FunctionOperation");
//Tipos de producto existentes e funcionalidades proprias de cada um
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(CurrentDataSet.Tables["TipoServer"]);
da.Fill(CurrentDataSet, "TipoServer");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(CurrentDataSet.Tables["Modules"]);
da.Fill(CurrentDataSet, "Modules");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(CurrentDataSet.Tables["ProductFunction"]);
da.Fill(CurrentDataSet, "ProductFunction");
//Enumerados utilizados em foreign keys
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(CurrentDataSet.Tables["TipoControloAutForma"]);
da.Fill(CurrentDataSet, "TipoControloAutForma");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(CurrentDataSet.Tables["TipoControloAutRel"]);
da.Fill(CurrentDataSet, "TipoControloAutRel");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(CurrentDataSet.Tables["TipoDensidade"]);
da.Fill(CurrentDataSet, "TipoDensidade");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(CurrentDataSet.Tables["TipoEntidadeProdutora"]);
da.Fill(CurrentDataSet, "TipoEntidadeProdutora");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(CurrentDataSet.Tables["TipoEstadoDeConservacao"]);
da.Fill(CurrentDataSet, "TipoEstadoDeConservacao");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(CurrentDataSet.Tables["TipoFormaSuporteAcond"]);
da.Fill(CurrentDataSet, "TipoFormaSuporteAcond");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(CurrentDataSet.Tables["TipoFRDBase"]);
da.Fill(CurrentDataSet, "TipoFRDBase");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(CurrentDataSet.Tables["TipoMaterialDeSuporte"]);
da.Fill(CurrentDataSet, "TipoMaterialDeSuporte");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(CurrentDataSet.Tables["TipoMedida"]);
da.Fill(CurrentDataSet, "TipoMedida");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(CurrentDataSet.Tables["TipoNivel"]);
da.Fill(CurrentDataSet, "TipoNivel");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(CurrentDataSet.Tables["TipoNivelRelacionado"]);
da.Fill(CurrentDataSet, "TipoNivelRelacionado");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(CurrentDataSet.Tables["TipoNivelRelacionadoCodigo"]);
da.Fill(CurrentDataSet, "TipoNivelRelacionadoCodigo");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(CurrentDataSet.Tables["RelacaoTipoNivelRelacionado"]);
da.Fill(CurrentDataSet, "RelacaoTipoNivelRelacionado");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(CurrentDataSet.Tables["TipoNoticiaAut"]);
da.Fill(CurrentDataSet, "TipoNoticiaAut");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(CurrentDataSet.Tables["TipoNoticiaATipoControloAForma"]);
da.Fill(CurrentDataSet, "TipoNoticiaATipoControloAForma");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(CurrentDataSet.Tables["TipoOrdenacao"]);
da.Fill(CurrentDataSet, "TipoOrdenacao");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(CurrentDataSet.Tables["TipoPertinencia"]);
da.Fill(CurrentDataSet, "TipoPertinencia");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(CurrentDataSet.Tables["TipoSuporte"]);
da.Fill(CurrentDataSet, "TipoSuporte");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(CurrentDataSet.Tables["TipoAcondicionamento"]);
da.Fill(CurrentDataSet, "TipoAcondicionamento");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(CurrentDataSet.Tables["TipoTecnicaRegisto"]);
da.Fill(CurrentDataSet, "TipoTecnicaRegisto");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(CurrentDataSet.Tables["TipoEstadoConservacao"]);
da.Fill(CurrentDataSet, "TipoEstadoConservacao");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(CurrentDataSet.Tables["TipoSubDensidade"]);
da.Fill(CurrentDataSet, "TipoSubDensidade");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(CurrentDataSet.Tables["TipoTecnicasDeRegisto"]);
da.Fill(CurrentDataSet, "TipoTecnicasDeRegisto");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(CurrentDataSet.Tables["TipoTradicaoDocumental"]);
da.Fill(CurrentDataSet, "TipoTradicaoDocumental");
//Autos de eliminação
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(CurrentDataSet.Tables["AutoEliminacao"]);
da.Fill(CurrentDataSet, "AutoEliminacao");
//Tipos de entrega
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(CurrentDataSet.Tables["TipoEntrega"]);
da.Fill(CurrentDataSet, "TipoEntrega");
//Integracao
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(CurrentDataSet.Tables["Integ_Sistema"]);
da.Fill(CurrentDataSet, "Integ_Sistema");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(CurrentDataSet.Tables["Integ_TipoEntidade"]);
da.Fill(CurrentDataSet, "Integ_TipoEntidade");
// TipoTipologias:
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(CurrentDataSet.Tables["TipoTipologias"]);
da.Fill(CurrentDataSet, "TipoTipologias");
}
catch (Exception)
{
throw;
}
}
}
public override void LoadUF(DataSet currentDataSet, long idNivel, IDbConnection conn)
{
using (SqlCommand command = SqlSyntax.CreateSelectCommandWithNoDeletedRowsParam((SqlConnection)conn))
using (SqlDataAdapter da = new SqlDataAdapter(command))
{
command.Parameters.AddWithValue("@idNivel", idNivel);
// nuvem nível
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["Nivel"],
"WHERE ID=@idNivel");
da.Fill(currentDataSet, "Nivel");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["NivelDesignado"],
"WHERE ID=@idNivel");
da.Fill(currentDataSet, "NivelDesignado");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["LocalConsulta"]);
da.Fill(currentDataSet, "LocalConsulta");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["NivelUnidadeFisica"],
"WHERE ID=@idNivel");
da.Fill(currentDataSet, "NivelUnidadeFisica");
// entidade detentora
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["RelacaoHierarquica"],
"WHERE ID=@idNivel");
da.Fill(currentDataSet, "RelacaoHierarquica");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["Nivel"],
"INNER JOIN RelacaoHierarquica rh ON rh.ID = Nivel.ID " +
"INNER JOIN Nivel nED ON nED.ID = rh.IDUpper " +
"WHERE Nivel.ID=@idNivel");
da.Fill(currentDataSet, "Nivel");
// nuvem frdbase
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["FRDBase"],
"WHERE FRDBase.IDNivel=@idNivel");
da.Fill(currentDataSet, "FRDBase");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["SFRDDatasProducao"],
"INNER JOIN FRDBase frd ON frd.ID = SFRDDatasProducao.IDFRDBase " +
"WHERE frd.IDNivel=@idNivel");
da.Fill(currentDataSet, "SFRDDatasProducao");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["SFRDUFCota"],
"INNER JOIN FRDBase frd ON frd.ID = SFRDUFCota.IDFRDBase " +
"WHERE frd.IDNivel=@idNivel");
da.Fill(currentDataSet, "SFRDUFCota");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["SFRDConteudoEEstrutura"],
"INNER JOIN FRDBase frd ON frd.ID = SFRDConteudoEEstrutura.IDFRDBase " +
"WHERE frd.IDNivel=@idNivel");
da.Fill(currentDataSet, "SFRDConteudoEEstrutura");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["SFRDUFDescricaoFisica"],
"INNER JOIN FRDBase frd ON frd.ID = SFRDUFDescricaoFisica.IDFRDBase " +
"WHERE frd.IDNivel=@idNivel");
da.Fill(currentDataSet, "SFRDUFDescricaoFisica");
// unidades informacionais associadas
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["Nivel"],
"INNER JOIN FRDBase ON FRDBase.IDNivel = Nivel.ID " +
"INNER JOIN SFRDUnidadeFisica ON SFRDUnidadeFisica.IDFRDBase = FRDBase.ID " +
"WHERE SFRDUnidadeFisica.IDNivel=@idNivel");
da.Fill(currentDataSet, "Nivel");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["RelacaoHierarquica"],
"INNER JOIN FRDBase ON FRDBase.IDNivel = RelacaoHierarquica.ID " +
"INNER JOIN SFRDUnidadeFisica ON SFRDUnidadeFisica.IDFRDBase = FRDBase.ID " +
"WHERE SFRDUnidadeFisica.IDNivel=@idNivel");
da.Fill(currentDataSet, "RelacaoHierarquica");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["Nivel"],
"INNER JOIN RelacaoHierarquica ON RelacaoHierarquica.IDUpper = Nivel.ID " +
"INNER JOIN FRDBase ON FRDBase.IDNivel = RelacaoHierarquica.ID " +
"INNER JOIN SFRDUnidadeFisica ON SFRDUnidadeFisica.IDFRDBase = FRDBase.ID " +
"WHERE SFRDUnidadeFisica.IDNivel=@idNivel");
da.Fill(currentDataSet, "Nivel");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["FRDBase"],
"INNER JOIN SFRDUnidadeFisica ON SFRDUnidadeFisica.IDFRDBase = FRDBase.ID " +
"WHERE SFRDUnidadeFisica.IDNivel=@idNivel");
da.Fill(currentDataSet, "FRDBase");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["SFRDUnidadeFisica"],
"WHERE IDNivel=@idNivel");
da.Fill(currentDataSet, "SFRDUnidadeFisica");
}
}
public override ArrayList GetItems(DataSet currentDataSet, int pageNr, int itemsPerPage, IDbConnection conn)
{
ArrayList rows = new ArrayList();
using (SqlCommand command = new SqlCommand("", (SqlConnection)conn))
{
if (!uaCalculated)
{
command.CommandText = "CREATE TABLE #NumUnidadesInformacionais(IDNivel BIGINT, NumUnidadesInformacionais BIGINT)";
command.ExecuteNonQuery();
command.CommandText =
"INSERT INTO #NumUnidadesInformacionais " +
"SELECT sfrduf.IDNivel, COUNT(*) NumUnidadesInformacionais " +
"FROM #OrderedItems oi " +
"INNER JOIN SFRDUnidadeFisica sfrduf ON sfrduf.IDNivel = oi.ID " +
"WHERE seq_id >= @seq_id1 AND seq_id <= @seq_id2 AND sfrduf.isDeleted = @isDeleted " +
"GROUP BY sfrduf.IDNivel";
command.Parameters.AddWithValue("@isDeleted", 0);
command.Parameters.AddWithValue("@seq_id1", (pageNr - 1) * itemsPerPage + 1);
command.Parameters.AddWithValue("@seq_id2", pageNr * itemsPerPage);
command.ExecuteNonQuery();
command.Parameters.Clear();
uaCalculated = true;
}
command.CommandText = "CREATE TABLE #ItemsID (ID BIGINT, Codigo NVARCHAR(110), Designacao NVARCHAR(768), Cota NVARCHAR(300), CodigoBarras NVARCHAR(20), InicioAno NVARCHAR(4), " +
"InicioMes NVARCHAR(2), InicioDia NVARCHAR(2), InicioAtribuida BIT, FimAno NVARCHAR(4), FimMes NVARCHAR(2), FimDia NVARCHAR(2), FimAtribuida BIT, NumUnidadesInformacionais BIGINT, " +
"Eliminado BIT, AutosEliminacao NVARCHAR(MAX))";
command.ExecuteNonQuery();
command.CommandText =
"INSERT INTO #ItemsID " +
"SELECT n.ID, nED.Codigo + '/' + n.Codigo Codigo, nd.Designacao, cota.Cota, nuf.CodigoBarras, dp.InicioAno, dp.InicioMes, " +
"dp.InicioDia, dp.InicioAtribuida, dp.FimAno, dp.FimMes, dp.FimDia, dp.FimAtribuida, " +
"COALESCE(num.NumUnidadesInformacionais, 0) NumUnidadesInformacionais, nuf.Eliminado, autos.AutosEliminacao " +
"FROM #OrderedItems oi " +
"INNER JOIN Nivel n ON n.ID = oi.ID " +
"INNER JOIN NivelDesignado nd ON nd.ID = n.ID " +
"INNER JOIN RelacaoHierarquica rh ON rh.ID = n.ID " +
"INNER JOIN Nivel nED ON nED.ID = rh.IDUpper " +
"INNER JOIN FRDBase frd ON frd.IDNivel = n.ID " +
"LEFT JOIN NivelUnidadeFisica nuf ON nuf.ID = nd.ID " +
"LEFT JOIN SFRDUFCota cota ON cota.IDFRDBase = frd.ID " +
"LEFT JOIN SFRDDatasProducao dp ON dp.IDFRDBase = frd.ID " +
"LEFT JOIN #NumUnidadesInformacionais num ON num.IDNivel = n.ID " +
"LEFT JOIN #autosAssociados autos ON autos.ufID = n.ID " +
"WHERE seq_id >= @seq_id1 AND seq_id <= @seq_id2 " +
"AND n.isDeleted = @isDeleted " +
"AND nd.isDeleted = @isDeleted " +
"AND rh.isDeleted = @isDeleted " +
"AND nED.isDeleted = @isDeleted " +
"AND frd.isDeleted = @isDeleted " +
"AND (cota.isDeleted IS NULL OR cota.isDeleted = @isDeleted) " +
"AND (nuf.isDeleted IS NULL OR nuf.isDeleted = @isDeleted) " +
"AND (dp.isDeleted IS NULL OR dp.isDeleted = @isDeleted) " +
"ORDER BY seq_id";
command.Parameters.AddWithValue("@isDeleted", 0);
command.Parameters.AddWithValue("@seq_id1", (pageNr - 1) * itemsPerPage + 1);
command.Parameters.AddWithValue("@seq_id2", pageNr * itemsPerPage);
command.ExecuteNonQuery();
using (SqlDataAdapter da = new SqlDataAdapter(command))
{
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["Nivel"],
"INNER JOIN #ItemsID ON #ItemsID.ID = Nivel.ID");
da.Fill(currentDataSet, "Nivel");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["FRDBase"],
"INNER JOIN #ItemsID ON #ItemsID.ID = FRDBase.IDNivel");
da.Fill(currentDataSet, "FRDBase");
// é necessário carregar esta informação para mostrar o código de uma unidade física seleccionada para o caso
// só existir uma única na lista
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["Nivel"],
"INNER JOIN RelacaoHierarquica rh ON rh.IDUpper = Nivel.ID " +
"INNER JOIN #ItemsID ON #ItemsID.ID = rh.ID");
da.Fill(currentDataSet, "Nivel");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["RelacaoHierarquica"],
"INNER JOIN #ItemsID ON #ItemsID.ID = RelacaoHierarquica.ID");
da.Fill(currentDataSet, "RelacaoHierarquica");
}
command.CommandText = "SELECT * FROM #ItemsID;";
command.Parameters.Clear();
SqlDataReader reader = command.ExecuteReader();
ArrayList row;
while (reader.Read())
{
row = new ArrayList();
row.Add(reader.GetValue(0));
row.Add(reader.GetValue(1));
row.Add(reader.GetValue(2));
row.Add(reader.GetValue(3));
row.Add(reader.GetValue(4));
row.Add(reader.GetValue(5));
row.Add(reader.GetValue(6));
row.Add(reader.GetValue(7));
row.Add(reader.GetValue(8));
row.Add(reader.GetValue(9));
row.Add(reader.GetValue(10));
row.Add(reader.GetValue(11));
row.Add(reader.GetValue(12));
row.Add(reader.GetValue(13));
row.Add(reader.IsDBNull(14) ? false : reader.GetValue(14)); // Eliminado: pode ser BDNull
row.Add(reader.IsDBNull(15) ? string.Empty : reader.GetValue(15));
rows.Add(row);
}
reader.Close();
}
return(rows);
}
public override List <PermissoesRule.ObjDig> LoadDataObjDigital(DataSet CurrentDataSet, long IDNivel, long IDTrustee, long IDLoginTrustee, out Dictionary <long, Dictionary <long, byte> > permsImpl, IDbConnection conn)
{
var res = new List <PermissoesRule.ObjDig>();
permsImpl = new Dictionary <long, Dictionary <long, byte> >();
using (var command = new SqlCommand("", (SqlConnection)conn))
using (SqlDataAdapter da = new SqlDataAdapter(command))
{
command.CommandText = "CREATE TABLE #temp(ID BIGINT PRIMARY KEY); CREATE TABLE #odsTemp(ID BIGINT PRIMARY KEY, pid NVARCHAR(20), titulo NVARCHAR(768));";
command.ExecuteNonQuery();
command.CommandText = "INSERT INTO #temp VALUES (@IDNivel)";
command.Parameters.AddWithValue("@IDNivel", IDNivel);
command.ExecuteNonQuery();
command.CommandText = @"
WITH Temp (ID, IDUpper)
AS (
SELECT rh.ID, rh.IDUpper
FROM RelacaoHierarquica rh
WHERE rh.IDUpper = @IDNivel AND rh.isDeleted = @isDeleted
UNION ALL
SELECT rh.ID, rh.IDUpper
FROM RelacaoHierarquica rh
INNER JOIN Temp ON Temp.ID = rh.IDUpper
WHERE rh.isDeleted = @isDeleted
)
INSERT INTO #temp
SELECT Temp.ID
FROM Temp";
command.Parameters.AddWithValue("@isDeleted", 0);
command.ExecuteNonQuery();
PermissoesRule.Current.GetEffectiveReadPermissions(" FROM #temp ", IDLoginTrustee, conn);
command.CommandText = "DELETE FROM #temp WHERE ID IN (SELECT IDNivel FROM #effective WHERE Ler = @Ler OR Ler IS NULL)";
command.Parameters.AddWithValue("@Ler", 0);
command.ExecuteNonQuery();
PermissoesRule.Current.DropEffectivePermissionsTempTable(conn);
command.CommandText = @"
INSERT INTO #odsTemp
SELECT ID, pid, Titulo
FROM (
SELECT od.ID, od.pid, od.Titulo
FROM #temp T
INNER JOIN FRDBase frd ON frd.IDNivel = T.ID AND frd.IDTipoFRDBase = @IDTipoFRDBase AND frd.isDeleted = @isDeleted
INNER JOIN SFRDImagem img ON img.IDFRDBase = frd.ID AND img.Tipo = @imgTipo AND img.isDeleted = @isDeleted
INNER JOIN SFRDImagemObjetoDigital imgOD ON imgOD.IDFRDBase = img.IDFRDBase AND imgOD.idx = img.idx AND imgOD.isDeleted = @isDeleted
INNER JOIN ObjetoDigital od ON od.ID = imgOD.IDObjetoDigital AND od.isDeleted = @isDeleted
UNION ALL
SELECT odSimples.ID, odSimples.pid, odSimples.Titulo
FROM #temp T
INNER JOIN FRDBase frd ON frd.IDNivel = T.ID AND frd.IDTipoFRDBase = @IDTipoFRDBase AND frd.isDeleted = @isDeleted
INNER JOIN SFRDImagem img ON img.IDFRDBase = frd.ID AND img.Tipo = @imgTipo AND img.isDeleted = @isDeleted
INNER JOIN SFRDImagemObjetoDigital imgOD ON imgOD.IDFRDBase = img.IDFRDBase AND imgOD.idx = img.idx AND imgOD.isDeleted = @isDeleted
INNER JOIN ObjetoDigital od ON od.ID = imgOD.IDObjetoDigital AND od.isDeleted = @isDeleted
INNER JOIN ObjetoDigitalRelacaoHierarquica odrh ON odrh.IDUpper = od.ID AND odrh.isDeleted = @isDeleted
INNER JOIN ObjetoDigital odSimples ON odSimples.ID = odrh.ID AND odSimples.isDeleted = @isDeleted
) ods";
command.Parameters.AddWithValue("@IDTipoFRDBase", 1);
command.Parameters.AddWithValue("@imgTipo", "Fedora");
command.ExecuteNonQuery();
PermissoesRule.Current.GetODEffectivePermissions(" FROM #odsTemp ", IDLoginTrustee, conn);
command.CommandText = "DELETE FROM #odsTemp WHERE ID IN (SELECT DISTINCT ID FROM #effective WHERE IsGrant = @IsGrant OR IsGrant IS NULL)";
command.Parameters.AddWithValue("@IsGrant", 0);
command.ExecuteNonQuery();
PermissoesRule.Current.DropEffectivePermissionsTempTable(conn);
command.CommandText = "SELECT * FROM #odsTemp";
var reader = command.ExecuteReader();
var od = new PermissoesRule.ObjDig();
while (reader.Read())
{
od = new PermissoesRule.ObjDig();
od.ID = reader.GetInt64(0);
od.pid = reader.GetString(1);
od.titulo = reader.GetString(2);
res.Add(od);
}
reader.Close();
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(CurrentDataSet.Tables["ObjetoDigital"],
"INNER JOIN #odsTemp ON #odsTemp.ID = ObjetoDigital.ID ");
da.Fill(CurrentDataSet, "ObjetoDigital");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(CurrentDataSet.Tables["TrusteeUser"]);
da.Fill(CurrentDataSet, "TrusteeUser");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(CurrentDataSet.Tables["TrusteeGroup"]);
da.Fill(CurrentDataSet, "TrusteeGroup");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(CurrentDataSet.Tables["UserGroups"]);
da.Fill(CurrentDataSet, "UserGroups");
// carregar permissões
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(CurrentDataSet.Tables["TrusteeObjetoDigitalPrivilege"],
"INNER JOIN #odsTemp ON #odsTemp.ID = TrusteeObjetoDigitalPrivilege.IDObjetoDigital " +
"WHERE TrusteeObjetoDigitalPrivilege.IDTrustee = " + IDTrustee);
da.Fill(CurrentDataSet, "TrusteeObjetoDigitalPrivilege");
command.CommandText = "DROP TABLE #temp; DROP TABLE #odsTemp; ";
command.ExecuteNonQuery();
}
return(res);
}
public override List <DocumentoMovimentado> GetDocumentos(long IDReq, string filter, DataSet currentDataSet, IDbConnection conn)
{
List <DocumentoMovimentado> ret = new List <DocumentoMovimentado>();
using (SqlCommand command = new SqlCommand("", (SqlConnection)conn))
{
command.CommandText = "CREATE TABLE #NiveisTemp (IDNivel BIGINT); " +
"CREATE TABLE #SPParametersNiveis (IDNivel BIGINT); " +
"CREATE TABLE #SPResultsCodigos (IDNivel BIGINT, CodigoCompleto NVARCHAR(300));";
command.ExecuteNonQuery();
command.CommandText =
"INSERT INTO #NiveisTemp " +
"SELECT dr.IDNivel " +
"FROM DocumentosMovimentados dr " +
"WHERE dr.IDMovimento = @IDReq AND dr.isDeleted=@isDeleted";
command.Parameters.AddWithValue("@IDReq", IDReq);
command.Parameters.AddWithValue("@isDeleted", 0);
command.ExecuteNonQuery();
using (SqlDataAdapter da = new SqlDataAdapter(command))
{
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["Nivel"],
"INNER JOIN #NiveisTemp ON #NiveisTemp.IDNivel = Nivel.ID");
da.Fill(currentDataSet, "Nivel");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["DocumentosMovimentados"],
"WHERE IDMovimento = @IDReq");
da.Fill(currentDataSet, "DocumentosMovimentados");
}
command.CommandText = "INSERT INTO #SPParametersNiveis " +
"SELECT IDNivel " +
"FROM #NiveisTemp " +
"EXEC sp_getCodigosCompletosNiveis ";
command.ExecuteNonQuery();
command.CommandText =
"SELECT DISTINCT nt.IDNivel, nd.Designacao, tnr.Designacao, dp.InicioAno, dp.InicioMes, dp.InicioDia, dp.FimAno, dp.FimMes, dp.FimDia, codigo.CodigoCompleto " +
"FROM #NiveisTemp nt " +
"INNER JOIN NivelDesignado nd ON nt.IDNivel = nd.ID AND nd.isDeleted = @isDeleted " +
"INNER JOIN RelacaoHierarquica rh ON nt.IDNivel = rh.ID AND rh.isDeleted = @isDeleted " +
"INNER JOIN TipoNivelRelacionado tnr ON rh.IDTipoNivelRelacionado = tnr.ID AND tnr.isDeleted = @isDeleted " +
"INNER JOIN FRDBase frd ON frd.IDNivel = nt.IDNivel AND frd.isDeleted = @isDeleted " +
"INNER JOIN ( " +
"SELECT #SPResultsCodigos.IDNivel, MIN(#SPResultsCodigos.CodigoCompleto) CodigoCompleto " +
"FROM #SPResultsCodigos " +
"INNER JOIN #NiveisTemp p ON p.IDNivel = #SPResultsCodigos.IDNivel " +
"GROUP BY #SPResultsCodigos.IDNivel " +
") codigo ON codigo.IDNivel = nt.IDNivel " +
"LEFT JOIN SFRDDatasProducao dp ON dp.IDFRDBase = frd.ID AND dp.isDeleted = @isDeleted ";
if (filter != string.Empty)
{
command.Parameters.AddWithValue("@ndDesignacao", filter);
command.CommandText += "WHERE nd.Designacao=@ndDesignacao";
}
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
DocumentoMovimentado dm = new DocumentoMovimentado();
dm.IDNivel = System.Convert.ToInt64(reader.GetValue(0));
dm.Designacao = reader.GetValue(1).ToString();
dm.NivelDescricao = reader.GetValue(2).ToString();
dm.AnoInicio = reader.GetValue(3).ToString();
dm.MesInicio = reader.GetValue(4).ToString();
dm.DiaInicio = reader.GetValue(5).ToString();
dm.AnoFim = reader.GetValue(6).ToString();
dm.MesFim = reader.GetValue(7).ToString();
dm.DiaFim = reader.GetValue(8).ToString();
dm.CodigoCompleto = reader.GetValue(9).ToString();
ret.Add(dm);
}
reader.Close();
command.CommandText =
"DROP TABLE #NiveisTemp " +
"DROP TABLE #SPParametersNiveis " +
"DROP TABLE #SPResultsCodigos ";
command.ExecuteNonQuery();
}
return(ret);
}
public override List <AutoEliminacao_UFsEliminadas> LoadUnidadesFisicasAvaliadas(DataSet currentDataSet, long aeID, IDbConnection conn)
{
this.LoadAutoEliminacaoUFsID(currentDataSet, aeID, (SqlConnection)conn);
List <AutoEliminacao_UFsEliminadas> rows = new List <AutoEliminacao_UFsEliminadas>();
using (SqlCommand command = SqlSyntax.CreateSelectCommandWithNoDeletedRowsParam((SqlConnection)conn))
using (SqlDataAdapter da = new SqlDataAdapter(command))
{
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["Nivel"],
"INNER JOIN #NiveisTemp ON #NiveisTemp.IDNivel = Nivel.ID ");
da.Fill(currentDataSet, "Nivel");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["NivelDesignado"],
"INNER JOIN #NiveisTemp ON #NiveisTemp.IDNivel = NivelDesignado.ID ");
da.Fill(currentDataSet, "NivelDesignado");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["LocalConsulta"]);
da.Fill(currentDataSet, "LocalConsulta");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["NivelUnidadeFisica"],
"INNER JOIN #NiveisTemp ON #NiveisTemp.IDNivel = NivelUnidadeFisica.ID ");
da.Fill(currentDataSet, "NivelUnidadeFisica");
command.CommandText =
"SELECT temp.IDNivel, nEDs.Codigo + '/' + nUFs.Codigo Codigo, ndUFs.Designacao, COALESCE(nuf.Eliminado, 0), df.MedidaAltura, df.MedidaLargura, df.MedidaProfundidade, tp.Designacao " +
"FROM #NiveisTemp temp " +
"INNER JOIN Nivel nUFs ON nUFs.ID = temp.IDNivel AND nUFs.isDeleted = @isDeleted " +
"INNER JOIN NivelDesignado ndUFs ON ndUFs.ID = temp.IDNivel AND ndUFs.isDeleted = @isDeleted " +
"INNER JOIN RelacaoHierarquica rh ON rh.ID = temp.IDNivel AND rh.isDeleted = @isDeleted " +
"INNER JOIN Nivel nEDs ON nEDs.ID = rh.IDUpper AND nEDs.isDeleted = @isDeleted " +
"LEFT JOIN NivelUnidadeFisica nuf ON nuf.ID = temp.IDNivel AND nuf.isDeleted = @isDeleted " +
"LEFT JOIN FRDBase frd ON frd.IDNivel = nUFs.ID AND frd.isDeleted = @isDeleted " +
"LEFT JOIN SFRDUFDescricaoFisica df ON df.IDFRDBase = frd.ID AND df.isDeleted = @isDeleted " +
"LEFT JOIN TipoMedida tp ON tp.ID = df.IDTipoMedida AND tp.isDeleted = @isDeleted";
SqlDataReader reader = command.ExecuteReader();
AutoEliminacao_UFsEliminadas ae;
while (reader.Read())
{
ae = new AutoEliminacao_UFsEliminadas();
ae.IDNivel = reader.GetInt64(0); // IDNivel
ae.codigo = reader.GetString(1); // Codigo
ae.designacao = reader.GetString(2); // Designacao
ae.paraEliminar = Convert.ToBoolean(reader.GetValue(3)); // Eliminar
if (reader.GetValue(4) != DBNull.Value)
{
ae.altura = System.Convert.ToDecimal(reader.GetValue(4)); // Altura
}
if (reader.GetValue(5) != DBNull.Value)
{
ae.largura = System.Convert.ToDecimal(reader.GetValue(5)); // Largura
}
if (reader.GetValue(6) != DBNull.Value)
{
ae.profundidade = System.Convert.ToDecimal(reader.GetValue(6)); // Profundidade
}
ae.tipoMedida = reader.GetValue(7).ToString(); // TipoMedida
rows.Add(ae);
}
reader.Close();
command.CommandText = "DROP TABLE #NiveisTemp; ";
command.ExecuteNonQuery();
}
return(rows);
}
