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