public override List<UFRule.UFsAssociadas> LoadDetalhesUF(DataSet currentDataSet, string frdID, IDbConnection conn) { List<UFRule.UFsAssociadas> result = new List<UFRule.UFsAssociadas>(); using (SqlCommand command = new SqlCommand(string.Empty, (SqlConnection)conn)) { command.Parameters.AddWithValue("@frdID", frdID); command.Parameters.AddWithValue("@isDeleted", 0); command.Parameters.AddWithValue("@eliminado", 0); command.CommandText = "SELECT nEDs.Codigo + '/' + nUFs.Codigo Codigo, ndUFs.Designacao, dp.InicioAno, dp.InicioMes, dp.InicioDia, dp.InicioAtribuida, dp.FimAno, dp.FimMes, dp.FimDia, dp.FimAtribuida, c.Cota, sfrduf.Cota " + "FROM SFRDUnidadeFisica sfrduf " + "INNER JOIN Nivel nUFs ON nUFs.ID = sfrduf.IDNivel AND nUFs.isDeleted = @isDeleted " + "INNER JOIN NivelDesignado ndUFs ON ndUFs.ID = nUFs.ID AND ndUFs.isDeleted = @isDeleted " + "LEFT JOIN NivelUnidadeFisica nuf ON nuf.ID = nUFs.ID AND nuf.isDeleted = @isDeleted AND (nuf.Eliminado = @eliminado OR nuf.Eliminado IS NULL) " + "INNER JOIN RelacaoHierarquica rh ON rh.ID = nUFs.ID AND rh.isDeleted = @isDeleted " + "INNER JOIN Nivel nEDs ON nEDs.ID = rh.IDUpper AND nEDs.isDeleted = @isDeleted " + "INNER JOIN FRDBase frd ON frd.IDNivel = nUFs.ID AND frd.isDeleted = @isDeleted " + "LEFT JOIN SFRDDatasProducao dp ON dp.IDFRDBase = frd.ID AND dp.isDeleted = @isDeleted " + "LEFT JOIN SFRDUFCota c ON c.IDFRDBase = frd.ID AND c.isDeleted = @isDeleted " + "WHERE sfrduf.IDFRDBase = @frdID AND sfrduf.isDeleted = @isDeleted "; SqlDataReader reader = command.ExecuteReader(); UFRule.UFsAssociadas ufAssociada; while (reader.Read()) { ufAssociada = new UFRule.UFsAssociadas(); ufAssociada.Codigo = reader.GetValue(0).ToString(); ufAssociada.Designacao = reader.GetValue(1).ToString(); ufAssociada.DPInicioAno = reader.GetValue(2).ToString(); ufAssociada.DPInicioMes = reader.GetValue(3).ToString(); ufAssociada.DPInicioDia = reader.GetValue(4).ToString(); if (reader.GetValue(5) != DBNull.Value) ufAssociada.DPInicioAtribuida = System.Convert.ToBoolean(reader.GetValue(5)); else ufAssociada.DPInicioAtribuida = false; ufAssociada.DPFimAno = reader.GetValue(6).ToString(); ufAssociada.DPFimMes = reader.GetValue(7).ToString(); ufAssociada.DPFimDia = reader.GetValue(8).ToString(); if (reader.GetValue(9) != DBNull.Value) ufAssociada.DPFimAtribuida = System.Convert.ToBoolean(reader.GetValue(9)); else ufAssociada.DPInicioAtribuida = false; ufAssociada.Cota = reader.GetValue(10).ToString(); ufAssociada.CotaDocumento = reader.GetValue(11).ToString(); result.Add(ufAssociada); } reader.Close(); } return result; }
public override List<UFRule.UFsAssociadas> LoadOIDimensoesSuporteData(DataSet currentDataSet, long CurrentFRDBaseID, IDbConnection conn) { var result = new List<UFRule.UFsAssociadas>(); using (SqlCommand command = SqlSyntax.CreateSelectCommandWithNoDeletedRowsParam((SqlConnection)conn)) using (SqlDataAdapter da = new SqlDataAdapter(command)) { command.Parameters.AddWithValue("@IDFRDBase", CurrentFRDBaseID); command.Parameters.AddWithValue("@NufEliminado", 1); command.Parameters.AddWithValue("@IDTipoNivel", 1); // carregamento das notas das associações da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["SFRDDimensaoSuporte"], "WHERE SFRDDimensaoSuporte.IDFRDBase=@IDFRDBase"); da.Fill(currentDataSet, "SFRDDimensaoSuporte"); // carregar entidades detentoras da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["Nivel"], "WHERE IDTipoNivel = @IDTipoNivel"); da.Fill(currentDataSet, "Nivel"); command.CommandText = @" with TMP (ID, IDUpper) as ( select ID, IDUpper FROM RelacaoHierarquica where ID = (select IDNivel from FRDBase where ID = @IDFRDBase) and isDeleted=@isDeleted union all select rh.ID, rh.IDUpper from RelacaoHierarquica rh inner join TMP on TMP.IDUpper = rh.ID where rh.isDeleted = @isDeleted ) select Codigo from Nivel where ID in ( select distinct TMP.IDUpper from TMP left join RelacaoHierarquica rh on rh.ID = TMP.IDUpper and rh.isDeleted=0 where rh.ID is null) and IDTipoNivel = 1"; var edCod = command.ExecuteScalar().ToString(); command.Parameters.AddWithValue("@EdCod", edCod); command.CommandText = @" SELECT n.ID IDNivel, @EdCod + '/' + n.Codigo Codigo, nd.Designacao Designacao, COALESCE(ta.Designacao, '') TipoAcondicionamento, COALESCE(df.MedidaLargura, 0,000) Largura, COALESCE(df.MedidaAltura, 0,000) Altura, COALESCE(df.MedidaProfundidade, 0,000) Profundidade, COALESCE(ct.Cota, '') Cota, COALESCE(dp.InicioAno, '') InicioAno, COALESCE(dp.InicioMes, '') InicioMes, COALESCE(dp.InicioDia, '') InicioDia, COALESCE(dp.InicioAtribuida, 0) InicioAtribuida, COALESCE(dp.FimAno, '') FimAno, COALESCE(dp.FimMes, '') FimMes, COALESCE(dp.FimDia, '') FimDia, COALESCE(dp.FimAtribuida, 0) FimAtribuida, COALESCE(nuf.Eliminado, 0) Eliminado, COALESCE(tm.Designacao, '') Medida, '' Autos, frduf.ID IDFRDBase into #ufs FROM FRDBase frd INNER JOIN SFRDUnidadeFisica sfrduf ON sfrduf.IDFRDBase = frd.ID AND sfrduf.isDeleted=@isDeleted INNER JOIN Nivel n ON n.ID = sfrduf.IDNivel AND n.isDeleted=@isDeleted INNER JOIN NivelDesignado nd ON nd.ID = n.ID AND nd.isDeleted=@isDeleted INNER JOIN NivelUnidadeFisica nuf ON nuf.ID = n.ID AND nuf.isDeleted=@isDeleted INNER JOIN FRDBase frduf ON frduf.IDNivel = n.ID AND frduf.isDeleted=@isDeleted LEFT JOIN SFRDUFDescricaoFisica df ON df.IDFRDBase = frduf.ID AND df.isDeleted=@isDeleted LEFT JOIN SFRDUFCota ct ON ct.IDFRDBase = frduf.ID AND ct.isDeleted=@isDeleted LEFT JOIN SFRDDatasProducao dp ON dp.IDFRDBase = frduf.ID AND dp.isDeleted=@isDeleted LEFT JOIN TipoAcondicionamento ta ON ta.ID = df.IDTipoAcondicionamento LEFT JOIN TipoMedida tm ON tm.ID = df.IDTipoMedida WHERE frd.isDeleted=@isDeleted AND frd.ID=@IDFRDBase update #ufs set Autos = LEFT(o1.list, LEN(o1.list)-1) from #ufs U CROSS APPLY ( SELECT CONVERT(VARCHAR(200), autos.Designacao) + '; ' AS [text()] FROM ( SELECT ae.Designacao FROM SFRDUFAutoEliminacao ufae INNER JOIN AutoEliminacao ae ON ae.ID = ufae.IDAutoEliminacao AND ae.isDeleted=@isDeleted WHERE ufae.isDeleted=@isDeleted AND ufae.IDFRDBase = U.IDFRDBase UNION SELECT ae.Designacao FROM SFRDUnidadeFisica sfrduf INNER JOIN FRDBase frdNvlDoc ON frdNvlDoc.ID = sfrduf.IDFRDBase AND frdNvlDoc.isDeleted=@isDeleted INNER JOIN SFRDAvaliacao av ON av.IDFRDBase = frdNvlDoc.ID AND av.isDeleted=@isDeleted INNER JOIN AutoEliminacao ae ON ae.ID = av.IDAutoEliminacao AND ae.isDeleted=@isDeleted WHERE sfrduf.isDeleted=@isDeleted AND sfrduf.IDNivel = U.IDNivel ) autos GROUP BY autos.Designacao FOR XML PATH('') ) o1 (list) where U.Eliminado=@NufEliminado select * from #ufs order by IDNivel desc drop table #ufs"; SqlDataReader reader = command.ExecuteReader(); var ufAssociada = default(UFRule.UFsAssociadas); while (reader.Read()) { ufAssociada = new UFRule.UFsAssociadas(); ufAssociada.ID = reader.GetInt64(0); ufAssociada.Codigo = reader.GetString(1); ufAssociada.Designacao = reader.GetString(2); ufAssociada.TipoAcondicionamento = reader.GetString(3); ufAssociada.Largura = reader.GetDecimal(4); ufAssociada.Altura = reader.GetDecimal(5); ufAssociada.Profundidade = reader.GetDecimal(6); ufAssociada.Cota = reader.GetString(7); ufAssociada.DPInicioAno = reader.GetString(8); ufAssociada.DPInicioMes = reader.GetString(9); ufAssociada.DPInicioDia = reader.GetString(10); ufAssociada.DPInicioAtribuida = System.Convert.ToBoolean(reader.GetValue(11)); ufAssociada.DPFimAno = reader.GetString(12); ufAssociada.DPFimMes = reader.GetString(13); ufAssociada.DPFimDia = reader.GetString(14); ufAssociada.DPFimAtribuida = System.Convert.ToBoolean(reader.GetValue(15)); ufAssociada.Eliminado = System.Convert.ToBoolean(reader.GetValue(16)); ufAssociada.TipoMedida = reader.GetString(17); ufAssociada.AutosAssociados = reader.IsDBNull(18) ? string.Empty : reader.GetString(18); result.Add(ufAssociada); } reader.Close(); } return result; }