예제 #1
0
        public override void GetPidsPorNvl(List <string> IDNiveis, IDbConnection conn, out List <string> pids)
        {
            GisaDataSetHelperRule.ImportIDs(IDNiveis.Select(id => System.Convert.ToInt64(id)).ToArray(), conn);

            pids = new List <string>();

            var cmd     = @"
SELECT od.pid 
FROM (
	SELECT img.IDFRDBase
	FROM #temp
		INNER JOIN FRDBase frd ON frd.IDNivel = #temp.ID AND frd.isDeleted = 0
		INNER JOIN SFRDImagem img ON img.IDFRDBase = frd.ID AND img.Tipo = 'Fedora' AND img.isDeleted = 0
	GROUP BY img.IDFRDBase
	HAVING COUNT(img.IDFRDBase) = 1
) imgs
	INNER JOIN SFRDImagem img ON img.IDFRDBase = imgs.IDFRDBase AND img.Tipo = 'Fedora' AND img.isDeleted = 0
	INNER JOIN SFRDImagemObjetoDigital imgOD ON imgOD.IDFRDBase = img.IDFRDBase AND imgOD.idx = img.idx AND imgOD.isDeleted = 0
	INNER JOIN ObjetoDigital od ON od.ID = imgOD.IDObjetoDigital AND od.isDeleted = 0"    ;
            var command = new SqlCommand(cmd, (SqlConnection)conn);

            command.CommandText = cmd;

            var reader = command.ExecuteReader();

            while (reader.Read())
            {
                pids.Add(reader.GetValue(0).ToString());
            }

            reader.Close();
        }
예제 #2
0
        public override Dictionary <string, string> GetGisaIDs(List <string> pids, IDbConnection conn)
        {
            var gisa_ids = new Dictionary <string, string>();

            GisaDataSetHelperRule.ImportDesignacoes(pids.Distinct().ToArray(), conn);

            var command = new SqlCommand(string.Empty, (SqlConnection)conn);

            command.CommandType    = CommandType.Text;
            command.CommandTimeout = 500;
            command.CommandText    = @"
create table #ODTemp (ID bigint)

insert into #ODTemp
select od.ID
from ObjetoDigital od 
    inner join #temp on #temp.Designacao COLLATE LATIN1_GENERAL_CS_AS = od.pid COLLATE LATIN1_GENERAL_CS_AS
where od.isDeleted = 0";
            command.ExecuteNonQuery();

            command.CommandText = @"
select od.pid, frd.IDNivel 
from ObjetoDigital od 
	inner join #ODTemp T on T.ID = od.ID
	inner join SFRDImagemObjetoDigital imgOD on imgOD.IDObjetoDigital = od.ID and imgOD.isDeleted = 0
	inner join SFRDImagem img on img.IDFRDBase = imgOD.IDFRDBase and img.isDeleted = 0
	inner join FRDBase frd on frd.ID = img.IDFRDBase and frd.isDeleted = 0
where od.isDeleted = 0

select od.pid, frd.IDNivel 
from ObjetoDigital od 
	inner join #ODTemp T on T.ID = od.ID
	inner join ObjetoDigitalRelacaoHierarquica odrh on odrh.ID = od.ID and odrh.isDeleted = 0
	inner join ObjetoDigital odComp on odComp.ID = odrh.IDUpper and odComp.isDeleted = 0
	inner join SFRDImagemObjetoDigital imgOD on imgOD.IDObjetoDigital = odComp.ID and imgOD.isDeleted = 0
	inner join SFRDImagem img on img.IDFRDBase = imgOD.IDFRDBase and img.isDeleted = 0
	inner join FRDBase frd on frd.ID = img.IDFRDBase and frd.isDeleted = 0
where od.isDeleted = 0
";
            var reader = command.ExecuteReader();

            while (reader.Read())
            {
                gisa_ids[reader.GetString(0)] = reader.GetInt64(1).ToString();
            }

            reader.NextResult();

            while (reader.Read())
            {
                gisa_ids[reader.GetString(0)] = reader.GetInt64(1).ToString();
            }

            reader.Close();

            return(gisa_ids);
        }
예제 #3
0
        public override void LoadControloAuts(DataSet currentDataSet, string[] designacoes, IDbConnection conn)
        {
            GisaDataSetHelperRule.ImportDesignacoes(designacoes, conn);

            using (SqlCommand command = new SqlCommand(string.Empty, (SqlConnection)conn))
            {
                command.CommandText = "CREATE TABLE #aux (ID BIGINT, Termo NVARCHAR(768));";
                command.ExecuteNonQuery();

                command.Parameters.AddWithValue("@isDeleted", 0);
                command.CommandText =
                    "INSERT INTO #aux " +
                    "SELECT ID, Termo " +
                    "FROM Dicionario " +
                    "INNER JOIN #temp ON #temp.Designacao COLLATE LATIN1_GENERAL_CS_AS LIKE Termo COLLATE LATIN1_GENERAL_CS_AS " +
                    "WHERE isDeleted = @isDeleted";
                command.ExecuteNonQuery();

                using (SqlDataAdapter da = new SqlDataAdapter(command))
                {
                    da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["Dicionario"],
                                                                                     "INNER JOIN #aux ON #aux.ID = Dicionario.ID ");
                    da.Fill(currentDataSet, "Dicionario");

                    da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["ControloAut"],
                                                                                     "INNER JOIN ControloAutDicionario cad ON cad.IDControloAut = ControloAut.ID AND cad.isDeleted = @isDeleted " +
                                                                                     "INNER JOIN Dicionario d ON d.ID = cad.IDDicionario AND d.isDeleted = @isDeleted " +
                                                                                     "INNER JOIN #aux ON #aux.ID = d.ID ");
                    da.Fill(currentDataSet, "ControloAut");

                    da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["ControloAutDicionario"],
                                                                                     "INNER JOIN Dicionario d ON d.ID = ControloAutDicionario.IDDicionario AND d.isDeleted = @isDeleted " +
                                                                                     "INNER JOIN #aux ON #aux.ID = d.ID ");
                    da.Fill(currentDataSet, "ControloAutDicionario");

                    da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["Nivel"],
                                                                                     "INNER JOIN NivelControloAut nca ON nca.ID = Nivel.ID AND nca.isDeleted = @isDeleted " +
                                                                                     "INNER JOIN ControloAut ca ON ca.ID = nca.IDControloAut AND ca.isDeleted = @isDeleted " +
                                                                                     "INNER JOIN ControloAutDicionario cad ON cad.IDControloAut = ca.ID AND cad.isDeleted = @isDeleted " +
                                                                                     "INNER JOIN Dicionario d ON d.ID = cad.IDDicionario AND d.isDeleted = @isDeleted " +
                                                                                     "INNER JOIN #aux ON #aux.ID = d.ID ");
                    da.Fill(currentDataSet, "Nivel");

                    da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["NivelControloAut"],
                                                                                     "INNER JOIN ControloAut ca ON ca.ID = NivelControloAut.IDControloAut AND ca.isDeleted = @isDeleted " +
                                                                                     "INNER JOIN ControloAutDicionario cad ON cad.IDControloAut = ca.ID AND cad.isDeleted = @isDeleted " +
                                                                                     "INNER JOIN Dicionario d ON d.ID = cad.IDDicionario AND d.isDeleted = @isDeleted " +
                                                                                     "INNER JOIN #aux ON #aux.ID = d.ID ");
                    da.Fill(currentDataSet, "NivelControloAut");
                }

                command.CommandText = "DROP TABLE #aux";
                command.ExecuteNonQuery();
            }
        }
예제 #4
0
        public override void LoadUnidadesFisicas(DataSet currentDataSet, string[] codigos, IDbConnection conn)
        {
            GisaDataSetHelperRule.ImportDesignacoes(codigos, conn);

            using (SqlCommand command = new SqlCommand(string.Empty, (SqlConnection)conn))
            {
                command.CommandText = "CREATE TABLE #aux (ID BIGINT, Termo NVARCHAR(50))";
                command.ExecuteNonQuery();

                command.Parameters.AddWithValue("@isDeleted", 0);
                command.CommandText = @"
                    INSERT INTO #aux
                    SELECT n.ID, n.Codigo
                    FROM Nivel n
                        INNER JOIN #temp ON #temp.Designacao COLLATE LATIN1_GENERAL_CS_AS LIKE n.Codigo COLLATE LATIN1_GENERAL_CS_AS
                    WHERE n.isDeleted = @isDeleted";
                command.ExecuteNonQuery();

                using (SqlDataAdapter da = new SqlDataAdapter(command))
                {
                    da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["Nivel"],
                                                                                     "INNER JOIN #aux ON #aux.ID = Nivel.ID ");
                    da.Fill(currentDataSet, "Nivel");

                    da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["NivelDesignado"],
                                                                                     "INNER JOIN #aux ON #aux.ID = NivelDesignado.ID ");
                    da.Fill(currentDataSet, "NivelDesignado");

                    da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["FRDBase"],
                                                                                     "INNER JOIN #aux ON #aux.ID = FRDBase.IDNivel ");
                    da.Fill(currentDataSet, "FRDBase");

                    da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["RelacaoHierarquica"],
                                                                                     "INNER JOIN #aux ON #aux.ID = RelacaoHierarquica.ID ");
                    da.Fill(currentDataSet, "RelacaoHierarquica");

                    da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["Nivel"],
                                                                                     "INNER JOIN RelacaoHierarquica ON RelacaoHierarquica.IDUpper = Nivel.ID " +
                                                                                     "INNER JOIN #aux ON #aux.ID = RelacaoHierarquica.ID ");
                    da.Fill(currentDataSet, "Nivel");
                }

                command.CommandText = "DROP TABLE #aux";
                command.ExecuteNonQuery();
            }
        }
예제 #5
0
        public override void LoadDocumentos(DataSet currentDataSet, string[] designacoes, IDbConnection conn)
        {
            GisaDataSetHelperRule.ImportDesignacoes(designacoes, conn);

            using (SqlCommand command = new SqlCommand(string.Empty, (SqlConnection)conn))
            {
                command.CommandText = "CREATE TABLE #aux (ID BIGINT, Termo NVARCHAR(768))";
                command.ExecuteNonQuery();

                command.Parameters.AddWithValue("@isDeleted", 0);

                command.CommandText = @"
                    INSERT INTO #aux
                    SELECT n.ID, nd.Designacao
                    FROM Nivel n
                        INNER JOIN #temp ON #temp.Designacao = n.ID
                        INNER JOIN NivelDesignado nd ON nd.ID = n.ID AND nd.isDeleted = @isDeleted
                    WHERE n.isDeleted = @isDeleted";
                command.ExecuteNonQuery();

                using (SqlDataAdapter da = new SqlDataAdapter(command))
                {
                    da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["Nivel"],
                                                                                     "INNER JOIN #aux ON #aux.ID = Nivel.ID ");
                    da.Fill(currentDataSet, "Nivel");

                    da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["NivelDesignado"],
                                                                                     "INNER JOIN #aux ON #aux.ID = NivelDesignado.ID ");
                    da.Fill(currentDataSet, "NivelDesignado");

                    da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["RelacaoHierarquica"],
                                                                                     "INNER JOIN #aux ON #aux.ID = RelacaoHierarquica.ID ");
                    da.Fill(currentDataSet, "RelacaoHierarquica");

                    da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["Nivel"],
                                                                                     "INNER JOIN RelacaoHierarquica ON RelacaoHierarquica.IDUpper = Nivel.ID " +
                                                                                     "INNER JOIN #aux ON #aux.ID = RelacaoHierarquica.ID ");
                    da.Fill(currentDataSet, "Nivel");
                }

                command.CommandText = "DROP TABLE #aux";
                command.ExecuteNonQuery();
            }
        }
예제 #6
0
        public override decimal LoadDescricaoFisicaAndGetSomatorioLargura(DataSet currentDataSet, long[] ufIDs, IDbConnection conn)
        {
            GisaDataSetHelperRule.ImportIDs(ufIDs, conn);

            decimal total = 0;
            var     cmd   = @"
SELECT COUNT(df.MedidaLargura) 
FROM FRDBase frd 
    INNER JOIN #temp ON #temp.ID = frd.IDNivel
    LEFT JOIN SFRDUFDescricaoFisica df ON df.IDFRDBase = frd.ID AND df.isDeleted = @isDeleted
WHERE frd.isDeleted = @isDeleted";

            using (var command = new SqlCommand(cmd, (SqlConnection)conn))
                using (var da = new SqlDataAdapter(command))
                {
                    command.Parameters.AddWithValue("@isDeleted", 0);

                    total = System.Convert.ToDecimal(command.ExecuteScalar());

                    // carregar informação dos niveis documentais associados às ufs
                    da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["Nivel"],
                                                                                     "INNER JOIN FRDBase ON FRDBase.IDNivel = Nivel.ID " +
                                                                                     "INNER JOIN SFRDUnidadeFisica ON SFRDUnidadeFisica.IDFRDBase = FRDBase.ID " +
                                                                                     "INNER JOIN #temp ON #temp.ID = SFRDUnidadeFisica.IDNivel");
                    da.Fill(currentDataSet, "Nivel");

                    da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["FRDBase"],
                                                                                     "INNER JOIN SFRDUnidadeFisica ON SFRDUnidadeFisica.IDFRDBase = FRDBase.ID " +
                                                                                     "INNER JOIN #temp ON #temp.ID = SFRDUnidadeFisica.IDNivel");
                    da.Fill(currentDataSet, "FRDBase");

                    // carregar informação das ufs
                    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["SFRDUnidadeFisica"],
                                                                                     "INNER JOIN #temp ON #temp.ID = SFRDUnidadeFisica.IDNivel");
                    da.Fill(currentDataSet, "SFRDUnidadeFisica");
                }

            return(total);
        }
예제 #7
0
        public override Dictionary <long, Dictionary <string, byte> > CalculateEffectivePermissions(List <long> IDNiveis, long IDTrustee, IDbConnection conn)
        {
            GisaDataSetHelperRule.ImportIDs(IDNiveis.ToArray(), conn);

            var query = "FROM #temp";

            PermissoesRule.Current.GetEffectivePermissions(query, IDTrustee, conn);

            SqlCommand command = new SqlCommand(string.Empty, (SqlConnection)conn);

            command.CommandText = "SELECT * FROM #effective";
            var reader = command.ExecuteReader();

            var nivelPermissoes = GetEffectivePermissions(reader);

            PermissoesRule.Current.DropEffectivePermissionsTempTable(conn);

            return(nivelPermissoes);
        }
예제 #8
0
        public override void LoadDataCIPermissoes(DataSet CurrentDataSet, List <long> lstIDNivel, IDbConnection conn)
        {
            GisaDataSetHelperRule.ImportIDs(lstIDNivel.ToArray(), 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["TrusteeNivelPrivilege"],
                                                                                     "INNER JOIN #temp T ON T.ID = TrusteeNivelPrivilege.IDNivel ");
                    da.Fill(CurrentDataSet, "TrusteeNivelPrivilege");
                }
        }