IF OBJECT_ID ('DBA.procObra') IS NOT NULL
	DROP PROCEDURE DBA.procObra
GO

create procedure DBA.procObra(pCodempresa varchar(20) default '1',pDataInicial date,pDataFinal date,pTipoRelatorio varchar(1) default 'T')
begin
  declare local temporary table dados(
    CodEmpresa varchar(20) null,
    CodObra integer null,
    NomeObra varchar(100) null,
    SituacaoObra VARCHAR(1) DEFAULT 'A',
    Origem varchar(5) null,
    NomeOrigem varchar(50) null,
    OrigemForma varchar(5) null,
    NomeOrigemForma varchar(50) null,
    CodContaFinanceira varchar(6) null,
    NomeContaFinanceira varchar(60) null,
    StatusRateio varchar(1) null,
    NomeStatusRateio varchar(20) null,
    TipoLancamento varchar(1) null,
    Data date null,
    DataSincronizacao DATETIME null default now(*),
    Documento varchar(20) null,
    Valor numeric(19,5) null default 0,
    CodCentroCusto integer null,
    NomeCentroCusto varchar(50) null,
    CodContaContabil integer null,
    NomeContaContabil varchar(50) null,
    CodItemContabil integer null,
    NomeItemContabil varchar(50) null,
    HistoricoTitulo varchar(2000) null,
    Beneficiario varchar(100) null,
    CodContaPagar integer null,
    CodContaReceber integer null,
    CodTipoTitulo varchar(20) null,
    NomeTipoTitulo varchar(50) null,
    DataEmissao date null,
    DataVencimento date null,
    CodMovContaFinanceira integer null,
    CodPeriodoCaixa integer null,
    DataLancamento date null,
    NumeroCheque varchar(20) null,
    CodMovimentacaoEstoque integer null,
    CodTipoMovimentoEstoque integer null,
    TipoMovimentoEstoque varchar(1) null,
    CodProduto integer null,
    NomeProduto varchar(200) null,
    CodUnidadeMedida varchar(3) null,
    Quantidade numeric(19,5) null default 0,
    CodLocalArmazenagem integer null,
    NomeLocalArmazenagem varchar(100) null,
    Historico varchar(50) null,
    RevisaoObra VARCHAR(6) NULL DEFAULT '-',
    PercentualConclusaoObra numeric(19,2) null,
    NumeroContrato varchar(30) null,
    DataInicioObra date NULL,
    DataTerminoObra date null,
    ValorOrcadoObra numeric(19,5) null,
    ValorExecutadoObra numeric(19,5) null,
    StatusObra varchar(1) null,
    NomeStatusObra varchar(20) null,
    ObservacaoObra varchar(3000) null,
    CodClienteObra integer null,
    NomeClienteObra varchar(100) null,
    NumeroApartamentosObra integer null,
    EnderecoObra varchar(100) null,
    BairroObra varchar(100) null,
    QuadraObra varchar(3) null,
    LoteObra varchar(3) null,
    ComplementoObra varchar(200) null,
    CodMunicipioObra integer null,
    NomeMunicipioObra varchar(100) null,
    NumeroMatriculaObra varchar(20) null,
    EnergiaUnidadeConsumidoraObra varchar(20) null,
    AguaUnidadeConsumidoraObra varchar(20) null,
    NumeroAlvaraObra varchar(20) null,
    AreaConstruidaObra numeric(19,5) null,
    AreaTerrenoObra numeric(19,5) null,
    NumeroPavimentoObra integer null,
    NumeroHabiteSeObra varchar(20) null,
    RRTExecucaoObra integer null,
    RRTProjetoObra integer null,
    MatriculaCEIObra varchar(20) null,
    ProtocoloBombeiroObra varchar(20) null,
    AlvaraBombeiroObra varchar(20) null,
    ProfissionalAprovacaoObra varchar(50) null,
    CodItemObra integer null,
    CodEtapaObra integer null,
    CodTipoItemObra integer null,
    NomeItemObra varchar(500) null,
    DataPlanejadoInicioItemObra date null,
    DataPlanejadoTerminoItemObra date null,
    DataExecutadoInicioItemObra date null,
    DataExecutadoTerminoItemObra date null,
    ValorOrcadoItemObra numeric(19,5) null,
    StatusItemObra varchar(1) null,
    NomeStatusItemObra varchar(20) null,
    ObservacaoItemObra varchar(3000) null,
    PercentualConclusaoItemObra numeric(19,2) null,
    CodOrdemItemObra integer null,
    CodItemObraCronograma integer null,
    NomeItemObraCronograma varchar(500) null,
    DataPlanejadoInicioItemObraCronograma date null,
    DataPlanejadoTerminoItemObraCronograma date null,
    DataExecutadoInicioItemObraCronograma date null,
    DataExecutadoTerminoItemObraCronograma date null,
    StatusItemObraCronograma varchar(1) null,
    NomeStatusItemObraCronograma varchar(20) null,
    ObservacaoItemObraCronograma varchar(3000) null,
    CodOrdemItemObraCronograma integer null,
    CodFornecedorItemObraCronograma integer null,
    CodClienteItemObraCronograma integer null,
    ServicoTercerizadoItemObraCronograma varchar(1) null,
    GeraContaReceberItemObraCronograma varchar(1) null,
    SaldoReceberItemObraCronograma numeric(19,5) null,
    CodContaReceberItemObraCronograma integer null,
    GeraContaPagarItemObraCronograma varchar(1) null,
    SaldoPagarItemObraCronograma numeric(19,5) null,
    CodContaPagarItemObraCronograma integer null,
    ObservacaoPlanejadoItemObraCronograma varchar(3000) null,
    ObservacaoExecutadoItemObraCronograma varchar(3000) null,
    CodResponsavelItemObraCronograma integer null,
    TipoOrigem varchar(1) null,
    TipoRelatorio varchar(1) null,
    GrupoNivel01 varchar(100) null,
    GrupoNivel02 varchar(100) null,
    GrupoNivel03 varchar(100) null,
    GrupoNivel04 varchar(100) null,
    ) on commit delete rows;
  if pTipoRelatorio is null then
    set pTipoRelatorio='T'
  end if;
  if pDataInicial is null then
    set pDataInicial='2000/01/01'
  end if;
  if pDataFinal is null then
    set pDataFinal='2099/01/01'
  end if;
  --Insere Temporarias
  select * into #Obra from Obra;
  select * into #ItemObra from ItemObra;
  select * into #ItemObraCronograma from ItemObraCronograma;
  select * into #procRateioMovimentoContabil from procRateioMovimentoContabil(pCodEmpresa,pDataInicial,pDataFinal,'A');
  select * into #MovimentacaoEstoque from MovimentacaoEstoque where DataMovimento between pDataInicial and pDataFinal;
  if pTipoRelatorio in( 'F','T') then
    --Insere dados da Rateio
    insert into dados with auto name select 'F' as TipoOrigem,pTipoRelatorio as TipoRelatorio,CodEmpresa,Data,CodObra,CodItemObraCronograma,Origem,NomeOrigem,OrigemForma,NomeOrigemForma,CodContaFinanceira,NomeContaFinanceira,StatusRateio,NomeStatusRateio,TipoLancamento,DataSincronizacao,Documento,Valor,CodCentroCusto,NomeCentroCusto,CodContaContabil,NomeContaContabil,CodItemContabil,NomeItemContabil,HistoricoTitulo,Beneficiario,CodContaPagar,CodContaReceber,CodTipoTitulo,NomeTipoTitulo,DataEmissao,DataVencimento,CodMovContaFinanceira,CodPeriodoCaixa,DataLancamento,NumeroCheque from #procRateioMovimentoContabil
  end if;
  if pTipoRelatorio in( 'E','T') then
    --Insere Estoque
    insert into dados with auto name select 'E' as TipoOrigem,pTipoRelatorio as TipoRelatorio,CodEmpresa,DataMovimento as DATA,CodTipoMovimentoEstoque,CodProduto,CodMovimentacaoEstoque,CodMovimentacaoEstoque as Documento,NumeroDocumento as NumeroCheque,Origem,CodUnidadeMedida,Quantidade,CodLocalArmazenagem,ValorUnitario as Valor,Observacao as HistoricoTitulo,CodObra,CodEtapaObra from #MovimentacaoEstoque
  end if;
  --Atualiza e Insere dados da ItemObraCronograma
  update dados as d join #ItemObraCronograma as I on(d.CodItemObraCronograma = I.CodItemObraCronograma) set d.CodItemObra = I.CodItemObra,d.NomeItemObraCronograma = I.Nome,d.DataPlanejadoInicioItemObraCronograma = I.DataPlanejadoInicio,d.DataPlanejadoTerminoItemObraCronograma = I.DataPlanejadoTermino,d.DataExecutadoInicioItemObraCronograma = I.DataExecutadoInicio,d.DataExecutadoTerminoItemObraCronograma = I.DataExecutadoTermino,d.StatusItemObraCronograma = I.Status,d.ObservacaoItemObraCronograma = I.Observacao,d.CodOrdemItemObraCronograma = I.CodOrdem,d.CodFornecedorItemObraCronograma = I.CodFornecedor,d.CodClienteItemObraCronograma = I.CodCliente,d.ServicoTercerizadoItemObraCronograma = I.ServicoTercerizado,d.ObservacaoPlanejadoItemObraCronograma = I.ObservacaoPlanejado,d.ObservacaoExecutadoItemObraCronograma = I.ObservacaoExecutado;
  insert into dados with auto name select pTipoRelatorio as TipoRelatorio,CodEmpresa,CodItemObraCronograma,CodItemObra,CodObra,Nome as NomeItemObraCronograma,DataPlanejadoInicio as DataPlanejadoInicioItemObraCronograma,DataPlanejadoTermino as DataPlanejadoTerminoItemObraCronograma,DataExecutadoInicio as DataExecutadoInicioItemObraCronograma,DataExecutadoTermino as DataExecutadoTerminoItemObraCronograma,Status as StatusItemObraCronograma,Observacao as ObservacaoItemObraCronograma,CodOrdem as CodOrdemItemObraCronograma,CodFornecedor as CodFornecedorItemObraCronograma,CodCliente as CodClienteItemObraCronograma,ServicoTercerizado as ServicoTercerizadoItemObraCronograma,CodContaReceber as CodContaReceberItemObraCronograma,CodContaPagar as CodContaPagarItemObraCronograma,ObservacaoPlanejado as ObservacaoPlanejadoItemObraCronograma,ObservacaoExecutado as ObservacaoExecutadoItemObraCronograma,CodResponsavel as CodResponsavelItemObraCronograma from #ItemObraCronograma as I where not exists(select CodItemObraCronograma from dados as d where d.CodItemObraCronograma = I.CodItemObraCronograma);
  --Atualiza Etapa da Obra e Inclui Etapas sem ItemObraCronograma
  update dados as d join #ItemObra as I on(d.CodItemObra = I.CodItemObra and d.CodObra = I.CodObra) set d.NomeItemObra = I.Nome,d.DataPlanejadoInicioItemObra = I.DataPlanejadoInicio,d.DataPlanejadoTerminoItemObra = I.DataPlanejadoTermino,d.DataExecutadoInicioItemObra = I.DataExecutadoInicio,d.DataExecutadoTerminoItemObra = I.DataExecutadoTermino,d.ValorOrcadoItemObra = I.ValorOrcado,d.StatusItemObra = I.Status,d.ObservacaoItemObra = I.Observacao,d.PercentualConclusaoItemObra = I.PercentualConclusao,d.CodOrdemItemObra = I.CodOrdem;
  insert into dados with auto name select pTipoRelatorio as TipoRelatorio,I.CodEmpresa,I.CodItemObra,I.CodObra,I.Nome as NomeItemObra,I.DataPlanejadoInicio as DataPlanejadoInicioItemObra,I.DataPlanejadoTermino as DataPlanejadoTerminoItemObra,I.DataExecutadoInicio as DataExecutadoInicioItemObra,I.DataExecutadoTermino as DataExecutadoTerminoItemObra,I.ValorOrcado as ValorOrcadoItemObra,I.Status as StatusItemObra,I.Observacao as ObservacaoItemObra,I.PercentualConclusao as PercentualConclusaoItemObra,I.CodOrdem as CodOrdemItemObra from #ItemObra as I where not exists(select CodItemObra from dados as d where I.CodItemObra = d.CodItemObra);
  --Atualiza Obra e Inclui obras sem ItemObra
  update dados as d join #Obra as O on(d.CodObra = O.CodObra) set d.PercentualConclusaoObra = O.PercentualConclusao,d.NumeroContrato = O.NumeroContrato,d.DataInicioObra = O.DataInicio,d.DataTerminoObra = O.DataTermino,d.ValorOrcadoObra = O.ValorOrcado,d.ValorExecutadoObra = O.ValorExecutado,d.StatusObra = O.Status,d.ObservacaoObra = O.Observacao,d.NomeObra = O.Nome,d.CodClienteObra = O.CodCliente,d.NumeroApartamentosObra = O.NumeroApartamentos,d.EnderecoObra = O.Endereco,d.BairroObra = O.Bairro,d.QuadraObra = O.Quadra,d.LoteObra = O.Lote,d.ComplementoObra = O.Complemento,d.CodMunicipioObra = O.CodMunicipio,d.NumeroMatriculaObra = O.NumeroMatricula,d.EnergiaUnidadeConsumidoraObra = O.EnergiaUnidadeConsumidora,d.AguaUnidadeConsumidoraObra = O.AguaUnidadeConsumidora,d.NumeroAlvaraObra = O.NumeroAlvara,d.AreaConstruidaObra = O.AreaConstruida,d.AreaTerrenoObra = O.AreaTerreno,d.NumeroPavimentoObra = O.NumeroPavimento,d.NumeroHabiteSeObra = O.NumeroHabiteSe,d.RRTExecucaoObra = O.RRTExecucao,d.RRTProjetoObra = O.RRTProjeto,d.MatriculaCEIObra = O.MatriculaCEI,d.ProtocoloBombeiroObra = O.ProtocoloBombeiro,d.AlvaraBombeiroObra = O.AlvaraBombeiro,d.ProfissionalAprovacaoObra = O.ProfissionalAprovacao, d.RevisaoObra = O.Revisao, d.SituacaoObra = o.SituacaoObra;
  insert into dados with auto name select pTipoRelatorio as TipoRelatorio,O.CodEmpresa as CodEmpresa,O.CodObra as CodObra,O.PercentualConclusao as PercentualConclusaoObra,O.NumeroContrato as NumeroContrato,O.DataInicio as DataInicioObra,O.DataTermino as DataTerminoObra,O.ValorOrcado as ValorOrcadoObra,O.ValorExecutado as ValorExecutadoObra,O.Status as StatusObra,O.Observacao as ObservacaoObra,O.Nome as NomeObra,O.CodCliente as CodClienteObra,O.NumeroApartamentos as NumeroApartamentosObra,O.Endereco as EnderecoObra,O.Bairro as BairroObra,O.Quadra as QuadraObra,O.Lote as LoteObra,O.Complemento as ComplementoObra,O.CodMunicipio as CodMunicipioObra,O.NumeroMatricula as NumeroMatriculaObra,O.EnergiaUnidadeConsumidora as EnergiaUnidadeConsumidoraObra,O.AguaUnidadeConsumidora as AguaUnidadeConsumidoraObra,O.NumeroAlvara as NumeroAlvaraObra,O.AreaConstruida as AreaConstruidaObra,O.AreaTerreno as AreaTerrenoObra,O.NumeroPavimento as NumeroPavimentoObra,O.NumeroHabiteSe as NumeroHabiteSeObra,O.RRTExecucao as RRTExecucaoObra,O.RRTProjeto as RRTProjetoObra,O.MatriculaCEI as MatriculaCEIObra,O.ProtocoloBombeiro as ProtocoloBombeiroObra,O.AlvaraBombeiro as AlvaraBombeiroObra,O.ProfissionalAprovacao as ProfissionalAprovacaoObra from #Obra as O where not exists(select d.CodObra from dados as d where d.CodObra = O.CodObra);
  --Atualiza Status
  update dados set NomeStatusObra = (case StatusObra when 'A' then 'PENDENTE' when 'F' then 'CONCLUÍDO' when 'C' then 'CANCELADO' end),NomeStatusItemObra = (case StatusItemObra when 'A' then 'PENDENTE' when 'F' then 'CONCLUÍDO' when 'C' then 'CANCELADO' end),NomeStatusItemObraCronograma = (case StatusItemObraCronograma when 'A' then 'PENDENTE' when 'F' then 'CONCLUÍDO' when 'C' then 'CANCELADO' end);
  --Atualiza Cliente da Obra
  update dados as d join Cliente as C on(d.CodClienteObra = C.CodCliente) set d.NomeClienteObra = C.NomeFantasia;
  --Atualiza Produto
  update dados as d join produto as p on(d.CodProduto = p.CodProduto) set d.NomeProduto = p.Nome;
  --Atualiza Tipo Movimento Estoque
  update dados as d join TipoMovimentoEstoque as T on(d.CodTipoMovimentoEstoque = T.CodTipoMovimentoEstoque) set d.TipoMovimentoEstoque = T.Tipo;
  --Atualiza GrupoNivel01
  update dados set GrupoNivel01 = strzero(CodObra,2) || ' - ' || NomeObra where NomeObra is not null;
  update dados set GrupoNivel01 = '99 - OUTROS' where NomeObra is null;
  --Atualiza GrupoNivel02
  update dados set GrupoNivel02 = strzero(CodOrdemItemObra,2) || ' - ' || NomeItemObra where NomeItemObra is not null;
  update dados set GrupoNivel02 = '99 - OUTROS' where NomeItemObra is null;
  update dados set GrupoNivel02 = '00 - RECEBIMENTOS' where TipoLancamento = 'C';
  update dados set GrupoNivel02 = 'MOVIMENTAÇÃO DE ESTOQUE' where TIpoOrigem = 'E';
  --Atualiza GrupoNivel03
  update dados set GrupoNivel03 = strzero(CodOrdemItemObra,2) || '.' || strzero(CodOrdemItemObraCronograma,2) || ' - ' || NomeItemObraCronograma where NomeItemObraCronograma is not null;
  update dados set GrupoNivel03 = '99 - OUTROS' where NomeItemObraCronograma is null and NomeItemObra is not null and GrupoNivel04 is not null;
  --Atualiza GrupoNivel04
  update dados set GrupoNivel04 = strzero(CodOrdemItemObra,2) || '.' || strzero(CodOrdemItemObraCronograma,2) || '.' || NomeOrigemForma where NomeOrigemForma is not null;
  update dados set GrupoNivel04 = '99 - OUTROS' where NomeOrigemForma is null and NomeItemObraCronograma is not null;
  update dados set GrupoNivel04 = (case TipoMovimentoEstoque when 'E' then '01 - ENTRADA' when 'S' then '02 - SAÍDA' end) where TIpoOrigem = 'E';
  select * from dados order by(case when CodObra is null then 1 else 0 end) asc,GrupoNivel01 asc,(case when TipoLancamento = 'D' and CodItemObra is null then 1 else 0 end) asc,GrupoNivel02 asc,(case when CodItemObraCronograma is null then 1 else 0 end) asc,GrupoNivel03 asc,(case when NomeOrigemForma is null then 1 else 0 end) asc,GrupoNivel04 asc,DATA asc
end
GO

SELECT * FROM procObra('1',NULL,NULL,'T')