USE [TryNFeOnline]
GO

ALTER FUNCTION [dbo].[EstoqueProdutos] 
(
	@dataInf Datetime = null,
	@idEmitenteInf int,
	@idProdutoInf int = 0,
	@idProdutoClassificacaoInf int = 0,
	@idContaInf int = 0, 
	@idGrupoInf int = 0,
	@idnMostrarProdutosBaixadosInf bit = 0,
	@idnMostrarProdutosQtdNegativaInf bit = 1,
	@idMarcaInf int = 0
)
RETURNS 
@Resultado TABLE 
(
		rowNumber int,
		idProduto int,
		idProdutoClassificacao int NULL,	
		dscProdutoClasse varchar(500),			
		qtd decimal(18,4),
		unidadeMedida varchar(30),
		codigoProdutoClasse varchar(50),
		precoCadastro decimal(18,4), 
		precoEntradaCadastro decimal(18,4),
		idGrupo int null,
		dscGrupo varchar(100) null,
		idConta int,
		dscConta varchar(200),
		precoCalculado decimal(18,4),
		custoCalculado decimal(18,4),
		refFabrica varchar(50),
		idMarca int,
		dscMarca varchar(200),
		tipoItem varchar(4),
		qtdMinimaEstoque decimal(18,4),
		idFornecedor int,
		idEmitente int NULL,
		alturaMM int,
		comprimentoMM int,
		larguraMM int,
		peso decimal(18,4),
		idnPrecoOferta bit,
		custoCalculadoLetras varchar(50)
)
AS
BEGIN
	
	declare @data datetime = @dataInf;
	declare @idEmitente int = @idEmitenteInf;
	declare @idProduto int = 0;
	declare @idProdutoClassificacao int = 0;
	declare @idConta int = 0;
	declare @idGrupo int = 0;
	declare @idnMostrarProdutosBaixados bit = @idnMostrarProdutosBaixadosInf;
	declare @idnMostrarProdutosQtdNegativa bit = @idnMostrarProdutosQtdNegativaInf;		
	declare @idMarca int = @idMarcaInf;
	
	if (@idGrupoInf > 0)
		SET @idGrupo = @idGrupoInf;
	
	if (@idContaInf > 0)
		SET @idConta = @idContaInf;
		
	if (@idProdutoClassificacaoInf > 0)
		SET @idProdutoClassificacao = @idProdutoClassificacaoInf;
	
	if (@idProdutoInf > 0)
		SET @idProduto = @idProdutoInf;
	
	if (@data is null)
		SET @data = (SELECT GETDATE());		

	declare @exibirCustoLetras bit;
	SET @exibirCustoLetras = (select idnMostrarCustoEmLetras from configuracoes);

	declare @ItensEntradaSaida TABLE
	(
		idProduto int,
		idProdutoClassificacao int,
		dscProduto varchar(500),
		codigoProdutoClasse varchar(50),
		preco decimal(18,4),
		precoCadastro decimal(18,4),
		precoEntradaCadastro decimal(18,4),
		unidadeMedida varchar(30),
		unidadeMedidaConvertida varchar(30),
		fatorConversao decimal(18,4),
		qtd decimal(18,4),
		idOperacao int,
		idnOperacao varchar(5),
		idConta int,
		dscConta varchar(200)		
	)
	
	--Notas de entrada / Saida
	INSERT INTO @ItensEntradaSaida
	select 
		np.idProduto, 
		np.idProdutoClassificacao, 
		(case when np.idProdutoClassificacao is not null then pr.descricao + ' - ' + coalesce(pc.descricao, '') else pr.descricao end),
		(case when np.idProdutoClassificacao is not null then cast(pr.id as varchar(47)) + REPLACE(STR(pc.sequencia, 3), SPACE(1), '0') else cast(pr.id as varchar(50)) end),
		coalesce(np.vuncom, 0),
		(case when np.idProdutoClassificacao is not null then (case when pc.vuncom > 0 then pc.vuncom else pr.vuncom end) else pr.vuncom end),
		(case when np.idProdutoClassificacao is not null then (case when pc.precoEntrada > 0 then pc.precoEntrada else pr.precoEntrada end) else pr.precoEntrada end),
		upper(coalesce(pr.ucom, '')),
		upper(coalesce(np.ucom, '')),
		--Se não tem a quantidade real a ser considerada para o estoque, e as unidades de medida são diferentes, seleciona o fator de conversão
		(case when pr.ucom <> np.ucom and coalesce(np.qcomEstoque, 0) = 0 then (select top 1 pu.fatorConversao from produtoUnidade pu where (pu.idProduto = np.idProduto) and (pu.unidadeMedida = np.ucom)) else 0 end) as fatorConversao,
		(case when 
			(case when ecp.idnOperacao = '=' then (case when op.idnES = 0 then '+' else '-' end) else ecp.idnOperacao end) = '-' then
			(case when coalesce(np.qcomEstoque, 0) != 0 then np.qcomEstoque else np.qcom end) * -1
			else
			(case when coalesce(np.qcomEstoque, 0) != 0 then np.qcomEstoque else np.qcom end) --Considera a quantidade a ser usada para o estoque caso tenha sido informada
			end),
		nf.idOperacao,
		(case when ecp.idnOperacao = '=' then (case when op.idnES = 0 then '+' else '-' end) else ecp.idnOperacao end),
		ecp.idConta, 
		ec.descricao
		from nfproduto np
		inner join notafiscal nf on nf.id = np.idNotaFiscal
		inner join nfemitente nfe on nfe.idnotafiscal = np.idnotafiscal
		inner join produto pr on pr.id = np.idProduto
		inner join operacao op on op.id = nf.idOperacao
		inner join EstoqueContaOperacao ecp on ecp.idOperacao = nf.idOperacao
		inner join EstoqueConta ec on ec.id = ecp.idConta
		left join produtoClassificacao pc on pc.id = np.idProdutoClassificacao
		where ((nf.nfeCstat = 100) or (nf.nfeCstat = 150))
		and   (nf.nfeTpAmb = 1)
		and   (cast(nf.dataEmissao as date) <= @data)
		and   ((op.idnNaoCalcularEstoque is null) or (op.idnNaoCalcularEstoque = 0))
		and   (nfe.idEmitente = @idEmitente)		
		and   ((np.idProduto = @idProduto) or (@idProduto = 0))
		and   ((np.idProdutoClassificacao = @idProdutoClassificacao) or (@idProdutoClassificacao = 0))
		and   ((np.idnItemCanceladoCF is null) or (np.idnItemCanceladoCF != 1))
		and   (pr.idnControlaEstoque = 1)
		and ((@idnMostrarProdutosBaixados = 1) OR (coalesce(pr.idnBaixa,0) = 0))
		and ((@idnMostrarProdutosBaixados = 1) OR (coalesce(pc.idnBaixado,0) = 0))
		and   ((ecp.idConta = @idConta) or (@idConta = 0))
	
	--Ajustes de estoque
	INSERT INTO @ItensEntradaSaida
		select 
			ea.idProduto, 
			ea.idProdutoClassificacao,
			(case when ea.idProdutoClassificacao is not null then pr.descricao + ' - ' + coalesce(pc.descricao, '') else pr.descricao end),
			(case when ea.idProdutoClassificacao is not null then cast(pr.id as varchar(20)) + REPLACE(STR(pc.sequencia, 3), SPACE(1), '0') else cast(pr.id as varchar(20)) end),
			ea.unitario, 
			(case when ea.idProdutoClassificacao is not null then (case when pc.vuncom > 0 then pc.vuncom else pr.vuncom end) else pr.vuncom end),
			(case when ea.idProdutoClassificacao is not null then (case when pc.precoEntrada > 0 then pc.precoEntrada else pr.precoEntrada end) else pr.precoEntrada end),
			upper(coalesce(pr.ucom,'')),
			upper(coalesce(ea.unidadeMedida, '')),
			(case when pr.ucom <> ea.unidadeMedida then (select top 1 pu.fatorConversao from produtoUnidade pu where (pu.idProduto = ea.idProduto) and (pu.unidadeMedida = ea.unidadeMedida)) else 0 end) as fatorConversao,
			(case when
				(case when ecp.idnOperacao = '=' then (case when ea.idnES = 0 then '+' else '-' end) else ecp.idnOperacao end) = '-' then
				ea.qtd * -1
				else
				ea.qtd end),
			ea.idOperacao,
			(case when ecp.idnOperacao = '=' then (case when ea.idnES = 0 then '+' else '-' end) else ecp.idnOperacao end),
			ecp.idConta, 
			ec.descricao
		from estoqueAjustes ea
		inner join produto pr on pr.id = ea.idProduto
		inner join operacao op on op.id = ea.idOperacao
		inner join EstoqueContaOperacao ecp on ecp.idOperacao = ea.idOperacao
		inner join EstoqueConta ec on ec.id = ecp.idConta
		left join ProdutoClassificacao pc on pc.id = ea.idProdutoClassificacao
		
		where (cast(ea.data as date) <= @data)
		and   (ea.idEmitente = @idEmitente)
		and   ((ea.idProduto = @idProduto) or (@idProduto = 0))
		and   ((ea.idProdutoClassificacao = @idProdutoClassificacao) or (@idProdutoClassificacao = 0))
		and   (pr.idnControlaEstoque = 1)
		and   ((op.idnNaoCalcularEstoque is null) or (op.idnNaoCalcularEstoque = 0))
		and ((@idnMostrarProdutosBaixados = 1) OR (coalesce(pr.idnBaixa,0) = 0))
		and ((@idnMostrarProdutosBaixados = 1) OR (coalesce(pc.idnBaixado,0) = 0))
		and   ((ecp.idConta = @idConta) or (@idConta = 0))
		
	--Produtos alocados nas ordens de servi?o
	INSERT INTO @ItensEntradaSaida
		select 
			osp.idProduto, 
			osp.idProdutoClassificacao,
			(case when osp.idProdutoClassificacao is not null then pr.descricao + ' - ' + coalesce(pc.descricao, '') else pr.descricao end),
			(case when osp.idProdutoClassificacao is not null then cast(pr.id as varchar(20)) + REPLACE(STR(pc.sequencia, 3), SPACE(1), '0') else cast(pr.id as varchar(20)) end),
			osp.precoUnitario, 
			(case when osp.idProdutoClassificacao is not null then (case when pc.vuncom > 0 then pc.vuncom else pr.vuncom end) else pr.vuncom end),
			(case when osp.idProdutoClassificacao is not null then (case when pc.precoEntrada > 0 then pc.precoEntrada else pr.precoEntrada end) else pr.precoEntrada end),
			upper(coalesce(pr.ucom, '')),
			upper(coalesce(osp.ucom, '')),
			(case when pr.ucom <> osp.ucom then (select top 1 pu.fatorConversao from produtoUnidade pu where (pu.idProduto = osp.idProduto) and (pu.unidadeMedida = osp.ucom)) else 0 end) as fatorConversao,
			(case when 
				(case when ecp.idnOperacao = '=' then (case when op.idnES = 0 then '+' else '-' end) else ecp.idnOperacao end) = '-' then
				osp.quantidade * -1 else osp.quantidade end),
			os.idOperacao,
			(case when ecp.idnOperacao = '=' then (case when op.idnES = 0 then '+' else '-' end) else ecp.idnOperacao end),
			ec.id,
			ec.descricao
		from OrdemServicoProduto osp
		inner join produto pr on pr.id = osp.idProduto
		inner join OrdemServico os on os.id = osp.idOrdemServico
		inner join operacao op on op.id = os.idOperacao
		inner join EstoqueContaOperacao ecp on ecp.idOperacao = os.idOperacao
		inner join EstoqueConta ec on ec.id = ecp.idConta
		left join ProdutoClassificacao pc on pc.id = osp.idProdutoClassificacao	
		where ((case when osp.dataHora is not null then cast(osp.dataHora as date) else cast(os.dataHora as date) end) <= @data)
		and   (os.idEmitente = @idEmitente)
		and   ((osp.idProduto = @idProduto) or (@idProduto = 0))
		and   ((osp.idProdutoClassificacao = @idProdutoClassificacao) or (@idProdutoClassificacao = 0))
		and   ((op.idnNaoCalcularEstoque is null) or (op.idnNaoCalcularEstoque = 0))
		and   (pr.idnControlaEstoque = 1)
		and ((@idnMostrarProdutosBaixados = 1) OR (coalesce(pr.idnBaixa,0) = 0))
		and ((@idnMostrarProdutosBaixados = 1) OR (coalesce(pc.idnBaixado,0) = 0))
		and   ((ecp.idConta = @idConta) or (@idConta = 0))
		and ((os.idnCancelada = 0) or (os.idnCancelada is null))

	--Pedidos
	INSERT INTO @ItensEntradaSaida
		select
		pp.idProduto,
		pp.idProdutoClassificacao,
		(case when pp.idProdutoClassificacao is not null then pr.descricao + ' - ' + coalesce(pc.descricao, '') else pr.descricao end),
		(case when pp.idProdutoClassificacao is not null then cast(pr.id as varchar(20)) + REPLACE(STR(pc.sequencia, 3), SPACE(1), '0') else cast(pr.id as varchar(20)) end),
		pp.vuncom, 
		(case when pp.idProdutoClassificacao is not null then (case when pc.vuncom > 0 then pc.vuncom else pr.vuncom end) else pr.vuncom end),
		(case when pp.idProdutoClassificacao is not null then (case when pc.precoEntrada > 0 then pc.precoEntrada else pr.precoEntrada end) else pr.precoEntrada end),
		upper(coalesce(pr.ucom, '')),
		upper(coalesce(pp.ucom, '')),
		(case when pr.ucom <> pp.ucom then (select top 1 pu.fatorConversao from produtoUnidade pu where (pu.idProduto = pp.idProduto) and (pu.unidadeMedida = pp.ucom)) else 0 end) as fatorConversao,
		(case when (case when ecp.idnOperacao = '=' then (case when op.idnES = 0 then '+' else '-' end) else ecp.idnOperacao end) = '-' then
			(pp.qcom - coalesce(pp.qtdFaturada, 0)) * -1
			else (pp.qcom - coalesce(pp.qtdFaturada, 0)) end),
		pe.idOperacao,
		(case when ecp.idnOperacao = '=' then (case when op.idnES = 0 then '+' else '-' end) else ecp.idnOperacao end),
		ec.id,
		ec.descricao
		from pedidoProduto pp
		inner join produto pr on pr.id = pp.idProduto
		inner join pedido pe on pe.id = pp.idPedido
		inner join operacao op on op.id = pe.idOperacao
		inner join EstoqueContaOperacao ecp on ecp.idOperacao = pe.idOperacao
		inner join EstoqueConta ec on ec.id = ecp.idConta
		left join ProdutoClassificacao pc on pc.id = pp.idProdutoClassificacao
		where (cast(pe.dataPedido as date) <= @data)
		and   (((pe.idnFaturado is null) or (pe.idnFaturado = 0)) or ((coalesce(pe.idnFaturado, 0) <> 0) and (pe.id not in (select idPedido from pedidoFaturado))))
		and   ((pe.idnCancelado is null) or (pe.idnCancelado = 0))
		and ((pe.idnProducao = 1)
		or (((pe.idnProducao is null) or (pe.idnProducao = 0)) and ((op.idnConsiderarEstoqueApenasPedidoEmProducao is null) or (op.idnConsiderarEstoqueApenasPedidoEmProducao = 0))))
		and   (pe.idEmitente = @idEmitente)
		and   ((pp.idProduto = @idProduto) or (@idProduto = 0))
		and   ((pp.idProdutoClassificacao = @idProdutoClassificacao) or (@idProdutoClassificacao = 0))
		and   ((op.idnNaoCalcularEstoque is null) or (op.idnNaoCalcularEstoque = 0))
		and   (pr.idnControlaEstoque = 1)
		and ((@idnMostrarProdutosBaixados = 1) OR (coalesce(pr.idnBaixa,0) = 0))
		and ((@idnMostrarProdutosBaixados = 1) OR (coalesce(pc.idnBaixado,0) = 0))
		and   ((ecp.idConta = @idConta) or (@idConta = 0))	

		
	--Condicional
	INSERT INTO @ItensEntradaSaida
		select 
			cp.idProduto, 
			cp.idProdutoClassificacao,
			(case when cp.idProdutoClassificacao is not null then pr.descricao + ' - ' + coalesce(pc.descricao, '') else pr.descricao end),
			(case when cp.idProdutoClassificacao is not null then cast(pr.id as varchar(20)) + REPLACE(STR(pc.sequencia, 3), SPACE(1), '0') else cast(pr.id as varchar(20)) end),
			cp.vuncom, 
			(case when cp.idProdutoClassificacao is not null then (case when pc.vuncom > 0 then pc.vuncom else pr.vuncom end) else pr.vuncom end),
			(case when cp.idProdutoClassificacao is not null then (case when pc.precoEntrada > 0 then pc.precoEntrada else pr.precoEntrada end) else pr.precoEntrada end),
			upper(coalesce(pr.ucom, '')),
			upper(coalesce(cp.ucom, '')),
			0 as fatorConversao,
			(case when (case when ecp.idnOperacao = '=' then (case when op.idnES = 0 then '+' else '-' end) else ecp.idnOperacao end) = '-' then
		      (cp.qcom - coalesce(cp.qtdDevolucao, 0)) * -1
		      else (cp.qcom - coalesce(cp.qtdDevolucao, 0)) end),
			co.idOperacao,
			(case when ecp.idnOperacao = '=' then (case when op.idnES = 0 then '+' else '-' end) else ecp.idnOperacao end),
			ec.id,
			ec.descricao
		from CondicionalProduto cp
		inner join produto pr on pr.id = cp.idProduto
		inner join Condicional co on co.id = cp.idCondicional		
		inner join operacao op on op.id = co.idOperacao
		inner join EstoqueContaOperacao ecp on ecp.idOperacao = co.idOperacao
		inner join EstoqueConta ec on ec.id = ecp.idConta
		left join ProdutoClassificacao pc on pc.id = cp.idProdutoClassificacao	
		where (cast(co.dataEmissao as date) <= @data)
		and   ((co.idnFechado is null) or (co.idnFechado = 0))
		and   (co.idEmitente = @idEmitente)
		and   ((cp.idProduto = @idProduto) or (@idProduto = 0))
		and   ((cp.idProdutoClassificacao = @idProdutoClassificacao) or (@idProdutoClassificacao = 0))
		and   ((op.idnNaoCalcularEstoque is null) or (op.idnNaoCalcularEstoque = 0))
		and   (pr.idnControlaEstoque = 1)
		and ((@idnMostrarProdutosBaixados = 1) OR (coalesce(pr.idnBaixa,0) = 0))
		and ((@idnMostrarProdutosBaixados = 1) OR (coalesce(pc.idnBaixado,0) = 0))
		and   ((ecp.idConta = @idConta) or (@idConta = 0))
		
	--Consignacao (Só existe entrada no caso da consignação
	INSERT INTO @ItensEntradaSaida
		select 
			cp.idProduto, 
			cp.idProdutoClassificacao,
			(case when cp.idProdutoClassificacao is not null then pr.descricao + ' - ' + coalesce(pc.descricao, '') else pr.descricao end),
			(case when cp.idProdutoClassificacao is not null then cast(pr.id as varchar(20)) + REPLACE(STR(pc.sequencia, 3), SPACE(1), '0') else cast(pr.id as varchar(20)) end),
			cp.vuncom, 
			(case when cp.idProdutoClassificacao is not null then (case when pc.vuncom > 0 then pc.vuncom else pr.vuncom end) else pr.vuncom end),
			(case when cp.idProdutoClassificacao is not null then (case when pc.precoEntrada > 0 then pc.precoEntrada else pr.precoEntrada end) else pr.precoEntrada end),
			upper(coalesce(pr.ucom, '')),
			upper(coalesce(cp.ucom, '')),
			0 as fatorConversao,
			(case when (case when ecp.idnOperacao = '=' then (case when op.idnES = 0 then '+' else '-' end) else ecp.idnOperacao end) = '-' then
		      cp.qcom * -1
		      else cp.qcom end),
			co.idOperacao,
			(case when ecp.idnOperacao = '=' then (case when op.idnES = 0 then '+' else '-' end) else ecp.idnOperacao end),
			ec.id,
			ec.descricao
		from ConsignacaoProduto cp
		inner join produto pr on pr.id = cp.idProduto
		inner join Consignacao co on co.id = cp.idConsignacao		
		inner join operacao op on op.id = co.idOperacao
		inner join EstoqueContaOperacao ecp on ecp.idOperacao = co.idOperacao
		inner join EstoqueConta ec on ec.id = ecp.idConta
		left join ProdutoClassificacao pc on pc.id = cp.idProdutoClassificacao	
		where (cast(co.data as date) <= @data)
		--and   ((co.idnFechado is null) or (co.idnFechado = 0))
		and   (co.idEmitente = @idEmitente)
		and   ((cp.idProduto = @idProduto) or (@idProduto = 0))
		and   ((cp.idProdutoClassificacao = @idProdutoClassificacao) or (@idProdutoClassificacao = 0))
		and   ((op.idnNaoCalcularEstoque is null) or (op.idnNaoCalcularEstoque = 0))
		and   (pr.idnControlaEstoque = 1)
		and ((@idnMostrarProdutosBaixados = 1) OR (coalesce(pr.idnBaixa,0) = 0))
		and ((@idnMostrarProdutosBaixados = 1) OR (coalesce(pc.idnBaixado,0) = 0))
		and   ((ecp.idConta = @idConta) or (@idConta = 0))		
		

	--atualiza a qtd dos produtos com unidade de medida diferente da definida no cadastro e efetua a convers?o conforme multiplicador	
	UPDATE @ItensEntradaSaida set qtd = qtd * fatorConversao												
	WHERE (unidadeMedidaConvertida <> '')
	and   (unidadeMedida <> unidadeMedidaConvertida)
	and (fatorConversao > 0)
	
	INSERT INTO @Resultado
		select /*case when @exibirCustoLetras = 1 then ROW_NUMBER() OVER (ORDER BY ies.idProduto) else 0 end*/
			   0,
			   ies.idProduto, 
			   ies.idProdutoClassificacao, 
			   ies.dscProduto,
			   sum(ies.qtd),
			   ies.unidadeMedida,
			   ies.codigoProdutoClasse,
			   ies.precoCadastro,
			   ies.precoEntradaCadastro,
			   pr.idGrupo,
			   pg.descricao,
			   ies.idConta,
			   ies.dscConta,
			   (select top 1 coalesce(valor, 0) from PrecoProduto(@idEmitente, ies.idProduto, ies.idProdutoClassificacao, @data)) as precoCalculado,
			   (select top 1 coalesce(valor, 0) from CustoProdutoData(@idEmitente, ies.idProduto, ies.idProdutoClassificacao, @data)) as custoCalculado,
			   (case when len(pc.refFabrica) > 0 then pc.refFabrica else pr.refFabrica end),
			   pr.idMarca,
			   mc.descricao as dscMarca,
			   pr.tipoItem,
			   coalesce((case when pc.qtdEstoqueMinima > 0 then pc.qtdEstoqueMinima else pr.qtdEstoqueMinima end), 0),
			   pr.idFornecedor,
			   pr.idEmitente,
			   coalesce((case when pc.alturaMM > 0 then pc.alturaMM else pr.alturaMM end), 0),
			   coalesce((case when pc.comprimentoMM > 0 then pc.comprimentoMM else pr.comprimentoMM end), 0),
			   coalesce((case when pc.larguraMM > 0 then pc.larguraMM else pr.larguraMM end), 0),
			   coalesce(pr.pesoProduto, 0),
			   (select top 1 coalesce(idnOferta, 0) from PrecoProduto(@idEmitente, ies.idProduto, ies.idProdutoClassificacao, @data)),
			   ''
		from @ItensEntradaSaida ies
		inner join produto pr on pr.id = ies.idProduto
		left join ProdutoClassificacao pc on pc.id = ies.idProdutoClassificacao
		left join marca mc on mc.id = pr.idMarca
		left join produtoGrupo pg on pg.id = pr.idGrupo
		WHERE ((pr.idGrupo in (select idGrupo from ProdutoGruposFilhos(@idGrupo))) or (@idGrupo = 0))
		and   ((pr.idMarca = @idMarca) or (@idMarca = 0))
	    group by ies.idProduto, ies.idProdutoClassificacao, ies.dscProduto, ies.unidadeMedida, ies.codigoProdutoClasse, ies.precoCadastro, ies.precoEntradaCadastro, pr.idGrupo, pg.descricao, ies.idConta, ies.dscConta, (case when len(pc.refFabrica) > 0 then pc.refFabrica else pr.refFabrica end), pr.idMarca, mc.descricao, pr.tipoItem, coalesce((case when pc.qtdEstoqueMinima > 0 then pc.qtdEstoqueMinima else pr.qtdEstoqueMinima end), 0), pr.idFornecedor, pr.idEmitente, coalesce((case when pc.alturaMM > 0 then pc.alturaMM else pr.alturaMM end), 0), coalesce((case when pc.comprimentoMM > 0 then pc.comprimentoMM else pr.comprimentoMM end), 0), coalesce((case when pc.larguraMM > 0 then pc.larguraMM else pr.larguraMM end), 0), coalesce(pr.pesoProduto, 0)

		/*

		Custo em letras removido da seleção. Estava causando muita lentidão em clientes com movimentação significativa
		*/

		/*if (@exibirCustoLetras = 1)
		begin
			DECLARE @X VARCHAR(50)
			DECLARE @count int = (SELECT COUNT(rowNumber) FROM @Resultado)
			DECLARE @row int = 1
	    
			WHILE (@row <= @count)
			BEGIN
				SET @X = (select CAST(custoCalculado as varchar(50)) from @Resultado WHERE rowNumber = @row)
				SELECT @X = REPLACE(@X,numero,letra)
				FROM CustoLetra
			
				UPDATE @Resultado SET custoCalculadoLetras = @X
				WHERE rowNumber = @row
			
				SET @row += 1
			END
		end*/
	
	
	/*select * from @Resultado r
	where (((r.qtd > 0) and (@idnMostrarProdutosQtdNegativa = 0)) or (@idnMostrarProdutosQtdNegativa = 1))*/
	RETURN 
END
GO
ALTER FUNCTION [dbo].[EstoqueMovimento]
(
	@dataInf Date = null,
	@idEmitenteInf int,
	@idProdutoInf int = 0,
	@idProdutoClassificacaoInf int = 0,
	@idContaInf int = 0,
	@dataInicial Date = null
)
RETURNS 
@Resultado TABLE 
(
		idProduto int,
		idProdutoClassificacao int,
		dscProduto varchar(240),
		codigoProdutoClasse varchar(50),
		preco decimal(18,4),
		precoCadastro decimal(18,4),
		unidadeMedida varchar(30),
		unidadeMedidaConvertida varchar(30),
		fatorConversao decimal(18,4),
		qtd decimal(18,4),
		idOperacao int,
		idnOperacao varchar(5),
		idConta int,
		dscConta varchar(200),		
		numeroDocumento int null,
		dscOperacao varchar(100),
		data datetime,
		idGrupo int,
		dscGrupo varchar(100),
		idNF int
)
AS
BEGIN

	declare @data datetime = @dataInf;
	declare @idEmitente int = @idEmitenteInf;
	declare @idProduto int = 0;
	declare @idProdutoClassificacao int = 0;
	declare @idConta int = 0;
	
	if (@idContaInf > 0)
		SET @idConta = @idContaInf;
	
	if (@idProdutoClassificacaoInf > 0)
		SET @idProdutoClassificacao = @idProdutoClassificacaoInf;
	
	if (@idProdutoInf > 0)
		SET @idProduto = @idProdutoInf;
	
	if (@data is null)
		SET @data = (SELECT GETDATE());
			
	
	declare @ItensEntradaSaida TABLE
	(
		idProduto int,
		idProdutoClassificacao int,
		dscProduto varchar(240),
		codigoProdutoClasse varchar(50),
		preco decimal(18,4),
		precoCadastro decimal(18,4),
		unidadeMedida varchar(30),
		unidadeMedidaConvertida varchar(30),
		fatorConversao decimal(18,4),
		qtd decimal(18,4),
		idOperacao int,
		idnOperacao varchar(5),
		idConta int,
		dscConta varchar(200),		
		numeroDocumento int null,
		dscOperacao varchar(100),
		data datetime,
		idNF int
	)
	
	--Notas de entrada / Saida
	INSERT INTO @ItensEntradaSaida
	select 
		np.idProduto, 
		np.idProdutoClassificacao, 
		(case when np.idProdutoClassificacao is not null then pr.descricao + ' - ' + coalesce(pc.descricao, '') else pr.descricao end),
		(case when np.idProdutoClassificacao is not null then cast(pr.id as varchar(47)) + REPLACE(STR(pc.sequencia, 3), SPACE(1), '0') else cast(pr.id as varchar(50)) end),
		(case when np.qcom > 0 then (coalesce(np.vuncom, 0) - coalesce(np.vdesc / np.qcom, 0)) else (coalesce(np.vuncom, 0) - coalesce(np.vdesc, 0)) end),
		(case when np.idProdutoClassificacao is not null then (case when pc.vuncom > 0 then pc.vuncom else pr.vuncom end) else pr.vuncom end),
		upper(coalesce(pr.ucom, '')),
		upper(coalesce(np.ucom, '')),
		--Se não tem a quantidade real a ser considerada para o estoque, e as unidades de medida são diferentes, seleciona o fator de conversão
		(case when pr.ucom <> np.ucom and coalesce(np.qcomEstoque, 0) = 0 then (select top 1 pu.fatorConversao from produtoUnidade pu where (pu.idProduto = np.idProduto) and (pu.unidadeMedida = np.ucom)) else 0 end) as fatorConversao,
		(case when 
			(case when ecp.idnOperacao = '=' then (case when op.idnES = 0 then '+' else '-' end) else ecp.idnOperacao end) = '-' then
			(case when coalesce(np.qcomEstoque, 0) != 0 then np.qcomEstoque else np.qcom end) * -1
			else
			(case when coalesce(np.qcomEstoque, 0) != 0 then np.qcomEstoque else np.qcom end) --Considera a quantidade a ser usada para o estoque caso tenha sido informada
			end),
		nf.idOperacao,
		(case when ecp.idnOperacao = '=' then (case when op.idnES = 0 then '+' else '-' end) else ecp.idnOperacao end),
		ecp.idConta, 
		ec.descricao,
		(case when nf.numero > 0 then nf.numero else nf.numeroMovimento end),
		op.descricao as dscOperacao,
		nf.dataEmissao,
		nf.id
		from nfproduto np
		inner join notafiscal nf on nf.id = np.idNotaFiscal
		inner join nfemitente nfe on nfe.idnotafiscal = np.idnotafiscal
		inner join produto pr on pr.id = np.idProduto
		inner join operacao op on op.id = nf.idOperacao
		inner join EstoqueContaOperacao ecp on ecp.idOperacao = nf.idOperacao
		inner join EstoqueConta ec on ec.id = ecp.idConta
		left join produtoClassificacao pc on pc.id = np.idProdutoClassificacao
		where ((nf.nfeCstat = 100) or (nf.nfeCstat = 150))
		and   (nf.nfeTpAmb = 1)
		and   (cast(nf.dataEmissao as date) <= @data)
		and   (((@dataInicial is not null) and (cast(nf.dataEmissao as date) >= @dataInicial)) or (@dataInicial is null))
		and   ((op.idnNaoCalcularEstoque is null) or (op.idnNaoCalcularEstoque = 0))
		and   (nfe.idEmitente = @idEmitente)		
		and   ((np.idProduto = @idProduto) or (@idProduto = 0))
		and   ((np.idProdutoClassificacao = @idProdutoClassificacao) or (@idProdutoClassificacao = 0))
		and   (pr.idnControlaEstoque = 1)
		and   ((np.idnItemCanceladoCF is null) or (np.idnItemCanceladoCF != 1))
		and   ((ecp.idConta = @idConta) or (@idConta = 0))
	
	--Ajustes de estoque
	INSERT INTO @ItensEntradaSaida
		select 
			ea.idProduto, 
			ea.idProdutoClassificacao,
			(case when ea.idProdutoClassificacao is not null then pr.descricao + ' - ' + coalesce(pc.descricao, '') else pr.descricao end),
			(case when ea.idProdutoClassificacao is not null then cast(pr.id as varchar(20)) + REPLACE(STR(pc.sequencia, 3), SPACE(1), '0') else cast(pr.id as varchar(20)) end),
			ea.unitario, 
			(case when ea.idProdutoClassificacao is not null then (case when pc.vuncom > 0 then pc.vuncom else pr.vuncom end) else pr.vuncom end),
			upper(coalesce(pr.ucom, '')),
			upper(coalesce(ea.unidadeMedida, '')),
			(case when pr.ucom <> ea.unidadeMedida then (select top 1 pu.fatorConversao from produtoUnidade pu where (pu.idProduto = ea.idProduto) and (pu.unidadeMedida = ea.unidadeMedida)) else 0 end) as fatorConversao,
			(case when
				(case when ecp.idnOperacao = '=' then (case when ea.idnES = 0 then '+' else '-' end) else ecp.idnOperacao end) = '-' then
				ea.qtd * -1
				else
				ea.qtd end),
			ea.idOperacao,
			(case when ecp.idnOperacao = '=' then (case when ea.idnES = 0 then '+' else '-' end) else ecp.idnOperacao end),
			ecp.idConta, 
			ec.descricao,
			null,
			op.descricao as dscOperacao,
			ea.data,
			0
		from estoqueAjustes ea
		inner join produto pr on pr.id = ea.idProduto
		inner join operacao op on op.id = ea.idOperacao
		inner join EstoqueContaOperacao ecp on ecp.idOperacao = ea.idOperacao
		inner join EstoqueConta ec on ec.id = ecp.idConta
		left join ProdutoClassificacao pc on pc.id = ea.idProdutoClassificacao
		
		where (cast(ea.data as date) <= @data)
		and   (((@dataInicial is not null) and (cast(ea.data as date) >= @dataInicial)) or (@dataInicial is null))
		and   (ea.idEmitente = @idEmitente)
		and   ((op.idnNaoCalcularEstoque is null) or (op.idnNaoCalcularEstoque = 0))
		and   ((ea.idProduto = @idProduto) or (@idProduto = 0))
		and   ((ea.idProdutoClassificacao = @idProdutoClassificacao) or (@idProdutoClassificacao = 0))
		and   (pr.idnControlaEstoque = 1)
		and   ((ecp.idConta = @idConta) or (@idConta = 0))
		
	--Produtos alocados nas ordens de servi?o
	INSERT INTO @ItensEntradaSaida
		select 
			osp.idProduto, 
			osp.idProdutoClassificacao,
			(case when osp.idProdutoClassificacao is not null then pr.descricao + ' - ' + coalesce(pc.descricao, '') else pr.descricao end),
			(case when osp.idProdutoClassificacao is not null then cast(pr.id as varchar(20)) + REPLACE(STR(pc.sequencia, 3), SPACE(1), '0') else cast(pr.id as varchar(20)) end),
			osp.precoUnitario, 
			(case when osp.idProdutoClassificacao is not null then (case when pc.vuncom > 0 then pc.vuncom else pr.vuncom end) else pr.vuncom end),
			upper(coalesce(pr.ucom, '')),
			upper(coalesce(osp.ucom, '')),
			(case when pr.ucom <> osp.ucom then (select top 1 pu.fatorConversao from produtoUnidade pu where (pu.idProduto = osp.idProduto) and (pu.unidadeMedida = osp.ucom)) else 0 end) as fatorConversao,
			(case when 
				(case when ecp.idnOperacao = '=' then (case when op.idnES = 0 then '+' else '-' end) else ecp.idnOperacao end) = '-' then
				osp.quantidade * -1 else osp.quantidade end),
			os.idOperacao,
			(case when ecp.idnOperacao = '=' then (case when op.idnES = 0 then '+' else '-' end) else ecp.idnOperacao end),
			ec.id,
			ec.descricao,
			os.numeroOS,
			op.descricao,
			(case when osp.dataHora is not null then osp.dataHora else os.dataHora end),
			0
		from OrdemServicoProduto osp
		inner join produto pr on pr.id = osp.idProduto
		inner join OrdemServico os on os.id = osp.idOrdemServico
		inner join operacao op on op.id = os.idOperacao
		inner join EstoqueContaOperacao ecp on ecp.idOperacao = os.idOperacao
		inner join EstoqueConta ec on ec.id = ecp.idConta
		left join ProdutoClassificacao pc on pc.id = osp.idProdutoClassificacao	
		where ((case when osp.dataHora is not null then cast(osp.dataHora as date) else cast(os.dataHora as date) end) <= @data)
		and   (((@dataInicial is not null) and (cast(os.dataHora as date) >= @dataInicial)) or (@dataInicial is null))
		and   (os.idEmitente = @idEmitente)
		and   ((osp.idProduto = @idProduto) or (@idProduto = 0))
		and   ((osp.idProdutoClassificacao = @idProdutoClassificacao) or (@idProdutoClassificacao = 0))
		and   ((op.idnNaoCalcularEstoque is null) or (op.idnNaoCalcularEstoque = 0))
		and   (pr.idnControlaEstoque = 1)
		and   ((ecp.idConta = @idConta) or (@idConta = 0))
		and ((os.idnCancelada = 0) or (os.idnCancelada is null))
	
	--Pedidos
	INSERT INTO @ItensEntradaSaida
		select
		pp.idProduto,
		pp.idProdutoClassificacao,
		(case when pp.idProdutoClassificacao is not null then pr.descricao + ' - ' + coalesce(pc.descricao, '') else pr.descricao end),
		(case when pp.idProdutoClassificacao is not null then cast(pr.id as varchar(20)) + REPLACE(STR(pc.sequencia, 3), SPACE(1), '0') else cast(pr.id as varchar(20)) end),
		pp.vuncom, 
		(case when pp.idProdutoClassificacao is not null then (case when pc.vuncom > 0 then pc.vuncom else pr.vuncom end) else pr.vuncom end),
		upper(coalesce(pr.ucom, '')),
		upper(coalesce(pp.ucom, '')),
		(case when pr.ucom <> pp.ucom then (select top 1 pu.fatorConversao from produtoUnidade pu where (pu.idProduto = pp.idProduto) and (pu.unidadeMedida = pp.ucom)) else 0 end) as fatorConversao,
		(case when (case when ecp.idnOperacao = '=' then (case when op.idnES = 0 then '+' else '-' end) else ecp.idnOperacao end) = '-' then
			(pp.qcom - coalesce(pp.qtdFaturada, 0)) * -1
			else (pp.qcom - coalesce(pp.qtdFaturada, 0)) end),
		pe.idOperacao,
		(case when ecp.idnOperacao = '=' then (case when op.idnES = 0 then '+' else '-' end) else ecp.idnOperacao end),
		ec.id,
		ec.descricao,
		(case when pe.numeroPedido is not null then cast(pe.numeroPedido as int) else null end),
		op.descricao as dscOperacao,
		pe.dataPedido,
		0
		from pedidoProduto pp
		inner join produto pr on pr.id = pp.idProduto
		inner join pedido pe on pe.id = pp.idPedido
		inner join operacao op on op.id = pe.idOperacao
		inner join EstoqueContaOperacao ecp on ecp.idOperacao = pe.idOperacao
		inner join EstoqueConta ec on ec.id = ecp.idConta
		left join ProdutoClassificacao pc on pc.id = pp.idProdutoClassificacao
		where (cast(pe.dataPedido as date) <= @data)
		and   (((@dataInicial is not null) and (cast(pe.dataPedido as date) >= @dataInicial)) or (@dataInicial is null))
		and   (((pe.idnFaturado is null) or (pe.idnFaturado = 0)) or ((coalesce(pe.idnFaturado, 0) <> 0) and (pe.id not in (select idPedido from pedidoFaturado))))
		and   ((pe.idnCancelado is null) or (pe.idnCancelado = 0))
		and   (pe.idEmitente = @idEmitente)
		and   ((pp.idProduto = @idProduto) or (@idProduto = 0))
		and   ((pp.idProdutoClassificacao = @idProdutoClassificacao) or (@idProdutoClassificacao = 0))
		and   ((op.idnNaoCalcularEstoque is null) or (op.idnNaoCalcularEstoque = 0))
		and   (pr.idnControlaEstoque = 1)		
		and   ((ecp.idConta = @idConta) or (@idConta = 0))	
		
	--Condicional
	INSERT INTO @ItensEntradaSaida
		select 
			cp.idProduto, 
			cp.idProdutoClassificacao,
			(case when cp.idProdutoClassificacao is not null then pr.descricao + ' - ' + coalesce(pc.descricao, '') else pr.descricao end),
			(case when cp.idProdutoClassificacao is not null then cast(pr.id as varchar(20)) + REPLACE(STR(pc.sequencia, 3), SPACE(1), '0') else cast(pr.id as varchar(20)) end),
			cp.vuncom, 
			(case when cp.idProdutoClassificacao is not null then (case when pc.vuncom > 0 then pc.vuncom else pr.vuncom end) else pr.vuncom end),
			upper(coalesce(pr.ucom, '')),
			upper(coalesce(cp.ucom, '')),
			0 as fatorConversao,
			(case when (case when ecp.idnOperacao = '=' then (case when op.idnES = 0 then '+' else '-' end) else ecp.idnOperacao end) = '-' then
		      (cp.qcom - (coalesce(cp.qtdDevolucao, 0)  + coalesce(cp.qtdFaturada, 0))) * -1
		      else (cp.qcom - (coalesce(cp.qtdDevolucao, 0)  + coalesce(cp.qtdFaturada, 0))) end),
			co.idOperacao,
			(case when ecp.idnOperacao = '=' then (case when op.idnES = 0 then '+' else '-' end) else ecp.idnOperacao end),
			ec.id,
			ec.descricao,
			(case when co.numero is not null then cast(co.numero as int) else null end),
			op.descricao as dscOperacao,
			co.dataEmissao,
			0
		from CondicionalProduto cp
		inner join produto pr on pr.id = cp.idProduto
		inner join Condicional co on co.id = cp.idCondicional		
		inner join operacao op on op.id = co.idOperacao
		inner join EstoqueContaOperacao ecp on ecp.idOperacao = co.idOperacao
		inner join EstoqueConta ec on ec.id = ecp.idConta
		left join ProdutoClassificacao pc on pc.id = cp.idProdutoClassificacao	
		where (cast(co.dataEmissao as date) <= @data)
		and   (((@dataInicial is not null) and (cast(co.dataEmissao as date) >= @dataInicial)) or (@dataInicial is null))
		and   ((co.idnFechado is null) or (co.idnFechado = 0))
		and   (co.idEmitente = @idEmitente)
		and   ((cp.idProduto = @idProduto) or (@idProduto = 0))
		and   ((cp.idProdutoClassificacao = @idProdutoClassificacao) or (@idProdutoClassificacao = 0))
		and   ((op.idnNaoCalcularEstoque is null) or (op.idnNaoCalcularEstoque = 0))
		and   (pr.idnControlaEstoque = 1)		
		and   ((ecp.idConta = @idConta) or (@idConta = 0))

	--Consignacao
	INSERT INTO @ItensEntradaSaida
		select 
			cp.idProduto, 
			cp.idProdutoClassificacao,
			(case when cp.idProdutoClassificacao is not null then pr.descricao + ' - ' + coalesce(pc.descricao, '') else pr.descricao end),
			(case when cp.idProdutoClassificacao is not null then cast(pr.id as varchar(20)) + REPLACE(STR(pc.sequencia, 3), SPACE(1), '0') else cast(pr.id as varchar(20)) end),
			cp.vuncom, 
			(case when cp.idProdutoClassificacao is not null then (case when pc.vuncom > 0 then pc.vuncom else pr.vuncom end) else pr.vuncom end),
			upper(coalesce(pr.ucom, '')),
			upper(coalesce(cp.ucom, '')),
			0 as fatorConversao,
			(case when (case when ecp.idnOperacao = '=' then (case when op.idnES = 0 then '+' else '-' end) else ecp.idnOperacao end) = '-' then
		      cp.qcom * -1
		      else cp.qcom end),
			co.idOperacao,
			(case when ecp.idnOperacao = '=' then (case when op.idnES = 0 then '+' else '-' end) else ecp.idnOperacao end),
			ec.id,
			ec.descricao,
			(case when co.numeroConsignacao is not null then cast(co.numeroConsignacao as int) else null end),
			op.descricao as dscOperacao,
			co.data,
			0
		from ConsignacaoProduto cp
		inner join produto pr on pr.id = cp.idProduto
		inner join Consignacao co on co.id = cp.idConsignacao		
		inner join operacao op on op.id = co.idOperacao
		inner join EstoqueContaOperacao ecp on ecp.idOperacao = co.idOperacao
		inner join EstoqueConta ec on ec.id = ecp.idConta
		left join ProdutoClassificacao pc on pc.id = cp.idProdutoClassificacao	
		where (cast(co.data as date) <= @data)
		and   (((@dataInicial is not null) and (cast(co.data as date) >= @dataInicial)) or (@dataInicial is null))
		--and   ((co.idnFechado is null) or (co.idnFechado = 0))
		and   (co.idEmitente = @idEmitente)
		and   ((cp.idProduto = @idProduto) or (@idProduto = 0))
		and   ((cp.idProdutoClassificacao = @idProdutoClassificacao) or (@idProdutoClassificacao = 0))
		and   ((op.idnNaoCalcularEstoque is null) or (op.idnNaoCalcularEstoque = 0))
		and   (pr.idnControlaEstoque = 1)		
		and   ((ecp.idConta = @idConta) or (@idConta = 0))		

	--atualiza a qtd e o preço dos produtos com unidade de medida diferente da definida no cadastro e efetua a conversão conforme multiplicador	
	UPDATE @ItensEntradaSaida set qtd = qtd * fatorConversao, preco = preco / fatorConversao
	WHERE (unidadeMedidaConvertida <> '')
	and (unidadeMedida <> unidadeMedidaConvertida)
	and (fatorConversao > 0)
	
	INSERT INTO @Resultado
		SELECT ies.idProduto,
			   ies.idProdutoClassificacao,
			   ies.dscProduto,
			   ies.codigoProdutoClasse,
			   ies.preco,
			   ies.precoCadastro,
			   ies.unidadeMedida,
			   ies.unidadeMedidaConvertida,
			   ies.fatorConversao,
			   ies.qtd,
			   ies.idOperacao,
			   ies.idnOperacao,
			   ies.idConta,
			   ies.dscConta,
			   ies.numeroDocumento,
			   ies.dscOperacao,
			   ies.data,
			   pr.idGrupo,
			   pg.descricao,
			   ies.idNF
	from @ItensEntradaSaida ies
	inner join produto pr on pr.id = ies.idProduto
	left join produtoGrupo pg on pg.id = pr.idGrupo
	order by ies.data
	
	RETURN 
END
GO
delete from LayoutTelas
go
DELETE FROM Dashboard
GO
SET IDENTITY_INSERT [dbo].[Dashboard] ON 
GO
INSERT [dbo].[Dashboard] ([id], [descricao], [idDashboardGrupo], [xml]) VALUES (1, N'Visão geral de Vendas', 1, N'<Dashboard CurrencyCulture="pt-BR"><Title Text="Visão geral de Vendas" /><DataSources><SqlDataSource Name="SQL Data Source 1" ComponentName="dashboardSqlDataSource1"><Connection Name="localhost_TryNFeOnline_Connection" ProviderKey="MSSqlServer"><Parameters><Parameter Name="server" Value="DANIELJORIS\SQLEXPRESS2017" /><Parameter Name="database" Value="TryNFeOnlineDashboard" /><Parameter Name="useIntegratedSecurity" Value="True" /><Parameter Name="read only" Value="1" /><Parameter Name="generateConnectionHelper" Value="false" /><Parameter Name="userid" Value="" /><Parameter Name="password" Value="" /></Parameters></Connection><Query Type="CustomSqlQuery" Name="Vendas por Data"><Parameter Name="idEmitente" Type="DevExpress.DataAccess.Expression">(System.Int32)(?idEmitente)</Parameter><Parameter Name="idEmitenteLogado" Type="DevExpress.DataAccess.Expression">(System.Int32)(?idEmitenteLogado)</Parameter><Sql>SELECT 
cast(nf.dataEmissao as date) as diaVenda,
sum(nfp.vprod - coalesce(nfp.vdesc, 0)) as TotalVendas,
sum(coalesce(nfp.precoCustoTotal, 0)) as TotalCusto, 
count(distinct nf.id) as qtdVendas
from nfproduto nfp
inner join notafiscal nf on nf.id = nfp.idNotaFiscal
inner join operacao op on op.id = nf.idOperacao
inner join nfemitente ne on ne.idNotaFiscal = nf.id
where (((ne.idEmitente = @idEmitente) and (@idEmitente &gt; 0)) or ((ne.idEmitente = @idEmitenteLogado) and (@idEmitente = 0)))
and (nf.tipodocumento = 1)
and ((nf.nfeTpAmb &lt;&gt; 2) or (nf.nfeTpAmb is null))
and (op.idnCompraVenda = 1)
and (nf.nfeCstat = 100 or nf.nfeCstat = 150)
and (coalesce(nf.idnInutilizado, 0) = 0)
group by cast(nf.dataEmissao as date), nf.modelo</Sql></Query><Query Type="CustomSqlQuery" Name="Emitentes"><Sql>select 0 as id, ''Nenhum emitente selecionado'' as fantasia,''Nenhum emitente selecionado'' as razaosocial
UNION ALL
select id, fantasia, razaosocial from emitente where (coalesce(idnBaixa, 0) = 0)
</Sql></Query><Query Type="CustomSqlQuery" Name="Ano"><Sql>select distinct year(dataemissao) as ano from notafiscal</Sql></Query><Query Type="CustomSqlQuery" Name="Venda por Produto"><Parameter Name="idEmitente" Type="DevExpress.DataAccess.Expression">(System.Int32)(?idEmitente)</Parameter><Parameter Name="idEmitenteLogado" Type="DevExpress.DataAccess.Expression">(System.Int32)(?idEmitenteLogado)</Parameter><Sql>


/* Inner join */
select 
(cast(nf.dataEmissao as date)) as diaVenda,
sum(nfp.vprod - coalesce(nfp.vdesc, 0)) as TotalVendas,
sum(coalesce(nfp.precoCustoTotal, 0)) as TotalCusto,
sum(nfp.qcom) as qtdProduto,
cast(p.id as varchar(10)) + 
(case when nfp.sequenciaClassificacao &gt; 0 then ''/'' + 
cast(nfp.sequenciaClassificacao as varchar(20)) else '''' end) + '' - '' + 
nfp.descricao as dscProduto,
(case when p.idMarca is not null then marca.descricao else ''Não definido'' end) as marcaProduto
from nfproduto nfp
inner join notafiscal nf on nf.id = nfp.idNotaFiscal
inner join operacao op on op.id = nf.idOperacao
inner join nfemitente ne on ne.idNotaFiscal = nf.id
inner join produto p on p.id = nfp.idProduto
left join marca on marca.id = p.idMarca
where (((ne.idEmitente = @idEmitente) and (@idEmitente &gt; 0)) or ((ne.idEmitente = @idEmitenteLogado) and (@idEmitente = 0)))
and (nf.tipodocumento = 1)
and ((nf.nfeTpAmb &lt;&gt; 2) or (nf.nfeTpAmb is null))
and (op.idnCompraVenda = 1)
and (nf.nfeCstat = 100 or nf.nfeCstat = 150)
and (coalesce(nf.idnInutilizado, 0) = 0)
group by cast(nf.dataEmissao as date), 
cast(p.id as varchar(10)) + 
(case when nfp.sequenciaClassificacao &gt; 0 then ''/'' + 
cast(nfp.sequenciaClassificacao as varchar(20)) else '''' end)
+ '' - '' + nfp.descricao,
p.idMarca,
marca.descricao
</Sql></Query><Query Type="CustomSqlQuery" Name="Vendas por Vendedor"><Parameter Name="idEmitente" Type="DevExpress.DataAccess.Expression">(System.Int32)(?idEmitente)</Parameter><Parameter Name="idEmitenteLogado" Type="DevExpress.DataAccess.Expression">(System.Int32)(?idEmitenteLogado)</Parameter><Sql>select 
(cast(nf.dataEmissao as date)) as diaVenda,
sum(nfp.vprod - coalesce(nfp.vdesc, 0)) as TotalVendas,
sum(coalesce(nfp.precoCustoTotal, 0)) as TotalCusto,
(case when pa.fantasia is not null then pa.fantasia else ''Não definido'' end) as nomeVendedor,
count(distinct nf.id) as qtdVendas,
nf.dataEmissao
from nfproduto nfp
inner join notafiscal nf on nf.id = nfp.idNotaFiscal
inner join operacao op on op.id = nf.idOperacao
inner join nfemitente ne on ne.idNotaFiscal = nf.id
left join parceiro pa on pa.id = nf.idVendedor
where (((ne.idEmitente = @idEmitente) and (@idEmitente &gt; 0)) or ((ne.idEmitente = @idEmitenteLogado) and (@idEmitente = 0)))
and (nf.tipodocumento = 1)
and ((nf.nfeTpAmb &lt;&gt; 2) or (nf.nfeTpAmb is null))
and (op.idnCompraVenda = 1)
and (nf.nfeCstat = 100 or nf.nfeCstat = 150)
and (coalesce(nf.idnInutilizado, 0) = 0)
group by cast(cast(year(nf.dataemissao) as varchar(10)) + ''-'' + cast(month(nf.dataEmissao) as varchar(10)) + ''-01''  as date), 
pa.fantasia,
nf.dataEmissao
</Sql></Query><Query Type="CustomSqlQuery" Name="Vendas por Parceiro"><Parameter Name="idEmitente" Type="DevExpress.DataAccess.Expression">(System.Int32)(?idEmitente)</Parameter><Parameter Name="idEmitenteLogado" Type="DevExpress.DataAccess.Expression">(System.Int32)(?idEmitenteLogado)</Parameter><Sql>select 
(cast(nf.dataEmissao as date)) as diaVenda,
sum(nfp.vprod - coalesce(nfp.vdesc, 0)) as totalVendas,
sum(coalesce(nfp.precoCustoTotal, 0)) as totalCusto,
(case when nd.fantasia is not null then nd.fantasia else ''Não definido'' end) as nomeFantasiaParceiro,
count(distinct nf.id) as qtdVendas
from nfproduto nfp
inner join notafiscal nf on nf.id = nfp.idNotaFiscal
inner join operacao op on op.id = nf.idOperacao
inner join nfemitente ne on ne.idNotaFiscal = nf.id
left join nfDestinatario nd on nd.idNotaFiscal = nf.id
where (((ne.idEmitente = @idEmitente) and (@idEmitente &gt; 0)) or ((ne.idEmitente = @idEmitenteLogado) and (@idEmitente = 0)))
and (nf.tipodocumento = 1)
and ((nf.nfeTpAmb &lt;&gt; 2) or (nf.nfeTpAmb is null))
and (op.idnCompraVenda = 1)
and (nf.nfeCstat = 100 or nf.nfeCstat = 150)
and (coalesce(nf.idnInutilizado, 0) = 0)
group by nf.dataemissao, 
nd.fantasia
</Sql></Query><ResultSchema><DataSet Name="SQL Data Source 1"><View Name="Vendas por Data"><Field Name="diaVenda" Type="DateTime" /><Field Name="TotalVendas" Type="Decimal" /><Field Name="TotalCusto" Type="Decimal" /><Field Name="qtdVendas" Type="Int32" /></View><View Name="Emitentes"><Field Name="id" Type="Int32" /><Field Name="fantasia" Type="String" /><Field Name="razaosocial" Type="String" /></View><View Name="Ano"><Field Name="ano" Type="Int32" /></View><View Name="Venda por Produto"><Field Name="diaVenda" Type="DateTime" /><Field Name="TotalVendas" Type="Decimal" /><Field Name="TotalCusto" Type="Decimal" /><Field Name="qtdProduto" Type="Decimal" /><Field Name="dscProduto" Type="String" /><Field Name="marcaProduto" Type="String" /></View><View Name="Vendas por Vendedor"><Field Name="diaVenda" Type="DateTime" /><Field Name="TotalVendas" Type="Decimal" /><Field Name="TotalCusto" Type="Decimal" /><Field Name="nomeVendedor" Type="String" /><Field Name="qtdVendas" Type="Int32" /><Field Name="dataEmissao" Type="DateTime" /></View><View Name="Vendas por Parceiro"><Field Name="diaVenda" Type="DateTime" /><Field Name="totalVendas" Type="Decimal" /><Field Name="totalCusto" Type="Decimal" /><Field Name="nomeFantasiaParceiro" Type="String" /><Field Name="qtdVendas" Type="Int32" /></View></DataSet></ResultSchema><ConnectionOptions CloseConnection="true" /></SqlDataSource></DataSources><Parameters><Parameter Name="idEmitente" Type="System.Int32, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Value="0" Description="Filial/empresa"><DynamicListLookUpSettings DataSourceName="dashboardSqlDataSource1" DataMember="Emitentes" ValueMember="id" DisplayMember="fantasia" /></Parameter><Parameter Name="idEmitenteLogado" Type="System.Int32, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Value="1" Visible="false" /></Parameters><Items><Grid ComponentName="gridDashboardItem1" Name="Top 10 - Produtos Vendidos" DataSource="dashboardSqlDataSource1" DataMember="Venda por Produto"><DataItems><Measure DataMember="TotalVendas" Name="Total de Vendas" DefaultId="DataItem2"><NumericFormat FormatType="Currency" Unit="Ones" IncludeGroupSeparator="true" CurrencyCultureName="pt-BR" /></Measure><Dimension DataMember="diaVenda" DateTimeGroupInterval="MonthYear" DefaultId="DataItem3" /><Dimension DataMember="dscProduto" Name="Código - Nome do produto" SortByMeasure="DataItem2" TopNEnabled="true" TopNCount="10" TopNMeasure="DataItem2" DefaultId="DataItem1" /><Measure DataMember="qtdProduto" Name="Quantidade" DefaultId="DataItem0"><NumericFormat FormatType="Number" /></Measure></DataItems><HiddenDimensions><Dimension DefaultId="DataItem3" /></HiddenDimensions><GridColumns><GridDimensionColumn Weight="105.83483987603306"><Dimension DefaultId="DataItem1" /><Totals><Total /></Totals></GridDimensionColumn><GridMeasureColumn Weight="30.303137913223139"><Measure DefaultId="DataItem0" /><Totals><Total Type="Sum" /></Totals></GridMeasureColumn><GridMeasureColumn Weight="82.7682722107438"><Measure DefaultId="DataItem2" /><Totals><Total Type="Sum" /></Totals></GridMeasureColumn></GridColumns><GridOptions ColumnWidthMode="Manual" /></Grid><Pie ComponentName="pieDashboardItem1" Name="Vendas por Marca - Valor total de Vendas / Quantidade" DataSource="dashboardSqlDataSource1" DataMember="Venda por Produto" IsMasterFilterCrossDataSource="true" ShowPieCaptions="false"><InteractivityOptions MasterFilterMode="Multiple" /><DataItems><Measure DataMember="TotalVendas" Name="Valor total de vendas" DefaultId="DataItem1"><NumericFormat FormatType="Currency" Unit="Ones" IncludeGroupSeparator="true" CurrencyCultureName="pt-BR" /></Measure><Measure DataMember="qtdProduto" Name="Quantidade" DefaultId="DataItem2"><NumericFormat FormatType="Number" /></Measure><Dimension DataMember="marcaProduto" Name="Marca do Produto" SortOrder="Descending" SortByMeasure="DataItem1" TopNEnabled="true" TopNCount="10" TopNMeasure="DataItem1" DefaultId="DataItem0" /></DataItems><Arguments><Argument DefaultId="DataItem0" /></Arguments><Values><Value DefaultId="DataItem1" /><Value DefaultId="DataItem2" /></Values></Pie><RangeFilter ComponentName="rangeFilterDashboardItem2" Name="" ShowCaption="true" ParentContainer="dashboardItemGroup1" DataSource="dashboardSqlDataSource1" DataMember="Vendas por Data" IsMasterFilterCrossDataSource="true" SelectedDateTimePeriodIndex="Este Mês"><ColoringOptions MeasuresColoringMode="Hue" UseGlobalColors="false" /><DataItems><Dimension DataMember="diaVenda" DateTimeGroupInterval="MonthYear" DefaultId="DataItem0" /><Measure DataMember="TotalVendas" DefaultId="DataItem1" /></DataItems><Argument DefaultId="DataItem0" /><Series><Simple SeriesType="StackedArea"><Value DefaultId="DataItem1" /></Simple></Series><DateTimePeriods><DateTimePeriod Name="Este Mês"><StartLimit><FlowDateTimePeriodLimit Interval="Month" /></StartLimit><EndLimit><FlowDateTimePeriodLimit Interval="Month" Offset="1" /></EndLimit></DateTimePeriod><DateTimePeriod Name="Último Mês"><StartLimit><FlowDateTimePeriodLimit Interval="Month" Offset="-1" /></StartLimit><EndLimit><FlowDateTimePeriodLimit Interval="Month" /></EndLimit></DateTimePeriod><DateTimePeriod Name="Últimos 3 Meses"><StartLimit><FlowDateTimePeriodLimit Interval="Month" Offset="-2" /></StartLimit><EndLimit><FlowDateTimePeriodLimit Interval="Month" Offset="1" /></EndLimit></DateTimePeriod><DateTimePeriod Name="Este Ano"><StartLimit><FlowDateTimePeriodLimit /></StartLimit><EndLimit><FlowDateTimePeriodLimit Offset="1" /></EndLimit></DateTimePeriod><DateTimePeriod Name="Último Ano"><StartLimit><FlowDateTimePeriodLimit Offset="-1" /></StartLimit><EndLimit><FlowDateTimePeriodLimit /></EndLimit></DateTimePeriod><DateTimePeriod Name="Últimos 2 Anos"><StartLimit><FlowDateTimePeriodLimit Offset="-2" /></StartLimit><EndLimit><FlowDateTimePeriodLimit /></EndLimit></DateTimePeriod></DateTimePeriods><ColorScheme><Entry DataSource="dashboardSqlDataSource1" DataMember="Vendas por Data" Color="-14176672"><MeasureKey><Definition DataMember="TotalVendas" /></MeasureKey></Entry></ColorScheme></RangeFilter><Chart ComponentName="chartDashboardItem1" Name="Vendas por Vendedor" DataSource="dashboardSqlDataSource1" DataMember="Vendas por Vendedor"><InteractivityOptions MasterFilterMode="Single" /><DataItems><Dimension DataMember="nomeVendedor" TopNEnabled="true" TopNCount="10" TopNMeasure="DataItem1" DefaultId="DataItem0" /><Measure DataMember="TotalVendas" Name="Valor total de Vendas" DefaultId="DataItem1" /><Measure DataMember="qtdVendas" Name="Quantidade de Vendas" DefaultId="DataItem2" /></DataItems><Arguments><Argument DefaultId="DataItem0" /></Arguments><Panes><Pane Name="Pane 1"><Series><Simple Name="Valor total de Vendas"><Value DefaultId="DataItem1" /></Simple><Simple Name="Quantidade de vendas" PlotOnSecondaryAxis="true"><Value DefaultId="DataItem2" /></Simple></Series></Pane><Pane Name="Pane 2" /></Panes><ChartLegend InsidePosition="TopCenterHorizontal" /></Chart><Grid ComponentName="gridDashboardItem2" Name="Top 10 - Vendas por Parceiro" DataSource="dashboardSqlDataSource1" DataMember="Vendas por Parceiro" IsMasterFilterCrossDataSource="true"><DataItems><Dimension DataMember="nomeFantasiaParceiro" Name="Nome do Parceiro" SortOrder="Descending" SortByMeasure="DataItem1" TopNEnabled="true" TopNCount="10" TopNMeasure="DataItem1" DefaultId="DataItem0" /><Measure DataMember="totalVendas" Name="Valor total de Vendas" DefaultId="DataItem1"><NumericFormat FormatType="Currency" Unit="Ones" IncludeGroupSeparator="true" CurrencyCultureName="pt-BR" /></Measure><Measure DataMember="qtdVendas" Name="Quantidade de Vendas" DefaultId="DataItem2" /></DataItems><GridColumns><GridDimensionColumn Name="Nome do Parceiro" Weight="113.47150259067358"><Dimension DefaultId="DataItem0" /><Totals><Total /></Totals></GridDimensionColumn><GridMeasureColumn Weight="45.854922279792746"><Measure DefaultId="DataItem2" /><Totals><Total Type="Sum" /></Totals></GridMeasureColumn><GridMeasureColumn Weight="65.673575129533674"><Measure DefaultId="DataItem1" /><Totals><Total Type="Sum" /></Totals></GridMeasureColumn></GridColumns><GridOptions ColumnWidthMode="Manual" /></Grid></Items><Groups><Group ComponentName="dashboardItemGroup1" Name="Gráfico de vendas por Período"><InteractivityOptions IsMasterFilter="true" /></Group></Groups><ColorScheme><Entry DataSource="dashboardSqlDataSource1" DataMember="Vendas por Vendedor" Color="-14176672"><MeasureKey><Definition DataMember="TotalVendas" /></MeasureKey></Entry><Entry DataSource="dashboardSqlDataSource1" DataMember="Vendas por Vendedor" Color="-14057287"><MeasureKey><Definition DataMember="qtdVendas" /></MeasureKey></Entry><Entry DataSource="dashboardSqlDataSource1" DataMember="Venda por Produto" Color="-1618884"><DimensionKeys><DimensionKey><Definition DataMember="marcaProduto" /><Value Type="System.String" Value="Não definido" /></DimensionKey></DimensionKeys></Entry><Entry DataSource="dashboardSqlDataSource1" DataMember="Vendas por Data" Color="-14057287"><MeasureKey><Definition DataMember="qtdVendas" /></MeasureKey></Entry><Entry DataSource="dashboardSqlDataSource1" DataMember="Vendas por Data" Color="-14176672"><MeasureKey><Definition DataMember="TotalVendas" /></MeasureKey></Entry></ColorScheme><LayoutTree><LayoutGroup Orientation="Vertical" Weight="100"><LayoutGroup Orientation="Vertical" DashboardItem="dashboardItemGroup1" Weight="23.687423687423689"><LayoutItem DashboardItem="rangeFilterDashboardItem2" Weight="100" /></LayoutGroup><LayoutGroup Weight="38.095238095238095"><LayoutItem DashboardItem="chartDashboardItem1" Weight="67.771883289124673" /><LayoutItem DashboardItem="gridDashboardItem1" Weight="32.228116710875334" /></LayoutGroup><LayoutGroup Weight="38.217338217338217"><LayoutItem DashboardItem="gridDashboardItem2" Weight="38.527851458885941" /><LayoutItem DashboardItem="pieDashboardItem1" Weight="61.472148541114059" /></LayoutGroup></LayoutGroup></LayoutTree></Dashboard>')
GO
INSERT [dbo].[Dashboard] ([id], [descricao], [idDashboardGrupo], [xml]) VALUES (2, N'Recebimentos X Pagamentos', 5, N'<Dashboard CurrencyCulture="pt-BR"><Title Text="Recebimentos X Pagamentos" /><DataSources><SqlDataSource Name="SQL Data Source 1" ComponentName="dashboardSqlDataSource1"><Connection Name="localhost_TryNFeOnline_Connection" ProviderKey="MSSqlServer"><Parameters><Parameter Name="server" Value="DANIELJORIS\SQLEXPRESS2017" /><Parameter Name="database" Value="TryNFeOnlineDashboard" /><Parameter Name="useIntegratedSecurity" Value="True" /><Parameter Name="read only" Value="1" /><Parameter Name="generateConnectionHelper" Value="false" /><Parameter Name="userid" Value="" /><Parameter Name="password" Value="" /></Parameters></Connection><Query Type="CustomSqlQuery" Name="Anos"><Sql>select distinct YEAR(data) as ano 
from Caixa</Sql></Query><Query Type="CustomSqlQuery" Name="RecebimentosPagamentos"><Parameter Name="idEmitente" Type="DevExpress.DataAccess.Expression">(System.Int32)(?idEmitente)</Parameter><Parameter Name="idEmitenteLogado" Type="DevExpress.DataAccess.Expression">(System.Int32)(?idEmitenteLogado)</Parameter><Sql>select 
fpp.valor, 
recDes.descricao as dscReceitaDespesa, 
(case when f.idnReceitaDespesa = ''+'' then ''Receitas'' else ''Despesas'' end) as indReceitaDespesa,
(cast(cm.dataHora as date)) as dataMovimentoCaixa
from FaturaParcelaPagamento fpp
inner join FaturaParcela fp on fp.id = fpp.idFaturaParcela
inner join Fatura f on f.id = fp.idFatura
--inner join Emitente em on em.id = f.idEmitente
inner join CaixaMovimento cm on cm.idFaturaParcelaPagamentoOrigem = fpp.id
--inner join Caixa ca on ca.id = cm.idCaixa
inner join ReceitaDespesa recDes on recDes.id = f.idReceitaDespesa
where (((f.idEmitente = @idEmitente) and (@idEmitente &gt; 0)) or ((f.idEmitente = @idEmitenteLogado) and (@idEmitente = 0)))
--Isso tras somente as despesas de taxas, ignorando os recebimentos para não duplicar os valores
and (((f.idnLancamentoCartao = 0) or (f.idnLancamentoCartao is null)) or (f.idnReceitaDespesa = ''-''))
--Isso ignora os valores envolvidos no abatimento/troco, trazendo o valor real movimentado
and ((recDes.idnIgnorarNosRelatorios = 0) or (recDes.idnIgnorarNosRelatorios is null))
</Sql></Query><Query Type="CustomSqlQuery" Name="Emitentes"><Sql>select 0 as id, ''Nenhum emitente selecionado'' as fantasia,''Nenhum emitente selecionado'' as razaosocial
UNION ALL
select id, fantasia, razaosocial from emitente where (coalesce(idnBaixa, 0) = 0)
</Sql></Query><ResultSchema><DataSet Name="SQL Data Source 1"><View Name="Anos"><Field Name="ano" Type="Int32" /></View><View Name="RecebimentosPagamentos"><Field Name="valor" Type="Decimal" /><Field Name="dscReceitaDespesa" Type="String" /><Field Name="indReceitaDespesa" Type="String" /><Field Name="dataMovimentoCaixa" Type="DateTime" /></View><View Name="Emitentes"><Field Name="id" Type="Int32" /><Field Name="fantasia" Type="String" /><Field Name="razaosocial" Type="String" /></View></DataSet></ResultSchema><ConnectionOptions CloseConnection="true" /><CalculatedFields><CalculatedField Name="Receitas" Expression="Iif([indReceitaDespesa] =  ''Receitas'',[valor], 0)" DataType="Auto" DataMember="RecebimentosPagamentos" /><CalculatedField Name="Despesas" Expression="Iif([indReceitaDespesa] =  ''Despesas'',[valor], 0)&#xA;" DataType="Auto" DataMember="RecebimentosPagamentos" /></CalculatedFields></SqlDataSource></DataSources><Parameters><Parameter Name="idEmitente" Type="System.Int32, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Value="0" Description="Filial/empresa"><DynamicListLookUpSettings DataSourceName="dashboardSqlDataSource1" DataMember="Emitentes" ValueMember="id" DisplayMember="fantasia" /></Parameter><Parameter Name="idEmitenteLogado" Type="System.Int32, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Value="0" Visible="false" /></Parameters><Items><Pie ComponentName="pieDashboardItem1" Name="Comparativo de Receitas/Despesas" ParentContainer="dashboardItemGroup1" DataSource="dashboardSqlDataSource1" DataMember="RecebimentosPagamentos" PieType="Donut" LabelContentType="ArgumentValueAndPercent" ContentLineCount="4" ShowPieCaptions="false"><DataItems><Measure DataMember="valor" Name="Valor Total" DefaultId="DataItem0"><NumericFormat FormatType="Currency" Unit="Ones" IncludeGroupSeparator="true" CurrencyCultureName="pt-BR" /></Measure><Dimension DataMember="indReceitaDespesa" DefaultId="DataItem1" /></DataItems><Arguments><Argument DefaultId="DataItem1" /></Arguments><Values><Value DefaultId="DataItem0" /></Values></Pie><Chart ComponentName="chartDashboardItem1" Name="Recebimentos(receitas)" ParentContainer="dashboardItemGroup1" DataSource="dashboardSqlDataSource1" DataMember="RecebimentosPagamentos" FilterString="[DataItem2] = ''Receitas''"><DataItems><Dimension DataMember="dscReceitaDespesa" Name="Descrição" DefaultId="DataItem0" /><Measure DataMember="Receitas" Name="Receitas" DefaultId="DataItem1"><NumericFormat FormatType="Currency" Unit="Thousands" IncludeGroupSeparator="true" CurrencyCultureName="pt-BR" /></Measure><Dimension DataMember="indReceitaDespesa" DefaultId="DataItem2" /><Dimension DataMember="dscReceitaDespesa" SortOrder="Descending" SortByMeasure="DataItem1" DefaultId="DataItem3" /></DataItems><HiddenDimensions><Dimension DefaultId="DataItem2" /></HiddenDimensions><SeriesDimensions><SeriesDimension DefaultId="DataItem0" /></SeriesDimensions><Arguments><Argument DefaultId="DataItem3" /></Arguments><Panes><Pane Name="Pane 1"><AxisY Title="Receitas" Logarithmic="true" /><Series><Simple><Value DefaultId="DataItem1" /></Simple></Series></Pane></Panes><AxisX Title="Arguments" /></Chart><Chart ComponentName="chartDashboardItem2" Name="Pagamentos(despesas)" ParentContainer="dashboardItemGroup1" DataSource="dashboardSqlDataSource1" DataMember="RecebimentosPagamentos" FilterString="[DataItem1] = ''Despesas''"><DataItems><Dimension DataMember="dscReceitaDespesa" Name="Descrição" DefaultId="DataItem2" /><Measure DataMember="Despesas" Name="Despesas" DefaultId="DataItem0" /><Dimension DataMember="indReceitaDespesa" DefaultId="DataItem1" /><Dimension DataMember="dscReceitaDespesa" SortOrder="Descending" SortByMeasure="DataItem0" DefaultId="DataItem3" /></DataItems><HiddenDimensions><Dimension DefaultId="DataItem1" /></HiddenDimensions><SeriesDimensions><SeriesDimension DefaultId="DataItem2" /></SeriesDimensions><Arguments><Argument DefaultId="DataItem3" /></Arguments><Panes><Pane Name="Pane 1"><AxisY Logarithmic="true" /><Series><Simple><Value DefaultId="DataItem0" /></Simple></Series></Pane></Panes></Chart><RangeFilter ComponentName="rangeFilterDashboardItem1" Name="Recebimentos X Pagamentos por Período" ShowCaption="true" DataSource="dashboardSqlDataSource1" DataMember="RecebimentosPagamentos" IsMasterFilterCrossDataSource="true" SelectedDateTimePeriodIndex="Este Mês"><DataItems><Measure DataMember="Receitas" DefaultId="DataItem0" /><Measure DataMember="Despesas" DefaultId="DataItem1" /><Dimension DataMember="dataMovimentoCaixa" DateTimeGroupInterval="MonthYear" DefaultId="DataItem2" /></DataItems><Argument DefaultId="DataItem2" /><Series><Simple><Value DefaultId="DataItem0" /></Simple><Simple SeriesType="Area"><Value DefaultId="DataItem1" /></Simple></Series><DateTimePeriods><DateTimePeriod Name="Último Ano"><StartLimit><FlowDateTimePeriodLimit Offset="-1" /></StartLimit><EndLimit><FlowDateTimePeriodLimit /></EndLimit></DateTimePeriod><DateTimePeriod Name="Este Ano"><StartLimit><FlowDateTimePeriodLimit /></StartLimit><EndLimit><FlowDateTimePeriodLimit Offset="1" /></EndLimit></DateTimePeriod><DateTimePeriod Name="Últimos 3 Meses"><StartLimit><FlowDateTimePeriodLimit Interval="Month" Offset="-2" /></StartLimit><EndLimit><FlowDateTimePeriodLimit Interval="Month" Offset="1" /></EndLimit></DateTimePeriod><DateTimePeriod Name="Último Mês"><StartLimit><FlowDateTimePeriodLimit Interval="Month" Offset="-1" /></StartLimit><EndLimit><FlowDateTimePeriodLimit Interval="Month" /></EndLimit></DateTimePeriod><DateTimePeriod Name="Este Mês"><StartLimit><FlowDateTimePeriodLimit Interval="Month" /></StartLimit><EndLimit><FlowDateTimePeriodLimit Interval="Month" Offset="1" /></EndLimit></DateTimePeriod></DateTimePeriods></RangeFilter></Items><Groups><Group ComponentName="dashboardItemGroup1" Name="" ShowCaption="false"><InteractivityOptions IgnoreMasterFilters="false" /></Group></Groups><ColorScheme><Entry DataSource="dashboardSqlDataSource1" DataMember="RecebimentosPagamentos" PaletteIndex="2"><DimensionKeys><DimensionKey><Definition DataMember="dscReceitaDespesa" /><Value Type="System.String" Value="DEPOSITO BANCO" /></DimensionKey></DimensionKeys></Entry><Entry DataSource="dashboardSqlDataSource1" DataMember="RecebimentosPagamentos" Color="-13395610"><DimensionKeys><DimensionKey><Definition DataMember="indReceitaDespesa" /><Value Type="System.String" Value="Receitas" /></DimensionKey></DimensionKeys></Entry><Entry DataSource="dashboardSqlDataSource1" DataMember="RecebimentosPagamentos" Color="-3381760"><DimensionKeys><DimensionKey><Definition DataMember="indReceitaDespesa" /><Value Type="System.String" Value="Despesas" /></DimensionKey></DimensionKeys></Entry><Entry DataSource="dashboardSqlDataSource1" DataMember="RecebimentosPagamentos" Color="-3381760"><MeasureKey><Definition DataMember="Despesas" /></MeasureKey></Entry><Entry DataSource="dashboardSqlDataSource1" DataMember="RecebimentosPagamentos" Color="-13395610"><MeasureKey><Definition DataMember="Receitas" /></MeasureKey></Entry></ColorScheme><LayoutTree><LayoutGroup Orientation="Vertical" Weight="100"><LayoutGroup DashboardItem="dashboardItemGroup1" Weight="76.678876678876676"><LayoutItem DashboardItem="pieDashboardItem1" Weight="45.448460508701473" /><LayoutGroup Orientation="Vertical" Weight="54.551539491298527"><LayoutItem DashboardItem="chartDashboardItem1" Weight="49.913043478260867" /><LayoutItem DashboardItem="chartDashboardItem2" Weight="50.086956521739133" /></LayoutGroup></LayoutGroup><LayoutItem DashboardItem="rangeFilterDashboardItem1" Weight="23.32112332112332" /></LayoutGroup></LayoutTree></Dashboard>')
GO
INSERT [dbo].[Dashboard] ([id], [descricao], [idDashboardGrupo], [xml]) VALUES (3, N'Contas a Pagar X Receber', 5, N'<Dashboard CurrencyCulture="pt-BR"><Title Text="Contas a Pagar X Receber" /><DataSources><SqlDataSource Name="SQL Data Source 1" ComponentName="dashboardSqlDataSource1"><Connection Name="localhost_TryNFeOnline_Connection" ProviderKey="MSSqlServer"><Parameters><Parameter Name="server" Value="DANIELJORIS\SQLEXPRESS2017" /><Parameter Name="database" Value="TryNFeOnlineDashboard" /><Parameter Name="useIntegratedSecurity" Value="True" /><Parameter Name="read only" Value="1" /><Parameter Name="generateConnectionHelper" Value="false" /><Parameter Name="userid" Value="" /><Parameter Name="password" Value="" /></Parameters></Connection><Query Type="CustomSqlQuery" Name="Emitentes"><Sql>select 0 as id, ''Nenhum emitente selecionado'' as fantasia,''Nenhum emitente selecionado'' as razaosocial
UNION ALL
select id, fantasia, razaosocial from emitente where (coalesce(idnBaixa, 0) = 0)
</Sql></Query><Query Type="CustomSqlQuery" Name="ContasPagarReceber"><Parameter Name="idEmitente" Type="DevExpress.DataAccess.Expression">(System.Int32)(?idEmitente)</Parameter><Parameter Name="idEmitenteLogado" Type="DevExpress.DataAccess.Expression">(System.Int32)(?idEmitenteLogado)</Parameter><Sql>select
(cast(f.dataHoraEmissao as date)) as dataEmissao,
(cast(fp.dataVencimento as date)) as dataVencimentoParcelaFatura,
f.dataFatura as dataCompetenciaFatura,
fp.saldo as saldoRestanteParcela,
(case when fcc.id is null then f.valor else (case when fcc.idnPercentualValor = ''P'' then ((f.valor * fcc.valor) / 100) else fcc.valor end) end) as valorFatura,
(case when f.idnReceitaDespesa = ''+'' then ''Receita'' else ''Despesa'' end) as identificadorReceitaDespesa,
rd.descricao as dscReceitaDespesa, 
fm.descricao as dscFormaPagamento, 
fm.abreviatura as dscFormaAbreviatura
from FaturaParcela fp
inner join fatura f on f.id = fp.idFatura
inner join emitente em on em.id = f.idEmitente
inner join ReceitaDespesa rd on rd.id = f.idReceitaDespesa
left join FormaPagamento fm on fm.id = f.idFormaPagamento
left join FaturaParcelaPagamento fpp on fpp.idFaturaParcela = fp.id
left join CaixaMovimento cm on cm.idFaturaParcelaPagamentoOrigem = fpp.id
left join FaturaCentroCusto fcc on fcc.idFatura = f.id
where (((f.idEmitente = @idEmitente) and (@idEmitente &gt; 0)) or ((f.idEmitente = @idEmitenteLogado) and (@idEmitente = 0)))
and fp.saldo &gt; 0 -- Saldo da parcela precisa ser maior que zero
and (fp.idnBaixado is null or fp.idnBaixado = 0) -- Parcela da fatura não deve estar baixada/cancelada
and ((f.idnSaque = 0) or (f.idnSaque is null))
and ((f.idnDeposito = 0) or (f.idnDeposito is null))
and ((rd.idnIgnorarNosRelatorios = 0) or (rd.idnIgnorarNosRelatorios is null)) -- Deve estar com o identificador de ignorar nos relatórios desmarcado
and ((f.idnTrocaNumerario = 0) or (f.idnTrocaNumerario is null))</Sql></Query><Query Type="CustomSqlQuery" Name="ContasEmAbertoLiquidadas"><Parameter Name="idEmitente" Type="DevExpress.DataAccess.Expression">(System.Int32)(?idEmitente)</Parameter><Parameter Name="idEmitenteLogado" Type="DevExpress.DataAccess.Expression">(System.Int32)(?idEmitenteLogado)</Parameter><Sql>select
f.dataHoraEmissao as dataHoraEmissao,
fp.dataVencimento as dataVencimentoParcelaFatura,
f.dataFatura as dataCompetenciaFatura,
fp.saldo as saldoRestanteParcela,
(case when fcc.id is null then f.valor else (case when fcc.idnPercentualValor = ''P'' then ((f.valor * fcc.valor) / 100) else fcc.valor end) end) as valorFaturaParcela,
(case when f.idnReceitaDespesa = ''+'' then ''Receita'' else ''Despesa'' end) as identificadorReceitaDespesa,
rd.descricao as dscReceitaDespesa,
cm.dataHora as dataHoraLiquidacaoCaixa
from FaturaParcela fp
inner join fatura f on f.id = fp.idFatura
inner join ReceitaDespesa rd on rd.id = f.idReceitaDespesa
left join FormaPagamento fm on fm.id = f.idFormaPagamento
left join FaturaParcelaPagamento fpp on fpp.idFaturaParcela = fp.id
left join CaixaMovimento cm on cm.idFaturaParcelaPagamentoOrigem = fpp.id
left join FaturaCentroCusto fcc on fcc.idFatura = f.id
where (((f.idEmitente = @idEmitente) and (@idEmitente &gt; 0)) or ((f.idEmitente = @idEmitenteLogado) and (@idEmitente = 0)))
and (fp.idnBaixado is null or fp.idnBaixado = 0) -- Parcela da fatura não deve estar baixada/cancelada
and ((f.idnSaque = 0) or (f.idnSaque is null))
and ((f.idnDeposito = 0) or (f.idnDeposito is null))
and ((rd.idnIgnorarNosRelatorios = 0) or (rd.idnIgnorarNosRelatorios is null)) -- Deve estar com o identificador de ignorar nos relatórios desmarcado
and ((f.idnTrocaNumerario = 0) or (f.idnTrocaNumerario is null))
</Sql></Query><ResultSchema><DataSet Name="SQL Data Source 1"><View Name="Emitentes"><Field Name="id" Type="Int32" /><Field Name="fantasia" Type="String" /><Field Name="razaosocial" Type="String" /></View><View Name="ContasPagarReceber"><Field Name="dataEmissao" Type="DateTime" /><Field Name="dataVencimentoParcelaFatura" Type="DateTime" /><Field Name="dataCompetenciaFatura" Type="DateTime" /><Field Name="saldoRestanteParcela" Type="Decimal" /><Field Name="valorFatura" Type="Decimal" /><Field Name="identificadorReceitaDespesa" Type="String" /><Field Name="dscReceitaDespesa" Type="String" /><Field Name="dscFormaPagamento" Type="String" /><Field Name="dscFormaAbreviatura" Type="String" /></View><View Name="ContasEmAbertoLiquidadas"><Field Name="dataHoraEmissao" Type="DateTime" /><Field Name="dataVencimentoParcelaFatura" Type="DateTime" /><Field Name="dataCompetenciaFatura" Type="DateTime" /><Field Name="saldoRestanteParcela" Type="Decimal" /><Field Name="valorFaturaParcela" Type="Decimal" /><Field Name="identificadorReceitaDespesa" Type="String" /><Field Name="dscReceitaDespesa" Type="String" /><Field Name="dataHoraLiquidacaoCaixa" Type="DateTime" /></View></DataSet></ResultSchema><ConnectionOptions CloseConnection="true" /><CalculatedFields><CalculatedField Name="SaldoReceber" Expression="Sum(Iif([identificadorReceitaDespesa] = ''Receita'', [saldoRestanteParcela],0))" DataType="Auto" DataMember="ContasPagarReceber" /><CalculatedField Name="SaldoPagar" Expression="Sum(Iif([identificadorReceitaDespesa] = ''Despesa'', [saldoRestanteParcela], 0))" DataType="Auto" DataMember="ContasPagarReceber" /><CalculatedField Name="TotalFaturasPagas" Expression="Sum(Iif([saldoRestanteParcela] = 0, [valorFaturaParcela],0))" DataType="Auto" DataMember="ContasEmAbertoLiquidadas" /><CalculatedField Name="TotalFaturasAbertas" Expression="Sum(Iif([saldoRestanteParcela] &gt; 0 &amp;&amp; [dataVencimentoParcelaFatura] &gt;= Today(), [saldoRestanteParcela], 0))" DataType="Auto" DataMember="ContasEmAbertoLiquidadas" /><CalculatedField Name="TotalFaturasComSaldo" Expression="Sum([saldoRestanteParcela])" DataType="Auto" DataMember="ContasEmAbertoLiquidadas" /><CalculatedField Name="TotalFaturasVencidas" Expression="Sum(Iif([saldoRestanteParcela] &gt; 0 &amp;&amp; [dataVencimentoParcelaFatura] &lt; Today(), [saldoRestanteParcela], 0))" DataType="Auto" DataMember="ContasEmAbertoLiquidadas" /><CalculatedField Name="PercentualFaturasEmAtraso" Expression="([TotalFaturasVencidas] * 100) / [TotalFaturasAbertas] " DataType="Auto" DataMember="ContasEmAbertoLiquidadas" /></CalculatedFields></SqlDataSource></DataSources><Parameters><Parameter Name="idEmitente" Type="System.Int32, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Value="0" Description="Filial/empresa"><DynamicListLookUpSettings DataSourceName="dashboardSqlDataSource1" DataMember="Emitentes" ValueMember="id" DisplayMember="fantasia" /></Parameter><Parameter Name="idEmitenteLogado" Type="System.Int32, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Value="0" Visible="false" /></Parameters><Items><Card ComponentName="cardDashboardItem1" Name="A receber hoje" ShowCaption="false" ParentContainer="dashboardItemGroup1" DataSource="dashboardSqlDataSource1" DataMember="ContasPagarReceber" FilterString="[DataItem1] = ''Receita'' And [DataItem2] = Today()" ContentArrangementMode="FixedColumnCount"><DataItems><Measure DataMember="saldoRestanteParcela" Name="Saldo a Receber Hoje" DefaultId="DataItem0"><NumericFormat FormatType="Currency" Unit="Ones" IncludeGroupSeparator="true" CurrencyCultureName="pt-BR" /></Measure><Dimension DataMember="identificadorReceitaDespesa" DefaultId="DataItem1" /><Dimension DataMember="dataVencimentoParcelaFatura" DateTimeGroupInterval="DayMonthYear" DefaultId="DataItem2" /></DataItems><HiddenDimensions><Dimension DefaultId="DataItem1" /><Dimension DefaultId="DataItem2" /></HiddenDimensions><Card Name="A receber hoje"><ActualValue DefaultId="DataItem0" /><AbsoluteVariationNumericFormat /><PercentVariationNumericFormat /><PercentOfTargetNumericFormat /><LayoutTemplate MinWidth="270" MaxWidth="270" Type="Centered"><MainValue Visible="true" ValueType="Title" DimensionIndex="0" /><SubValue Visible="false" ValueType="Subtitle" DimensionIndex="0" /><BottomValue Visible="true" ValueType="ActualValue" DimensionIndex="0" /><BottomSubValue1 Visible="false" ValueType="AbsoluteVariation" DimensionIndex="0" /><BottomSubValue2 Visible="false" ValueType="PercentVariation" DimensionIndex="0" /><DeltaIndicator Visible="false" /><Sparkline Visible="false" /></LayoutTemplate></Card></Card><Card ComponentName="cardDashboardItem2" Name="A receber no restante do mês" ShowCaption="false" ParentContainer="dashboardItemGroup1" DataSource="dashboardSqlDataSource1" DataMember="ContasPagarReceber" FilterString="[DataItem1] = ''Receita'' And [DataItem2] &gt; LocalDateTimeToday() And [DataItem3] = LocalDateTimeThisMonth()"><DataItems><Measure DataMember="saldoRestanteParcela" Name="Saldo a Receber no Restante do Mês" DefaultId="DataItem0"><NumericFormat FormatType="Currency" Unit="Ones" IncludeGroupSeparator="true" CurrencyCultureName="pt-BR" /></Measure><Dimension DataMember="identificadorReceitaDespesa" DefaultId="DataItem1" /><Dimension DataMember="dataVencimentoParcelaFatura" DateTimeGroupInterval="DayMonthYear" DefaultId="DataItem2" /><Dimension DataMember="dataVencimentoParcelaFatura" DateTimeGroupInterval="MonthYear" DefaultId="DataItem3" /></DataItems><HiddenDimensions><Dimension DefaultId="DataItem1" /><Dimension DefaultId="DataItem2" /><Dimension DefaultId="DataItem3" /></HiddenDimensions><Card Name="No restante do mês"><ActualValue DefaultId="DataItem0" /><AbsoluteVariationNumericFormat /><PercentVariationNumericFormat /><PercentOfTargetNumericFormat /><LayoutTemplate MinWidth="270" MaxWidth="270" Type="Centered"><MainValue Visible="true" ValueType="Title" DimensionIndex="0" /><SubValue Visible="true" ValueType="Subtitle" DimensionIndex="0" /><BottomValue Visible="true" ValueType="ActualValue" DimensionIndex="0" /><BottomSubValue1 Visible="true" ValueType="AbsoluteVariation" DimensionIndex="0" /><BottomSubValue2 Visible="true" ValueType="PercentVariation" DimensionIndex="0" /><DeltaIndicator Visible="true" /><Sparkline Visible="true" /></LayoutTemplate></Card></Card><Card ComponentName="cardDashboardItem3" Name="A pagar hoje" ShowCaption="false" ParentContainer="dashboardItemGroup2" DataSource="dashboardSqlDataSource1" DataMember="ContasPagarReceber" FilterString="[DataItem1] = ''Despesa'' And [DataItem2] = Today()"><DataItems><Measure DataMember="saldoRestanteParcela" Name="Saldo a Pagar Hoje" DefaultId="DataItem0"><NumericFormat FormatType="Currency" Unit="Ones" IncludeGroupSeparator="true" CurrencyCultureName="pt-BR" /></Measure><Dimension DataMember="identificadorReceitaDespesa" DefaultId="DataItem1" /><Dimension DataMember="dataVencimentoParcelaFatura" DateTimeGroupInterval="DayMonthYear" DefaultId="DataItem2" /></DataItems><HiddenDimensions><Dimension DefaultId="DataItem1" /><Dimension DefaultId="DataItem2" /></HiddenDimensions><Card Name="A pagar hoje"><ActualValue DefaultId="DataItem0" /><CardDeltaOptions ResultIndicationMode="WarningIfGreater" /><AbsoluteVariationNumericFormat /><PercentVariationNumericFormat /><PercentOfTargetNumericFormat /><LayoutTemplate MinWidth="270" MaxWidth="270" Type="Centered"><MainValue Visible="true" ValueType="Title" DimensionIndex="0" /><SubValue Visible="true" ValueType="Subtitle" DimensionIndex="0" /><BottomValue Visible="true" ValueType="ActualValue" DimensionIndex="0" /><BottomSubValue1 Visible="true" ValueType="AbsoluteVariation" DimensionIndex="0" /><BottomSubValue2 Visible="true" ValueType="PercentVariation" DimensionIndex="0" /><DeltaIndicator Visible="true" /><Sparkline Visible="true" /></LayoutTemplate></Card></Card><Card ComponentName="cardDashboardItem4" Name="A pagar no restante do mês" ShowCaption="false" ParentContainer="dashboardItemGroup2" DataSource="dashboardSqlDataSource1" DataMember="ContasPagarReceber" FilterString="[DataItem1] = ''Despesa'' And [DataItem2] &gt; Today() And [DataItem3] = GetMonth(Today())"><DataItems><Measure DataMember="saldoRestanteParcela" Name="Saldo a Pagar no Restante do Mês" DefaultId="DataItem0"><NumericFormat FormatType="Currency" Unit="Ones" IncludeGroupSeparator="true" CurrencyCultureName="pt-BR" /></Measure><Dimension DataMember="identificadorReceitaDespesa" DefaultId="DataItem1" /><Dimension DataMember="dataVencimentoParcelaFatura" DateTimeGroupInterval="DayMonthYear" DefaultId="DataItem2" /><Dimension DataMember="dataVencimentoParcelaFatura" Name="dataVencimentoParcelaFatura (Month)" DateTimeGroupInterval="Month" DefaultId="DataItem3" /></DataItems><HiddenDimensions><Dimension DefaultId="DataItem1" /><Dimension DefaultId="DataItem2" /><Dimension DefaultId="DataItem3" /></HiddenDimensions><Card Name="No restante do mês"><ActualValue DefaultId="DataItem0" /><AbsoluteVariationNumericFormat /><PercentVariationNumericFormat /><PercentOfTargetNumericFormat /><LayoutTemplate MinWidth="270" MaxWidth="270" Type="Centered"><MainValue Visible="true" ValueType="Title" DimensionIndex="0" /><SubValue Visible="true" ValueType="Subtitle" DimensionIndex="0" /><BottomValue Visible="true" ValueType="ActualValue" DimensionIndex="0" /><BottomSubValue1 Visible="true" ValueType="AbsoluteVariation" DimensionIndex="0" /><BottomSubValue2 Visible="true" ValueType="PercentVariation" DimensionIndex="0" /><DeltaIndicator Visible="true" /><Sparkline Visible="true" /></LayoutTemplate></Card></Card><Gauge ComponentName="gaugeDashboardItem1" Name="Recebimentos em atraso" ShowCaption="false" ParentContainer="dashboardItemGroup3" DataSource="dashboardSqlDataSource1" DataMember="ContasPagarReceber" FilterString="[DataItem1] = ''Receita'' And [DataItem2] &lt; LocalDateTimeToday()" ContentLineCount="1" ViewType="LinearVertical"><DataItems><Measure DataMember="saldoRestanteParcela" Name="Recebimentos em atraso" DefaultId="DataItem0"><NumericFormat FormatType="Currency" Unit="Ones" IncludeGroupSeparator="true" CurrencyCultureName="pt-BR" /></Measure><Dimension DataMember="identificadorReceitaDespesa" DefaultId="DataItem1" /><Dimension DataMember="dataVencimentoParcelaFatura" DateTimeGroupInterval="DayMonthYear" DefaultId="DataItem2" /></DataItems><HiddenDimensions><Dimension DefaultId="DataItem1" /><Dimension DefaultId="DataItem2" /></HiddenDimensions><GaugeElement Name="Recebimentos em atraso"><ActualValue DefaultId="DataItem0" /><AbsoluteVariationNumericFormat /><PercentVariationNumericFormat /><PercentOfTargetNumericFormat /><ScaleLabelNumericFormat Unit="Ones" IncludeGroupSeparator="true" CurrencyCultureName="pt-BR" /></GaugeElement></Gauge><Gauge ComponentName="gaugeDashboardItem2" Name="Pagamentos em atraso" ShowCaption="false" ParentContainer="dashboardItemGroup3" DataSource="dashboardSqlDataSource1" DataMember="ContasPagarReceber" FilterString="[DataItem2] = ''Despesa'' And [DataItem1] &lt; Today()" ContentLineCount="1" ViewType="LinearVertical"><InteractivityOptions IgnoreMasterFilters="true" /><DataItems><Measure DataMember="saldoRestanteParcela" Name="Pagamentos em atraso" DefaultId="DataItem0"><NumericFormat FormatType="Currency" Unit="Ones" IncludeGroupSeparator="true" CurrencyCultureName="pt-BR" /></Measure><Dimension DataMember="dataVencimentoParcelaFatura" DateTimeGroupInterval="DayMonthYear" DefaultId="DataItem1" /><Dimension DataMember="identificadorReceitaDespesa" DefaultId="DataItem2" /></DataItems><HiddenDimensions><Dimension DefaultId="DataItem2" /><Dimension DefaultId="DataItem1" /></HiddenDimensions><GaugeElement><ActualValue DefaultId="DataItem0" /><AbsoluteVariationNumericFormat /><PercentVariationNumericFormat /><PercentOfTargetNumericFormat /></GaugeElement></Gauge><RangeFilter ComponentName="rangeFilterDashboardItem1" Name="Range Filter 1" ParentContainer="dashboardItemGroup4" DataSource="dashboardSqlDataSource1" DataMember="ContasPagarReceber" IsMasterFilterCrossDataSource="true" SelectedDateTimePeriodIndex="Este Mês"><DataItems><Measure DataMember="SaldoPagar" Name="Despesas" DefaultId="DataItem1" /><Measure DataMember="SaldoReceber" Name="Receitas" DefaultId="DataItem2" /><Dimension DataMember="dataCompetenciaFatura" DefaultId="DataItem0" /></DataItems><Argument DefaultId="DataItem0" /><Series><Simple><Value DefaultId="DataItem1" /></Simple><Simple><Value DefaultId="DataItem2" /></Simple></Series><DateTimePeriods><DateTimePeriod Name="Este Mês"><StartLimit><FlowDateTimePeriodLimit Interval="Month" /></StartLimit><EndLimit><FlowDateTimePeriodLimit Interval="Month" Offset="1" /></EndLimit></DateTimePeriod><DateTimePeriod Name="Último Mês"><StartLimit><FlowDateTimePeriodLimit Interval="Month" Offset="-1" /></StartLimit><EndLimit><FlowDateTimePeriodLimit Interval="Month" /></EndLimit></DateTimePeriod><DateTimePeriod Name="Últimos 3 Meses"><StartLimit><FlowDateTimePeriodLimit Interval="Month" Offset="-2" /></StartLimit><EndLimit><FlowDateTimePeriodLimit Interval="Month" Offset="1" /></EndLimit></DateTimePeriod><DateTimePeriod Name="Últimos 6 Meses"><StartLimit><FlowDateTimePeriodLimit Interval="Month" Offset="-5" /></StartLimit><EndLimit><FlowDateTimePeriodLimit Interval="Month" Offset="1" /></EndLimit></DateTimePeriod><DateTimePeriod Name="Este Ano"><StartLimit><FlowDateTimePeriodLimit /></StartLimit><EndLimit><FlowDateTimePeriodLimit Offset="1" /></EndLimit></DateTimePeriod><DateTimePeriod Name="Último Ano"><StartLimit><FlowDateTimePeriodLimit Offset="-1" /></StartLimit><EndLimit><FlowDateTimePeriodLimit /></EndLimit></DateTimePeriod></DateTimePeriods></RangeFilter><Pie ComponentName="pieDashboardItem1" Name="Por status" ParentContainer="dashboardItemGroup5" DataSource="dashboardSqlDataSource1" DataMember="ContasEmAbertoLiquidadas" PieType="Donut"><InteractivityOptions MasterFilterMode="Single" /><DataItems><Measure DataMember="TotalFaturasAbertas" Name="Faturas em aberto" DefaultId="DataItem0"><NumericFormat FormatType="Currency" Unit="Ones" IncludeGroupSeparator="true" CurrencyCultureName="pt-BR" /></Measure><Measure DataMember="TotalFaturasPagas" Name="Faturas pagas" DefaultId="DataItem1"><NumericFormat FormatType="Currency" Unit="Ones" IncludeGroupSeparator="true" CurrencyCultureName="pt-BR" /></Measure><Measure DataMember="TotalFaturasVencidas" Name="Faturas vencidas" DefaultId="DataItem2"><NumericFormat FormatType="Currency" Unit="Ones" IncludeGroupSeparator="true" CurrencyCultureName="pt-BR" /></Measure><Dimension DataMember="dataHoraLiquidacaoCaixa" DateTimeGroupInterval="MonthYear" DefaultId="DataItem3" /></DataItems><HiddenDimensions><Dimension DefaultId="DataItem3" /></HiddenDimensions><Values><Value DefaultId="DataItem0" /><Value DefaultId="DataItem1" /><Value DefaultId="DataItem2" /></Values></Pie><Pie ComponentName="pieDashboardItem2" Name="Por tipo" ParentContainer="dashboardItemGroup5" DataSource="dashboardSqlDataSource1" DataMember="ContasEmAbertoLiquidadas" PieType="Donut"><InteractivityOptions MasterFilterMode="Single" /><DataItems><Measure DataMember="TotalFaturasAbertas" Name="Faturas em aberto" DefaultId="DataItem0"><NumericFormat FormatType="Currency" Unit="Ones" IncludeGroupSeparator="true" CurrencyCultureName="pt-BR" /></Measure><Measure DataMember="TotalFaturasPagas" Name="Faturas pagas" DefaultId="DataItem1"><NumericFormat FormatType="Currency" Unit="Ones" IncludeGroupSeparator="true" CurrencyCultureName="pt-BR" /></Measure><Measure DataMember="TotalFaturasVencidas" Name="Faturas vencidas" DefaultId="DataItem2"><NumericFormat FormatType="Currency" Unit="Ones" IncludeGroupSeparator="true" CurrencyCultureName="pt-BR" /></Measure><Dimension DataMember="dataHoraLiquidacaoCaixa" DateTimeGroupInterval="MonthYear" DefaultId="DataItem3" /><Dimension DataMember="identificadorReceitaDespesa" SortOrder="Descending" DefaultId="DataItem4" /></DataItems><HiddenDimensions><Dimension DefaultId="DataItem3" /></HiddenDimensions><SeriesDimensions><SeriesDimension DefaultId="DataItem4" /></SeriesDimensions><Values><Value DefaultId="DataItem0" /><Value DefaultId="DataItem1" /><Value DefaultId="DataItem2" /></Values></Pie></Items><Groups><Group ComponentName="dashboardItemGroup1" Name="Contas a Receber" /><Group ComponentName="dashboardItemGroup2" Name="Contas a Pagar" /><Group ComponentName="dashboardItemGroup3" Name="Em Atraso" /><Group ComponentName="dashboardItemGroup4" Name="Contas a Receber X Pagar por Período"><InteractivityOptions IsMasterFilter="true" /></Group><Group ComponentName="dashboardItemGroup5" Name="Faturas"><InteractivityOptions IgnoreMasterFilters="false" /></Group></Groups><ColorScheme><Entry DataSource="dashboardSqlDataSource1" DataMember="ContasPagarReceber" Color="-3381760"><MeasureKey><Definition DataMember="SaldoPagar" /></MeasureKey></Entry><Entry DataSource="dashboardSqlDataSource1" DataMember="ContasPagarReceber" Color="-13395610"><MeasureKey><Definition DataMember="SaldoReceber" /></MeasureKey></Entry></ColorScheme><LayoutTree><LayoutGroup Orientation="Vertical" Weight="100"><LayoutGroup Weight="68.253968253968253"><LayoutGroup Orientation="Vertical" Weight="29.774535809018566"><LayoutGroup Orientation="Vertical" DashboardItem="dashboardItemGroup1" Weight="47.048300536672627"><LayoutItem DashboardItem="cardDashboardItem1" Weight="48.095238095238095" /><LayoutItem DashboardItem="cardDashboardItem2" Weight="51.904761904761905" /></LayoutGroup><LayoutGroup Orientation="Vertical" DashboardItem="dashboardItemGroup2" Weight="52.951699463327373"><LayoutItem DashboardItem="cardDashboardItem3" Weight="46.913580246913583" /><LayoutItem DashboardItem="cardDashboardItem4" Weight="53.086419753086417" /></LayoutGroup></LayoutGroup><LayoutGroup Weight="70.225464190981427"><LayoutGroup DashboardItem="dashboardItemGroup3" Weight="39.848914069877246"><LayoutItem DashboardItem="gaugeDashboardItem1" Weight="50.245098039215684" /><LayoutItem DashboardItem="gaugeDashboardItem2" Weight="49.754901960784316" /></LayoutGroup><LayoutGroup Orientation="Vertical" DashboardItem="dashboardItemGroup5" Weight="60.151085930122754"><LayoutItem DashboardItem="pieDashboardItem1" Weight="50" /><LayoutItem DashboardItem="pieDashboardItem2" Weight="50" /></LayoutGroup></LayoutGroup></LayoutGroup><LayoutGroup Orientation="Vertical" DashboardItem="dashboardItemGroup4" Weight="31.746031746031747"><LayoutItem DashboardItem="rangeFilterDashboardItem1" Weight="100" /></LayoutGroup></LayoutGroup></LayoutTree></Dashboard>')
GO
INSERT [dbo].[Dashboard] ([id], [descricao], [idDashboardGrupo], [xml]) VALUES (4, N'Receitas por Tipo', 5, N'<Dashboard CurrencyCulture="pt-BR"><Title Text="Receitas por Tipo" /><DataSources><SqlDataSource Name="SQL Data Source 1" ComponentName="dashboardSqlDataSource1"><Connection Name="localhost_TryNFeOnline_Connection" ProviderKey="MSSqlServer"><Parameters><Parameter Name="server" Value="DANIELJORIS\SQLEXPRESS2017" /><Parameter Name="database" Value="TryNFeOnlineDashboard" /><Parameter Name="useIntegratedSecurity" Value="True" /><Parameter Name="read only" Value="1" /><Parameter Name="generateConnectionHelper" Value="false" /><Parameter Name="userid" Value="" /><Parameter Name="password" Value="" /></Parameters></Connection><Query Type="CustomSqlQuery" Name="Emitentes"><Sql>select 0 as id, ''Nenhum emitente selecionado'' as fantasia,''Nenhum emitente selecionado'' as razaosocial
UNION ALL
select id, fantasia, razaosocial from emitente where (coalesce(idnBaixa, 0) = 0)
</Sql></Query><Query Type="CustomSqlQuery" Name="Query"><Parameter Name="idEmitente" Type="DevExpress.DataAccess.Expression">(System.Int32)(?idEmitente)</Parameter><Parameter Name="idEmitenteLogado" Type="DevExpress.DataAccess.Expression">(System.Int32)(?idEmitenteLogado)</Parameter><Sql>select 
fpp.valor, 
recDes.descricao as dscReceitaDespesa, 
(case when f.idnReceitaDespesa = ''+'' then ''Receitas'' else ''Despesas'' end) as indReceitaDespesa,
cm.dataHora as dataHoraMovimentoCaixa
from FaturaParcelaPagamento fpp
inner join FaturaParcela fp on fp.id = fpp.idFaturaParcela
inner join Fatura f on f.id = fp.idFatura
--inner join Emitente em on em.id = f.idEmitente
inner join CaixaMovimento cm on cm.idFaturaParcelaPagamentoOrigem = fpp.id
--inner join Caixa ca on ca.id = cm.idCaixa
inner join ReceitaDespesa recDes on recDes.id = f.idReceitaDespesa
where (((f.idEmitente = @idEmitente) and (@idEmitente &gt; 0)) or ((f.idEmitente = @idEmitenteLogado) and (@idEmitente = 0)))
--Isso tras somente as despesas de taxas, ignorando os recebimentos para não duplicar os valores
and (((f.idnLancamentoCartao = 0) or (f.idnLancamentoCartao is null)) or (f.idnReceitaDespesa = ''-''))
--Isso ignora os valores envolvidos no abatimento/troco, trazendo o valor real movimentado
and ((recDes.idnIgnorarNosRelatorios = 0) or (recDes.idnIgnorarNosRelatorios is null))
</Sql></Query><ResultSchema><DataSet Name="SQL Data Source 1"><View Name="Emitentes"><Field Name="id" Type="Int32" /><Field Name="fantasia" Type="String" /><Field Name="razaosocial" Type="String" /></View><View Name="Query"><Field Name="valor" Type="Decimal" /><Field Name="dscReceitaDespesa" Type="String" /><Field Name="indReceitaDespesa" Type="String" /><Field Name="dataHoraMovimentoCaixa" Type="DateTime" /></View></DataSet></ResultSchema><ConnectionOptions CloseConnection="true" /></SqlDataSource></DataSources><Parameters><Parameter Name="idEmitente" Type="System.Int32, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Value="0" Description="Filial/empresa"><DynamicListLookUpSettings DataSourceName="dashboardSqlDataSource1" DataMember="Emitentes" ValueMember="id" DisplayMember="fantasia" SortByMember="id" /></Parameter><Parameter Name="idEmitenteLogado" Type="System.Int32, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Value="0" Visible="false" /></Parameters><Items><Chart ComponentName="chartDashboardItem1" Name="Resultado" ParentContainer="dashboardItemGroup1" DataSource="dashboardSqlDataSource1" DataMember="Query" FilterString="[DataItem0] = ''Receitas''" Rotated="true"><DataItems><Dimension DataMember="indReceitaDespesa" DefaultId="DataItem0" /><Dimension DataMember="dscReceitaDespesa" Name="Nome da Receita" DefaultId="DataItem1" /><Dimension DataMember="dscReceitaDespesa" DefaultId="DataItem5" /><Dimension DataMember="dataHoraMovimentoCaixa" Name="Mês/Ano da Receita" DateTimeGroupInterval="MonthYear" DefaultId="DataItem2" /><Measure DataMember="valor" Name="Valor" DefaultId="DataItem3"><NumericFormat FormatType="Currency" Unit="Ones" IncludeGroupSeparator="true" CurrencyCultureName="pt-BR" /></Measure></DataItems><HiddenDimensions><Dimension DefaultId="DataItem0" /><Dimension DefaultId="DataItem2" /></HiddenDimensions><SeriesDimensions><SeriesDimension DefaultId="DataItem5" /></SeriesDimensions><Arguments><Argument DefaultId="DataItem1" /></Arguments><Panes><Pane Name="Pane 1"><AxisY Logarithmic="true" LogarithmicBase="Base2" /><Series><Simple><Value DefaultId="DataItem3" /></Simple></Series></Pane></Panes><AxisX TitleVisible="true" EnableZooming="true" LimitVisiblePoints="true" /></Chart><ListBox ComponentName="listBoxDashboardItem1" Name="Filtro por tipo" DataSource="dashboardSqlDataSource1" DataMember="Query" FilterString="[DataItem0] = ''Receitas''" EnableSearch="true"><DataItems><Dimension DataMember="indReceitaDespesa" DefaultId="DataItem0" /><Dimension DataMember="dscReceitaDespesa" Name="Nome da Receita" DefaultId="DataItem1" /></DataItems><HiddenDimensions><Dimension DefaultId="DataItem0" /></HiddenDimensions><FilterDimensions><Dimension DefaultId="DataItem1" /></FilterDimensions></ListBox><DateFilter ComponentName="dateFilterDashboardItem1" Name="Filtro por data" ParentContainer="dashboardItemGroup1" DataSource="dashboardSqlDataSource1" DataMember="Query" FilterString="[DataItem1] = ''Receitas''" SelectedDateTimePeriodIndex="Este Mês"><DataItems><Dimension DataMember="dataHoraMovimentoCaixa" DateTimeGroupInterval="MonthYear" DefaultId="DataItem0" /><Dimension DataMember="indReceitaDespesa" DefaultId="DataItem1" /></DataItems><HiddenDimensions><Dimension DefaultId="DataItem1" /></HiddenDimensions><Dimension DefaultId="DataItem0" /><DateTimePeriods><DateTimePeriod Name="Este Mês"><StartLimit><FlowDateTimePeriodLimit Interval="Month" /></StartLimit><EndLimit><FlowDateTimePeriodLimit Interval="Month" Offset="1" /></EndLimit></DateTimePeriod><DateTimePeriod Name="Último Mês"><StartLimit><FlowDateTimePeriodLimit Interval="Month" Offset="-1" /></StartLimit><EndLimit><FlowDateTimePeriodLimit Interval="Month" /></EndLimit></DateTimePeriod><DateTimePeriod Name="Últimos 3 Meses"><StartLimit><FlowDateTimePeriodLimit Interval="Month" Offset="-2" /></StartLimit><EndLimit><FlowDateTimePeriodLimit Interval="Month" Offset="1" /></EndLimit></DateTimePeriod><DateTimePeriod Name="Últimos 6 Meses"><StartLimit><FlowDateTimePeriodLimit Interval="Month" Offset="-5" /></StartLimit><EndLimit><FlowDateTimePeriodLimit Interval="Month" Offset="1" /></EndLimit></DateTimePeriod><DateTimePeriod Name="Este Ano"><StartLimit><FlowDateTimePeriodLimit /></StartLimit><EndLimit><FlowDateTimePeriodLimit Offset="1" /></EndLimit></DateTimePeriod><DateTimePeriod Name="Último Ano"><StartLimit><FlowDateTimePeriodLimit Offset="-1" /></StartLimit><EndLimit><FlowDateTimePeriodLimit /></EndLimit></DateTimePeriod></DateTimePeriods></DateFilter></Items><Groups><Group ComponentName="dashboardItemGroup1" Name="Resultado" ShowCaption="false"><InteractivityOptions IsMasterFilter="true" /></Group></Groups><LayoutTree><LayoutGroup Weight="100"><LayoutItem DashboardItem="listBoxDashboardItem1" Weight="14.522546419098143" /><LayoutGroup Orientation="Vertical" DashboardItem="dashboardItemGroup1" Weight="85.477453580901852"><LayoutItem DashboardItem="dateFilterDashboardItem1" Weight="10.622710622710622" /><LayoutItem DashboardItem="chartDashboardItem1" Weight="89.377289377289372" /></LayoutGroup></LayoutGroup></LayoutTree></Dashboard>')
GO
INSERT [dbo].[Dashboard] ([id], [descricao], [idDashboardGrupo], [xml]) VALUES (5, N'Despesas por Tipo', 5, N'<Dashboard CurrencyCulture="pt-BR"><Title Text="Despesas por Tipo" /><DataSources><SqlDataSource Name="SQL Data Source 1" ComponentName="dashboardSqlDataSource1"><Connection Name="localhost_TryNFeOnline_Connection" ProviderKey="MSSqlServer"><Parameters><Parameter Name="server" Value="DANIELJORIS\SQLEXPRESS2017" /><Parameter Name="database" Value="TryNFeOnlineDashboard" /><Parameter Name="useIntegratedSecurity" Value="True" /><Parameter Name="read only" Value="1" /><Parameter Name="generateConnectionHelper" Value="false" /><Parameter Name="userid" Value="" /><Parameter Name="password" Value="" /></Parameters></Connection><Query Type="CustomSqlQuery" Name="Emitentes"><Sql>select 0 as id, ''Nenhum emitente selecionado'' as fantasia,''Nenhum emitente selecionado'' as razaosocial
UNION ALL
select id, fantasia, razaosocial from emitente where (coalesce(idnBaixa, 0) = 0)
</Sql></Query><Query Type="CustomSqlQuery" Name="Query"><Parameter Name="idEmitente" Type="DevExpress.DataAccess.Expression">(System.Int32)(?idEmitente)</Parameter><Parameter Name="idEmitenteLogado" Type="DevExpress.DataAccess.Expression">(System.Int32)(?idEmitenteLogado)</Parameter><Sql>select 
fpp.valor, 
recDes.descricao as dscReceitaDespesa, 
(case when f.idnReceitaDespesa = ''+'' then ''Receitas'' else ''Despesas'' end) as indReceitaDespesa,
cm.dataHora as dataHoraMovimentoCaixa
from FaturaParcelaPagamento fpp
inner join FaturaParcela fp on fp.id = fpp.idFaturaParcela
inner join Fatura f on f.id = fp.idFatura
--inner join Emitente em on em.id = f.idEmitente
inner join CaixaMovimento cm on cm.idFaturaParcelaPagamentoOrigem = fpp.id
--inner join Caixa ca on ca.id = cm.idCaixa
inner join ReceitaDespesa recDes on recDes.id = f.idReceitaDespesa
where (((f.idEmitente = @idEmitente) and (@idEmitente &gt; 0)) or ((f.idEmitente = @idEmitenteLogado) and (@idEmitente = 0)))
--Isso tras somente as despesas de taxas, ignorando os recebimentos para não duplicar os valores
and (((f.idnLancamentoCartao = 0) or (f.idnLancamentoCartao is null)) or (f.idnReceitaDespesa = ''-''))
--Isso ignora os valores envolvidos no abatimento/troco, trazendo o valor real movimentado
and ((recDes.idnIgnorarNosRelatorios = 0) or (recDes.idnIgnorarNosRelatorios is null))
</Sql></Query><ResultSchema><DataSet Name="SQL Data Source 1"><View Name="Emitentes"><Field Name="id" Type="Int32" /><Field Name="fantasia" Type="String" /><Field Name="razaosocial" Type="String" /></View><View Name="Query"><Field Name="valor" Type="Decimal" /><Field Name="dscReceitaDespesa" Type="String" /><Field Name="indReceitaDespesa" Type="String" /><Field Name="dataHoraMovimentoCaixa" Type="DateTime" /></View></DataSet></ResultSchema><ConnectionOptions CloseConnection="true" /></SqlDataSource></DataSources><Parameters><Parameter Name="idEmitente" Type="System.Int32, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Value="0" Description="Filial/empresa"><DynamicListLookUpSettings DataSourceName="dashboardSqlDataSource1" DataMember="Emitentes" ValueMember="id" DisplayMember="fantasia" SortByMember="id" /></Parameter><Parameter Name="idEmitenteLogado" Type="System.Int32, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Value="0" Visible="false" /></Parameters><Items><Chart ComponentName="chartDashboardItem1" Name="Resultado" ParentContainer="dashboardItemGroup1" DataSource="dashboardSqlDataSource1" DataMember="Query" FilterString="[DataItem0] = ''Despesas''" Rotated="true"><DataItems><Dimension DataMember="indReceitaDespesa" DefaultId="DataItem0" /><Measure DataMember="valor" Name="Valor" DefaultId="DataItem3"><NumericFormat FormatType="Currency" Unit="Ones" IncludeGroupSeparator="true" CurrencyCultureName="pt-BR" /></Measure><Dimension DataMember="dataHoraMovimentoCaixa" Name="Mês/Ano da Despesa" DateTimeGroupInterval="MonthYear" DefaultId="DataItem2" /><Dimension DataMember="dscReceitaDespesa" Name="Nome da Despesa" DefaultId="DataItem4" /><Dimension DataMember="dscReceitaDespesa" Name="Nome da Despesa" DefaultId="DataItem1" /></DataItems><HiddenDimensions><Dimension DefaultId="DataItem0" /><Dimension DefaultId="DataItem2" /></HiddenDimensions><SeriesDimensions><SeriesDimension DefaultId="DataItem4" /></SeriesDimensions><Arguments><Argument DefaultId="DataItem1" /></Arguments><Panes><Pane Name="Pane 1"><AxisY Logarithmic="true" LogarithmicBase="Base2"><NumericFormat FormatType="Currency" Unit="Ones" IncludeGroupSeparator="true" CurrencyCultureName="pt-BR" /></AxisY><Series><Simple><Value DefaultId="DataItem3" /></Simple></Series></Pane></Panes><AxisX TitleVisible="true" LimitVisiblePoints="true" /></Chart><ListBox ComponentName="listBoxDashboardItem1" Name="Filtro por tipo" DataSource="dashboardSqlDataSource1" DataMember="Query" FilterString="[DataItem0] = ''Despesas''" IsMasterFilterCrossDataSource="true" EnableSearch="true"><DataItems><Dimension DataMember="indReceitaDespesa" DefaultId="DataItem0" /><Dimension DataMember="dscReceitaDespesa" Name="Nome da Despesa" DefaultId="DataItem1" /></DataItems><HiddenDimensions><Dimension DefaultId="DataItem0" /></HiddenDimensions><FilterDimensions><Dimension DefaultId="DataItem1" /></FilterDimensions></ListBox><DateFilter ComponentName="dateFilterDashboardItem1" Name="Filtro por data" ParentContainer="dashboardItemGroup1" DataSource="dashboardSqlDataSource1" DataMember="Query" SelectedDateTimePeriodIndex="Este Mês"><DataItems><Dimension DataMember="dataHoraMovimentoCaixa" DateTimeGroupInterval="MonthYear" DefaultId="DataItem0" /></DataItems><Dimension DefaultId="DataItem0" /><DateTimePeriods><DateTimePeriod Name="Este Mês"><StartLimit><FlowDateTimePeriodLimit Interval="Month" /></StartLimit><EndLimit><FlowDateTimePeriodLimit Interval="Month" Offset="1" /></EndLimit></DateTimePeriod><DateTimePeriod Name="Último Mês"><StartLimit><FlowDateTimePeriodLimit Interval="Month" Offset="-1" /></StartLimit><EndLimit><FlowDateTimePeriodLimit Interval="Month" /></EndLimit></DateTimePeriod><DateTimePeriod Name="Últimos 3 Meses"><StartLimit><FlowDateTimePeriodLimit Interval="Month" Offset="-2" /></StartLimit><EndLimit><FlowDateTimePeriodLimit Interval="Month" Offset="1" /></EndLimit></DateTimePeriod><DateTimePeriod Name="Últimos 6 Meses"><StartLimit><FlowDateTimePeriodLimit Interval="Month" Offset="-5" /></StartLimit><EndLimit><FlowDateTimePeriodLimit Interval="Month" Offset="1" /></EndLimit></DateTimePeriod><DateTimePeriod Name="Este Ano"><StartLimit><FlowDateTimePeriodLimit /></StartLimit><EndLimit><FlowDateTimePeriodLimit Offset="1" /></EndLimit></DateTimePeriod><DateTimePeriod Name="Último Ano"><StartLimit><FlowDateTimePeriodLimit Offset="-1" /></StartLimit><EndLimit><FlowDateTimePeriodLimit /></EndLimit></DateTimePeriod></DateTimePeriods></DateFilter></Items><Groups><Group ComponentName="dashboardItemGroup1" Name="Resultado" ShowCaption="false"><InteractivityOptions IgnoreMasterFilters="false" /></Group></Groups><LayoutTree><LayoutGroup Weight="100"><LayoutItem DashboardItem="listBoxDashboardItem1" Weight="14.854111405835544" /><LayoutGroup Orientation="Vertical" DashboardItem="dashboardItemGroup1" Weight="85.145888594164461"><LayoutItem DashboardItem="dateFilterDashboardItem1" Weight="21.123321123321123" /><LayoutItem DashboardItem="chartDashboardItem1" Weight="78.876678876678881" /></LayoutGroup></LayoutGroup></LayoutTree></Dashboard>')
GO
INSERT [dbo].[Dashboard] ([id], [descricao], [idDashboardGrupo], [xml]) VALUES (6, N'Balancete de Receita e Despesa', 6, N'<Dashboard CurrencyCulture="pt-BR"><Title Text="Balancete de Receitas e Despesas" /><DataSources><SqlDataSource Name="SQL Data Source 1" ComponentName="dashboardSqlDataSource1"><Connection Name="localhost_Connection" ProviderKey="MSSqlServer"><Parameters><Parameter Name="server" Value="DANIELJORIS\SQLEXPRESS2017" /><Parameter Name="database" Value="TryNFeOnlineDashboard" /><Parameter Name="useIntegratedSecurity" Value="True" /><Parameter Name="read only" Value="1" /><Parameter Name="generateConnectionHelper" Value="false" /><Parameter Name="userid" Value="" /><Parameter Name="password" Value="" /></Parameters></Connection><Query Type="CustomSqlQuery" Name="BalanceteReceitaDespesa"><Parameter Name="idEmitente" Type="DevExpress.DataAccess.Expression">(System.Int32)(?idEmitente)</Parameter><Parameter Name="idEmitenteLogado" Type="DevExpress.DataAccess.Expression">(System.Int32)(?idEmitenteLogado)</Parameter><Sql>select 
fp.id as idFaturaParcela, 
fp.idFatura as idFatura, 
f.idReceitaDespesa, 
fcc.id as idFaturaCentroCusto, 
fcc.idCentroCusto, 
rd.idGrupoReceitaDespesa, 
cc.descricao as dscCentroCusto, 
rd.descricao as dscReceitaDespesa, 
grd.descricao as dscGrupoReceitaDespesa, 
f.idnReceitaDespesa,
(case when f.idnReceitaDespesa = ''+'' then ''RECEITA'' else ''DESPESA'' end) as dscIdnReceitaDespesa,
sum((case when f.idnReceitaDespesa = ''+'' then (case when fcc.idnPercentualValor = ''P'' then ((fpp.valor * fcc.valor) / 100) 
else fcc.valor end) else 0 end)) as valorDebito,
sum((case when f.idnReceitaDespesa = ''-'' then (case when fcc.idnPercentualValor = ''P'' then ((fpp.valor * fcc.valor) / 100) 
else fcc.valor end) else 0 end)) as valorCredito,
(cast(cm.dataHora as date)) as dataMovimentacao
from FaturaCentroCusto fcc
inner join Fatura f on f.id = fcc.idFatura
inner join FaturaParcela fp on fp.idFatura = f.id
inner join FaturaParcelaPagamento fpp on fpp.idFaturaParcela = fp.id
inner join CaixaMovimento cm on cm.idFaturaParcelaPagamentoOrigem = fpp.id
inner join ReceitaDespesa rd on rd.id = f.idReceitaDespesa
inner join GrupoReceitaDespesa grd on grd.id = rd.idGrupoReceitaDespesa
inner join CentroCusto cc on cc.id = fcc.idCentroCusto
where (((f.idEmitente = @idEmitente) and (@idEmitente &gt; 0)) or ((f.idEmitente = @idEmitenteLogado) and (@idEmitente = 0)))
--(cast(fp.dataVencimento as date) between cast(@dataInicial as date) and cast(@dataFinal as date))
and ((f.idnTransferencia = 0) or (f.idnTransferencia is null))
and ((f.idnSaque = 0) or (f.idnSaque is null))
and ((f.idnDeposito = 0) or (f.idnDeposito is null))
and ((f.idnTransferenciaEntreContas = 0) or (f.idnTransferenciaEntreContas is null))
and ((rd.idnIgnorarNosRelatorios = 0) or (rd.idnIgnorarNosRelatorios is null))
and ((f.idnTrocaNumerario = 0) or (f.idnTrocaNumerario is null))
and ((f.idnLancamentoCartao = 0) or (f.idnLancamentoCartao is null))
group by cc.descricao, f.idnReceitaDespesa, grd.descricao, rd.descricao, f.idReceitaDespesa, fp.id,
fp.idFatura, fcc.id, fcc.idCentroCusto, rd.idGrupoReceitaDespesa, cm.dataHora
</Sql></Query><Query Type="CustomSqlQuery" Name="Emitentes"><Sql>select 0 as id, ''Nenhum emitente selecionado'' as fantasia,''Nenhum emitente selecionado'' as razaosocial
UNION ALL
select id, fantasia, razaosocial from emitente where (coalesce(idnBaixa, 0) = 0)
</Sql></Query><ResultSchema><DataSet Name="SQL Data Source 1"><View Name="BalanceteReceitaDespesa"><Field Name="idFaturaParcela" Type="Int32" /><Field Name="idFatura" Type="Int32" /><Field Name="idReceitaDespesa" Type="Int32" /><Field Name="idFaturaCentroCusto" Type="Int32" /><Field Name="idCentroCusto" Type="Int32" /><Field Name="idGrupoReceitaDespesa" Type="Int32" /><Field Name="dscCentroCusto" Type="String" /><Field Name="dscReceitaDespesa" Type="String" /><Field Name="dscGrupoReceitaDespesa" Type="String" /><Field Name="idnReceitaDespesa" Type="String" /><Field Name="dscIdnReceitaDespesa" Type="String" /><Field Name="valorDebito" Type="Decimal" /><Field Name="valorCredito" Type="Decimal" /><Field Name="dataMovimentacao" Type="DateTime" /></View><View Name="Emitentes"><Field Name="id" Type="Int32" /><Field Name="fantasia" Type="String" /><Field Name="razaosocial" Type="String" /></View></DataSet></ResultSchema><ConnectionOptions CloseConnection="true" /><CalculatedFields><CalculatedField Name="saldoAtual" Expression="(sum([valorDebito]) - sum([valorCredito]))" DataType="Auto" DataMember="BalanceteReceitaDespesa" /><CalculatedField Name="totalDebito" Expression="sum([valorDebito])" DataType="Auto" DataMember="BalanceteReceitaDespesa" /><CalculatedField Name="totalCredito" Expression="sum([valorCredito])" DataType="Auto" DataMember="BalanceteReceitaDespesa" /></CalculatedFields></SqlDataSource></DataSources><Parameters><Parameter Name="idEmitente" Type="System.Int32, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Value="0" Description="Filial/empresa"><DynamicListLookUpSettings DataSourceName="dashboardSqlDataSource1" DataMember="Emitentes" ValueMember="id" DisplayMember="fantasia" SortByMember="id" /></Parameter><Parameter Name="idEmitenteLogado" Type="System.Int32, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Value="0" /></Parameters><Items><Pie ComponentName="pieDashboardItem1" Name="Grupos" ParentContainer="dashboardItemGroup1" DataSource="dashboardSqlDataSource1" DataMember="BalanceteReceitaDespesa" FilterString="[DataItem4] = ''+''"><DataItems><Dimension DataMember="idnReceitaDespesa" DefaultId="DataItem4" /><Measure DataMember="saldoAtual" Name="Débito(receita)" DefaultId="DataItem5"><NumericFormat FormatType="Currency" Unit="Ones" IncludeGroupSeparator="true" CurrencyCultureName="pt-BR" /></Measure><Dimension DataMember="dscGrupoReceitaDespesa" Name="Grupo de Receita" DefaultId="DataItem1" /></DataItems><HiddenDimensions><Dimension DefaultId="DataItem4" /></HiddenDimensions><Arguments><Argument DefaultId="DataItem1" /></Arguments><Values><Value DefaultId="DataItem5" /></Values></Pie><Chart ComponentName="chartDashboardItem1" Name="Tipos" ParentContainer="dashboardItemGroup1" DataSource="dashboardSqlDataSource1" DataMember="BalanceteReceitaDespesa" FilterString="[DataItem4] = ''+''"><DataItems><Dimension DataMember="idnReceitaDespesa" DefaultId="DataItem4" /><Dimension DataMember="dscReceitaDespesa" DefaultId="DataItem5" /><Measure DataMember="saldoAtual" Name="Valor" DefaultId="DataItem0"><NumericFormat FormatType="Currency" Unit="Ones" IncludeGroupSeparator="true" CurrencyCultureName="pt-BR" /></Measure><Dimension DataMember="dscReceitaDespesa" DefaultId="DataItem1" /></DataItems><HiddenDimensions><Dimension DefaultId="DataItem4" /></HiddenDimensions><SeriesDimensions><SeriesDimension DefaultId="DataItem5" /></SeriesDimensions><Arguments><Argument DefaultId="DataItem1" /></Arguments><Panes><Pane Name="Pane 1"><Series><Simple><Value DefaultId="DataItem0" /></Simple></Series></Pane></Panes></Chart><Chart ComponentName="chartDashboardItem2" Name="Total Geral" DataSource="dashboardSqlDataSource1" DataMember="BalanceteReceitaDespesa"><DataItems><Measure DataMember="totalCredito" Name="Crédito (despesa)" DefaultId="DataItem0"><NumericFormat FormatType="Currency" Unit="Ones" IncludeGroupSeparator="true" CurrencyCultureName="pt-BR" /></Measure><Measure DataMember="totalDebito" Name="Débito (receita)" DefaultId="DataItem2"><NumericFormat FormatType="Currency" Unit="Ones" IncludeGroupSeparator="true" CurrencyCultureName="pt-BR" /></Measure><Measure DataMember="saldoAtual" Name="Saldo atual (receita - despesa)" DefaultId="DataItem1"><NumericFormat FormatType="Currency" Unit="Ones" IncludeGroupSeparator="true" CurrencyCultureName="pt-BR" /></Measure></DataItems><Panes><Pane Name="Pane 1"><Series><Simple><Value DefaultId="DataItem0" /></Simple><Simple><Value DefaultId="DataItem1" /></Simple><Simple><Value DefaultId="DataItem2" /></Simple></Series></Pane></Panes></Chart><Chart ComponentName="chartDashboardItem3" Name="Tipos" ParentContainer="dashboardItemGroup3" DataSource="dashboardSqlDataSource1" DataMember="BalanceteReceitaDespesa" FilterString="[DataItem4] = ''-''"><DataItems><Dimension DataMember="idnReceitaDespesa" DefaultId="DataItem4" /><Dimension DataMember="dscReceitaDespesa" DefaultId="DataItem5" /><Measure DataMember="totalCredito" Name="Valor" DefaultId="DataItem0"><NumericFormat FormatType="Currency" Unit="Ones" IncludeGroupSeparator="true" CurrencyCultureName="pt-BR" /></Measure><Dimension DataMember="dscReceitaDespesa" DefaultId="DataItem1" /></DataItems><HiddenDimensions><Dimension DefaultId="DataItem4" /></HiddenDimensions><SeriesDimensions><SeriesDimension DefaultId="DataItem5" /></SeriesDimensions><Arguments><Argument DefaultId="DataItem1" /></Arguments><Panes><Pane Name="Pane 1"><AxisY><NumericFormat FormatType="Currency" Unit="Ones" IncludeGroupSeparator="true" CurrencyCultureName="pt-BR" /></AxisY><Series><Simple><Value DefaultId="DataItem0" /></Simple></Series></Pane></Panes></Chart><Pie ComponentName="pieDashboardItem2" Name="Grupos" ParentContainer="dashboardItemGroup3" DataSource="dashboardSqlDataSource1" DataMember="BalanceteReceitaDespesa" FilterString="[DataItem4] = ''-''"><DataItems><Dimension DataMember="idnReceitaDespesa" DefaultId="DataItem4" /><Dimension DataMember="dscGrupoReceitaDespesa" Name="Grupo de Despesa" DefaultId="DataItem1" /><Measure DataMember="totalCredito" Name="Crédito(despesa)" DefaultId="DataItem2"><NumericFormat FormatType="Currency" Unit="Ones" IncludeGroupSeparator="true" CurrencyCultureName="pt-BR" /></Measure></DataItems><HiddenDimensions><Dimension DefaultId="DataItem4" /></HiddenDimensions><Arguments><Argument DefaultId="DataItem1" /></Arguments><Values><Value DefaultId="DataItem2" /></Values></Pie><DateFilter ComponentName="dateFilterDashboardItem1" Name="Período" DataSource="dashboardSqlDataSource1" DataMember="BalanceteReceitaDespesa" SelectedDateTimePeriodIndex="Este Mês"><DataItems><Dimension DataMember="dataMovimentacao" DateTimeGroupInterval="DayMonthYear" DefaultId="DataItem0" /></DataItems><Dimension DefaultId="DataItem0" /><DateTimePeriods><DateTimePeriod Name="Hoje"><StartLimit><FlowDateTimePeriodLimit Interval="Day" /></StartLimit><EndLimit><FlowDateTimePeriodLimit Interval="Day" Offset="1" /></EndLimit></DateTimePeriod><DateTimePeriod Name="Ontem"><StartLimit><FlowDateTimePeriodLimit Interval="Day" Offset="-1" /></StartLimit><EndLimit><FlowDateTimePeriodLimit Interval="Day" /></EndLimit></DateTimePeriod><DateTimePeriod Name="Últimos 7 Dias"><StartLimit><FlowDateTimePeriodLimit Interval="Day" Offset="-7" /></StartLimit><EndLimit><FlowDateTimePeriodLimit Interval="Day" /></EndLimit></DateTimePeriod><DateTimePeriod Name="Últimos 30 Dias"><StartLimit><FlowDateTimePeriodLimit Interval="Day" Offset="-30" /></StartLimit><EndLimit><FlowDateTimePeriodLimit Interval="Day" /></EndLimit></DateTimePeriod><DateTimePeriod Name="Este Mês"><StartLimit><FlowDateTimePeriodLimit Interval="Month" /></StartLimit><EndLimit><FlowDateTimePeriodLimit Interval="Month" Offset="1" /></EndLimit></DateTimePeriod><DateTimePeriod Name="Últimos 3 Meses"><StartLimit><FlowDateTimePeriodLimit Interval="Month" Offset="-3" /></StartLimit><EndLimit><FlowDateTimePeriodLimit Interval="Month" /></EndLimit></DateTimePeriod><DateTimePeriod Name="Últimos 6 Meses"><StartLimit><FlowDateTimePeriodLimit Interval="Month" Offset="-6" /></StartLimit><EndLimit><FlowDateTimePeriodLimit Interval="Month" /></EndLimit></DateTimePeriod><DateTimePeriod Name="Este Ano"><StartLimit><FlowDateTimePeriodLimit /></StartLimit><EndLimit><FlowDateTimePeriodLimit Offset="1" /></EndLimit></DateTimePeriod><DateTimePeriod Name="Último Ano"><StartLimit><FlowDateTimePeriodLimit Offset="-1" /></StartLimit><EndLimit><FlowDateTimePeriodLimit /></EndLimit></DateTimePeriod></DateTimePeriods></DateFilter></Items><Groups><Group ComponentName="dashboardItemGroup1" Name="Receitas"><InteractivityOptions IgnoreMasterFilters="false" /></Group><Group ComponentName="dashboardItemGroup3" Name="Despesas"><InteractivityOptions IgnoreMasterFilters="false" /></Group></Groups><ColorScheme><Entry DataSource="dashboardSqlDataSource1" DataMember="BalanceteReceitaDespesa" PaletteIndex="10"><DimensionKeys><DimensionKey><Definition DataMember="idnReceitaDespesa" TextGroupInterval="Alphabetical" /><Value Type="System.String" Value="RECEITA" /></DimensionKey></DimensionKeys></Entry><Entry DataSource="dashboardSqlDataSource1" DataMember="BalanceteReceitaDespesa" PaletteIndex="1"><DimensionKeys><DimensionKey><Definition DataMember="idnReceitaDespesa" TextGroupInterval="Alphabetical" /><Value Type="System.String" Value="DESPESA" /></DimensionKey></DimensionKeys></Entry><Entry DataSource="dashboardSqlDataSource1" DataMember="BalanceteReceitaDespesa" Color="-26368"><MeasureKey><Definition DataMember="totalCredito" /></MeasureKey></Entry><Entry DataSource="dashboardSqlDataSource1" DataMember="BalanceteReceitaDespesa" Color="-6723892"><MeasureKey><Definition DataMember="totalDebito" /></MeasureKey></Entry><Entry DataSource="dashboardSqlDataSource1" DataMember="BalanceteReceitaDespesa" Color="-13395508"><MeasureKey><Definition DataMember="saldoAtual" /></MeasureKey></Entry><Entry DataSource="dashboardSqlDataSource1" DataMember="BalanceteReceitaDespesa" PaletteIndex="19"><DimensionKeys><DimensionKey><Definition DataMember="dscGrupoReceitaDespesa" /><Value Type="System.String" Value="RECEITAS FINANCEIRAS" /></DimensionKey></DimensionKeys></Entry></ColorScheme><LayoutTree><LayoutGroup Weight="100"><LayoutGroup Orientation="Vertical" Weight="40.915119363395227"><LayoutItem DashboardItem="dateFilterDashboardItem1" Weight="13.797313797313798" /><LayoutItem DashboardItem="chartDashboardItem2" Weight="86.202686202686209" /></LayoutGroup><LayoutGroup Orientation="Vertical" Weight="59.084880636604773"><LayoutGroup DashboardItem="dashboardItemGroup1" Weight="47.374847374847377"><LayoutItem DashboardItem="chartDashboardItem1" Weight="48.802736602052448" /><LayoutItem DashboardItem="pieDashboardItem1" Weight="51.197263397947552" /></LayoutGroup><LayoutGroup DashboardItem="dashboardItemGroup3" Weight="52.625152625152623"><LayoutItem DashboardItem="chartDashboardItem3" Weight="49.258836944127708" /><LayoutItem DashboardItem="pieDashboardItem2" Weight="50.741163055872292" /></LayoutGroup></LayoutGroup></LayoutGroup></LayoutTree></Dashboard>')
GO
INSERT [dbo].[Dashboard] ([id], [descricao], [idDashboardGrupo], [xml]) VALUES (7, N'Recebimentos em Cartão', 5, N'<Dashboard CurrencyCulture="pt-BR"><Title Text="Recebimentos em Cartão" /><DataSources><SqlDataSource Name="SQL Data Source 1" ComponentName="dashboardSqlDataSource1"><Connection Name="localhost_TryNFeOnline_Connection" ProviderKey="MSSqlServer"><Parameters><Parameter Name="server" Value="DANIELJORIS\SQLEXPRESS2017" /><Parameter Name="database" Value="TryNFeOnlineDashboard" /><Parameter Name="useIntegratedSecurity" Value="True" /><Parameter Name="read only" Value="1" /><Parameter Name="generateConnectionHelper" Value="false" /><Parameter Name="userid" Value="" /><Parameter Name="password" Value="" /></Parameters></Connection><Query Type="CustomSqlQuery" Name="Emitentes"><Sql>select 0 as id, ''Nenhum emitente selecionado'' as fantasia,''Nenhum emitente selecionado'' as razaosocial
UNION ALL
select id, fantasia, razaosocial from emitente where (coalesce(idnBaixa, 0) = 0)
</Sql></Query><Query Type="CustomSqlQuery" Name="RecebimentosCartao"><Parameter Name="idEmitente" Type="DevExpress.DataAccess.Expression">(System.Int32)(?idEmitente)</Parameter><Parameter Name="idEmitenteLogado" Type="DevExpress.DataAccess.Expression">(System.Int32)(?idEmitenteLogado)</Parameter><Sql>select
cmp.id as idCartaoMovimentoParcela,
cp.id as idCartaoProduto, 
cp.descricao as dscProduto, 
ca.descricao as dscAdministradora, 
cast(cmp.dataVencimento as date) as dataVencimento, 
SUM(cmp.valorBruto) as valorBruto, 
SUM(cmp.valorLiquido) as valorLiquido, 
SUM(cmp.valorBruto - cmp.valorLiquido) as valorTaxas,
c.numeroConta, 
c.descricao as nomeContaDeposito,
(case when cmp.idnLoteGerado = 1 then ''Em conta'' else ''Não lançado'' end) as identificadorloteGerado
from CartaoMovimentoParcelas cmp
inner join CartaoMovimento cm on cmp.idCartaoMovimento = cm.id
inner join CartaoProdutos cp on cp.id = cm.idCartaoProduto
inner join Conta c on c.id = cm.idContaDeposito
inner join CartaoAdministradora ca on ca.id = cp.idAdministradora
where (((cp.idFilial = @idEmitente) and (@idEmitente &gt; 0)) or ((cp.idFilial = @idEmitenteLogado) and (@idEmitente = 0)))
group by cmp.id, cp.id, cp.descricao, ca.descricao, cmp.dataVencimento, c.id, c.numeroConta, c.descricao, cmp.idnLoteGerado</Sql></Query><ResultSchema><DataSet Name="SQL Data Source 1"><View Name="Emitentes"><Field Name="id" Type="Int32" /><Field Name="fantasia" Type="String" /><Field Name="razaosocial" Type="String" /></View><View Name="RecebimentosCartao"><Field Name="idCartaoMovimentoParcela" Type="Int32" /><Field Name="idCartaoProduto" Type="Int32" /><Field Name="dscProduto" Type="String" /><Field Name="dscAdministradora" Type="String" /><Field Name="dataVencimento" Type="DateTime" /><Field Name="valorBruto" Type="Decimal" /><Field Name="valorLiquido" Type="Decimal" /><Field Name="valorTaxas" Type="Decimal" /><Field Name="numeroConta" Type="String" /><Field Name="nomeContaDeposito" Type="String" /><Field Name="identificadorloteGerado" Type="String" /></View></DataSet></ResultSchema><ConnectionOptions CloseConnection="true" /></SqlDataSource></DataSources><Parameters><Parameter Name="idEmitente" Type="System.Int32, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Value="0" Description="Filial/empresa"><DynamicListLookUpSettings DataSourceName="dashboardSqlDataSource1" DataMember="Emitentes" ValueMember="id" DisplayMember="razaosocial" SortByMember="id" /></Parameter><Parameter Name="idEmitenteLogado" Type="System.Int32, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Value="0" Visible="false" /></Parameters><Items><Card ComponentName="cardDashboardItem2" Name="Totalizadores" ShowCaption="false" ParentContainer="dashboardTabPage1" DataSource="dashboardSqlDataSource1" DataMember="RecebimentosCartao"><InteractivityOptions MasterFilterMode="Single" /><DataItems><Measure DataMember="valorBruto" Name="Valor total de vendas" DefaultId="DataItem0"><NumericFormat FormatType="Currency" Unit="Ones" IncludeGroupSeparator="true" CurrencyCultureName="pt-BR" /></Measure><Dimension DataMember="dataVencimento" DateTimeGroupInterval="DayMonthYear" DefaultId="DataItem1" /><Measure DataMember="valorLiquido" Name="Valor Líquido de vendas" DefaultId="DataItem2"><NumericFormat FormatType="Currency" Unit="Ones" IncludeGroupSeparator="true" CurrencyCultureName="pt-BR" /></Measure><Measure DataMember="valorTaxas" Name="Valor total de taxas" DefaultId="DataItem3"><NumericFormat FormatType="Currency" Unit="Ones" IncludeGroupSeparator="true" CurrencyCultureName="pt-BR" /></Measure><Measure DataMember="valorBruto" DefaultId="DataItem4" /><Measure DataMember="valorBruto" Name="Valor/Percentual de Juros" DefaultId="DataItem5"><Calculation><RunningTotal /></Calculation><WindowDefinition><CardWindowDefinition /></WindowDefinition></Measure></DataItems><HiddenDimensions><Dimension DefaultId="DataItem1" /></HiddenDimensions><HiddenMeasures><Measure DefaultId="DataItem4" /></HiddenMeasures><Card><ActualValue DefaultId="DataItem0" /><AbsoluteVariationNumericFormat /><PercentVariationNumericFormat /><PercentOfTargetNumericFormat /><LayoutTemplate MinWidth="270" MaxWidth="270" Type="Centered"><MainValue Visible="true" ValueType="Title" DimensionIndex="0" /><SubValue Visible="true" ValueType="Subtitle" DimensionIndex="0" /><BottomValue Visible="true" ValueType="ActualValue" DimensionIndex="0" /><BottomSubValue1 Visible="true" ValueType="AbsoluteVariation" DimensionIndex="0" /><BottomSubValue2 Visible="true" ValueType="PercentVariation" DimensionIndex="0" /><DeltaIndicator Visible="true" /><Sparkline Visible="true" /></LayoutTemplate></Card><Card Name="Valor Líquido de vendas"><ActualValue DefaultId="DataItem2" /><TargetValue DefaultId="DataItem5" /><CardDeltaOptions ResultIndicationMode="WarningIfGreater" /><AbsoluteVariationNumericFormat /><PercentVariationNumericFormat /><PercentOfTargetNumericFormat /><SparklineOptions ViewType="WinLoss" /><LayoutTemplate MinWidth="270" MaxWidth="270" Type="Centered"><MainValue Visible="true" ValueType="Title" DimensionIndex="0" /><SubValue Visible="true" ValueType="Subtitle" DimensionIndex="0" /><BottomValue Visible="true" ValueType="ActualValue" DimensionIndex="0" /><BottomSubValue1 Visible="true" ValueType="AbsoluteVariation" DimensionIndex="0" /><BottomSubValue2 Visible="true" ValueType="PercentVariation" DimensionIndex="0" /><DeltaIndicator Visible="true" /><Sparkline Visible="true" /></LayoutTemplate></Card><Card><ActualValue DefaultId="DataItem3" /><AbsoluteVariationNumericFormat /><PercentVariationNumericFormat /><PercentOfTargetNumericFormat /><LayoutTemplate MinWidth="270" MaxWidth="270" Type="Centered"><MainValue Visible="true" ValueType="Title" DimensionIndex="0" /><SubValue Visible="true" ValueType="Subtitle" DimensionIndex="0" /><BottomValue Visible="true" ValueType="ActualValue" DimensionIndex="0" /><BottomSubValue1 Visible="true" ValueType="AbsoluteVariation" DimensionIndex="0" /><BottomSubValue2 Visible="true" ValueType="PercentVariation" DimensionIndex="0" /><DeltaIndicator Visible="true" /><Sparkline Visible="true" /></LayoutTemplate></Card></Card><DateFilter ComponentName="dateFilterDashboardItem1" Name="Filtro por Data de Vencimento" ParentContainer="dashboardTabPage1" DataSource="dashboardSqlDataSource1" DataMember="RecebimentosCartao" ArrangementMode="Horizontal" SelectedDateTimePeriodIndex="Hoje"><DataItems><Dimension DataMember="dataVencimento" DateTimeGroupInterval="DayMonthYear" DefaultId="DataItem0" /></DataItems><Dimension DefaultId="DataItem0" /><DateTimePeriods><DateTimePeriod Name="Este Ano"><StartLimit><FlowDateTimePeriodLimit /></StartLimit><EndLimit><FlowDateTimePeriodLimit Offset="1" /></EndLimit></DateTimePeriod><DateTimePeriod Name="Últimos 30 Dias"><StartLimit><FlowDateTimePeriodLimit Interval="Day" Offset="-29" /></StartLimit><EndLimit><FlowDateTimePeriodLimit Interval="Day" Offset="1" /></EndLimit></DateTimePeriod><DateTimePeriod Name="Últimos 7 Dias"><StartLimit><FlowDateTimePeriodLimit Interval="Day" Offset="-6" /></StartLimit><EndLimit><FlowDateTimePeriodLimit Interval="Day" Offset="1" /></EndLimit></DateTimePeriod><DateTimePeriod Name="Ontem"><StartLimit><FlowDateTimePeriodLimit Interval="Day" Offset="-1" /></StartLimit><EndLimit><FlowDateTimePeriodLimit Interval="Day" /></EndLimit></DateTimePeriod><DateTimePeriod Name="Hoje"><StartLimit><FlowDateTimePeriodLimit Interval="Day" /></StartLimit><EndLimit><FlowDateTimePeriodLimit Interval="Day" Offset="1" /></EndLimit></DateTimePeriod><DateTimePeriod Name="Este Mês"><StartLimit><FlowDateTimePeriodLimit Interval="Month" /></StartLimit><EndLimit><FlowDateTimePeriodLimit Interval="Month" Offset="1" /></EndLimit></DateTimePeriod><DateTimePeriod Name="Próximos 7 Dias"><StartLimit><FlowDateTimePeriodLimit Interval="Day" Offset="1" /></StartLimit><EndLimit><FlowDateTimePeriodLimit Interval="Day" Offset="8" /></EndLimit></DateTimePeriod><DateTimePeriod Name="Próximos 30 Dias"><StartLimit><FlowDateTimePeriodLimit Interval="Day" Offset="1" /></StartLimit><EndLimit><FlowDateTimePeriodLimit Interval="Day" Offset="31" /></EndLimit></DateTimePeriod><DateTimePeriod Name="Próximo Mês"><StartLimit><FlowDateTimePeriodLimit Interval="Month" Offset="1" /></StartLimit><EndLimit><FlowDateTimePeriodLimit Interval="Month" Offset="2" /></EndLimit></DateTimePeriod></DateTimePeriods></DateFilter><Chart ComponentName="chartDashboardItem1" Name="Valor líquido por produto/serviço" ParentContainer="dashboardTabPage1" DataSource="dashboardSqlDataSource1" DataMember="RecebimentosCartao" Rotated="true"><InteractivityOptions MasterFilterMode="Single" /><DataItems><Dimension DataMember="dscProduto" Name="Produto" DefaultId="DataItem2" /><Dimension DataMember="dscAdministradora" Name="Administradora" SortOrder="Descending" SortByMeasure="DataItem1" DefaultId="DataItem0" /><Measure DataMember="valorLiquido" Name="Valor Líquido" DefaultId="DataItem1"><NumericFormat FormatType="Currency" IncludeGroupSeparator="true" CurrencyCultureName="pt-BR" /></Measure></DataItems><SeriesDimensions><SeriesDimension DefaultId="DataItem2" /></SeriesDimensions><Arguments><Argument DefaultId="DataItem0" /></Arguments><Panes><Pane Name="Pane 1"><AxisY Logarithmic="true"><NumericFormat FormatType="Currency" IncludeGroupSeparator="true" CurrencyCultureName="pt-BR" /></AxisY><Series><Simple><Value DefaultId="DataItem1" /></Simple></Series></Pane></Panes><ChartLegend OutsidePosition="BottomRightHorizontal" /><AxisX EnableZooming="true" /></Chart><Chart ComponentName="chartDashboardItem2" Name="Detalhamento de Vendas" ParentContainer="dashboardTabPage2" DataSource="dashboardSqlDataSource1" DataMember="RecebimentosCartao" FilterString="[DataItem2] &gt;= LocalDateTimeLastYear()"><ColoringOptions UseGlobalColors="false" /><InteractivityOptions IgnoreMasterFilters="true" /><DataItems><Dimension DataMember="dataVencimento" Name="Data de Vencimento" DateTimeGroupInterval="MonthYear" DefaultId="DataItem2" /><Measure DataMember="valorLiquido" Name="Valor Líquido" DefaultId="DataItem0" /><Measure DataMember="valorBruto" Name="Valor Bruto" DefaultId="DataItem3" /><Measure DataMember="valorTaxas" Name="Valor de Taxas" DefaultId="DataItem1" /></DataItems><Arguments><Argument DefaultId="DataItem2" /></Arguments><ColorScheme><Entry DataSource="dashboardSqlDataSource1" DataMember="RecebimentosCartao" Color="-13395610"><MeasureKey><Definition DataMember="valorLiquido" /></MeasureKey></Entry><Entry DataSource="dashboardSqlDataSource1" DataMember="RecebimentosCartao" Color="-13395508"><MeasureKey><Definition DataMember="valorBruto" /></MeasureKey></Entry><Entry DataSource="dashboardSqlDataSource1" DataMember="RecebimentosCartao" Color="-39373"><MeasureKey><Definition DataMember="valorTaxas" /></MeasureKey></Entry></ColorScheme><Panes><Pane Name="Pane 1"><AxisY Logarithmic="true" /><Series><Simple SeriesType="StackedBar"><Value DefaultId="DataItem0" /></Simple><Simple SeriesType="StackedBar"><Value DefaultId="DataItem3" /></Simple><Simple SeriesType="Area"><Value DefaultId="DataItem1" /><PointLabelOptions OverlappingMode="Reposition" /></Simple></Series></Pane></Panes><ChartLegend Visible="false" /></Chart><Chart ComponentName="chartDashboardItem3" Name="Vendas a Receber" ParentContainer="dashboardTabPage2" DataSource="dashboardSqlDataSource1" DataMember="RecebimentosCartao" FilterString="[DataItem1] &gt; LocalDateTimeThisMonth()"><ColoringOptions UseGlobalColors="false" /><InteractivityOptions IgnoreMasterFilters="true" /><DataItems><Dimension DataMember="dataVencimento" DateTimeGroupInterval="MonthYear" DefaultId="DataItem1" /><Measure DataMember="valorLiquido" Name="Valor Líquido" DefaultId="DataItem0" /><Measure DataMember="valorBruto" Name="Valor Bruto" DefaultId="DataItem2" /><Measure DataMember="valorTaxas" Name="Valor de Taxas" DefaultId="DataItem3" /></DataItems><Arguments><Argument DefaultId="DataItem1" /></Arguments><ColorScheme><Entry DataSource="dashboardSqlDataSource1" DataMember="RecebimentosCartao" Color="-13395508"><MeasureKey><Definition DataMember="valorBruto" /></MeasureKey></Entry><Entry DataSource="dashboardSqlDataSource1" DataMember="RecebimentosCartao" Color="-13395610"><MeasureKey><Definition DataMember="valorLiquido" /></MeasureKey></Entry><Entry DataSource="dashboardSqlDataSource1" DataMember="RecebimentosCartao" Color="-39373"><MeasureKey><Definition DataMember="valorTaxas" /></MeasureKey></Entry></ColorScheme><Panes><Pane Name="Pane 1"><Series><Simple SeriesType="StackedSplineArea"><Value DefaultId="DataItem0" /></Simple><Simple SeriesType="StackedSplineArea"><Value DefaultId="DataItem2" /></Simple><Simple SeriesType="StackedSplineArea"><Value DefaultId="DataItem3" /></Simple></Series></Pane></Panes></Chart><TabContainer ComponentName="tabContainerDashboardItem1" Name="Tab Container 1"><Pages><Page ComponentName="dashboardTabPage1" Name="Resumo Geral" /><Page ComponentName="dashboardTabPage2" Name="Detalhamentos"><InteractivityOptions IgnoreMasterFilters="true" /></Page></Pages></TabContainer><ComboBox ComponentName="comboBoxDashboardItem1" Name="Combo Box 1" ShowCaption="false" ParentContainer="dashboardTabPage2" DataSource="dashboardSqlDataSource1" DataMember="RecebimentosCartao"><DataItems><Dimension DataMember="dataVencimento" Name="Data de vencimento" SortOrder="Descending" DefaultId="DataItem0" /></DataItems><FilterDimensions><Dimension DefaultId="DataItem0" /></FilterDimensions></ComboBox></Items><LayoutTree><LayoutGroup Orientation="Vertical" Weight="100"><LayoutTabContainer DashboardItem="tabContainerDashboardItem1" Weight="100"><LayoutTabPage DashboardItem="dashboardTabPage1" Weight="100"><LayoutItem DashboardItem="cardDashboardItem2" Weight="26.305220883534137" /><LayoutGroup Orientation="Vertical" Weight="73.694779116465867"><LayoutItem DashboardItem="dateFilterDashboardItem1" Weight="15.535248041775457" /><LayoutItem DashboardItem="chartDashboardItem1" Weight="84.464751958224539" /></LayoutGroup></LayoutTabPage><LayoutTabPage Orientation="Vertical" DashboardItem="dashboardTabPage2" Weight="100"><LayoutItem DashboardItem="comboBoxDashboardItem1" Weight="4.1775456919060057" /><LayoutItem DashboardItem="chartDashboardItem2" Weight="46.083550913838117" /><LayoutItem DashboardItem="chartDashboardItem3" Weight="49.738903394255871" /></LayoutTabPage></LayoutTabContainer></LayoutGroup></LayoutTree></Dashboard>')
GO
INSERT [dbo].[Dashboard] ([id], [descricao], [idDashboardGrupo], [xml]) VALUES (8, N'Visão geral de Ordens de Serviço', 3, N'<Dashboard CurrencyCulture="pt-BR"><Title Text="Visão geral de Ordens de Serviço" /><DataSources><SqlDataSource Name="SQL Data Source 1" ComponentName="dashboardSqlDataSource1"><Connection Name="localhost_TryNFeOnline_Connection" ProviderKey="MSSqlServer"><Parameters><Parameter Name="server" Value="DANIELJORIS\SQLEXPRESS2017" /><Parameter Name="database" Value="TryNFeOnlineDashboard" /><Parameter Name="useIntegratedSecurity" Value="True" /><Parameter Name="read only" Value="1" /><Parameter Name="generateConnectionHelper" Value="false" /><Parameter Name="userid" Value="" /><Parameter Name="password" Value="" /></Parameters></Connection><Query Type="CustomSqlQuery" Name="ProdutosOS"><Parameter Name="idEmitente" Type="DevExpress.DataAccess.Expression">(System.Int32)(?idEmitente)</Parameter><Parameter Name="idEmitenteLogado" Type="DevExpress.DataAccess.Expression">(System.Int32)(?idEmitenteLogado)</Parameter><Sql>select
cast(os.dataHora as date) as dataOs,
sum(coalesce(osp.total, 0)) as totalOS,
sum(coalesce(osp.custoTotal, 0)) as totalCusto,
sum(osp.quantidade) as qtdProduto,
cast(osp.idProduto as varchar(10)) + 
(case when pc.sequencia &gt; 0 then ''/'' + cast(pc.sequencia as varchar(20)) else '''' end) + 
'' - '' + osp.descricaoProduto as dscProduto
from OrdemServicoProduto osp
inner join OrdemServico os on os.id = osp.idOrdemServico
left join ProdutoClassificacao pc on pc.id = osp.idProdutoClassificacao
where (((os.idEmitente = @idEmitente) and (@idEmitente &gt; 0)) or ((os.idEmitente = @idEmitenteLogado) and (@idEmitente = 0)))
and (coalesce(os.idnCancelada, 0) = 0)
group by cast(os.dataHora as date), 
cast(osp.idProduto as varchar(10)) + 
(case when pc.sequencia &gt; 0 then ''/'' + cast(pc.sequencia as varchar(20)) else '''' end) + 
'' - '' + osp.descricaoProduto</Sql></Query><Query Type="CustomSqlQuery" Name="OrdensDeServicoGeradas"><Parameter Name="idEmitente" Type="DevExpress.DataAccess.Expression">(System.Int32)(?idEmitente)</Parameter><Parameter Name="idEmitenteLogado" Type="DevExpress.DataAccess.Expression">(System.Int32)(?idEmitenteLogado)</Parameter><Sql>select 
distinct os.id,
cast(os.dataHora as date) as dataOs,
coalesce(os.totalServicos, 0) as valorTotalServicos,
coalesce(os.totalProdutos, 0) as valorTotalProdutos,
sum(coalesce(osp.quantidade,0)) as quantidadeTotalProdutos,
sum(coalesce(oss.quantidade, 0)) as quantidadeTotalServicos,
os.status as statusOs,
os.totalGeralLiquido as totalLiquido,
cast(ose.dataHora as date) as dataHoraEncerramento
from OrdemServico os
left join OrdemServicoProduto osp on os.id = osp.idOrdemServico
left join OrdemServicoServico oss on os.id = oss.idOrdemServico
left join OrdemServicoEncerrada ose on ose.idOrdemServico = os.id
where (((os.idEmitente = @idEmitente) and (@idEmitente &gt; 0)) or ((os.idEmitente = @idEmitenteLogado) and (@idEmitente = 0)))
and (coalesce(os.idnCancelada, 0) = 0)
group by os.id, cast(os.dataHora as date) ,
os.totalServicos,
os.totalProdutos,
os.status,
os.totalGeralLiquido,
ose.dataHora
</Sql></Query><Query Type="CustomSqlQuery" Name="ServicosOS"><Parameter Name="idEmitente" Type="DevExpress.DataAccess.Expression">(System.Int32)(?idEmitente)</Parameter><Parameter Name="idEmitenteLogado" Type="DevExpress.DataAccess.Expression">(System.Int32)(?idEmitenteLogado)</Parameter><Sql>select 
cast(os.dataHora as date) as dataOs,
sum(coalesce(oss.total, 0)) as TotalOS,
sum(coalesce(oss.custoTotal, 0)) as TotalCusto,
sum(oss.quantidade) as qtdServico,
cast(oss.idServico as varchar(10)) + '' - '' + oss.descricaoServico as dscServico
from OrdemServicoServico oss
inner join OrdemServico os on os.id = oss.idOrdemServico
where (((os.idEmitente = @idEmitente) and (@idEmitente &gt; 0)) or ((os.idEmitente = @idEmitenteLogado) and (@idEmitente = 0)))
and (coalesce(os.idnCancelada, 0) = 0)
group by cast(os.dataHora as date), 
cast(oss.idServico as varchar(10)) + '' - '' + oss.descricaoServico
</Sql></Query><Query Type="CustomSqlQuery" Name="Emitentes"><Sql>select 0 as id, ''Nenhum emitente selecionado'' as fantasia,''Nenhum emitente selecionado'' as razaosocial
UNION ALL
select id, fantasia, razaosocial from emitente where (coalesce(idnBaixa, 0) = 0)
</Sql></Query><Query Type="CustomSqlQuery" Name="OrdemServicoPorTecnico"><Parameter Name="idEmitente" Type="DevExpress.DataAccess.Expression">(System.Int32)(?idEmitente)</Parameter><Parameter Name="idEmitenteLogado" Type="DevExpress.DataAccess.Expression">(System.Int32)(?idEmitenteLogado)</Parameter><Sql>select 
cast(os.dataHora as date) as dataOs,
oss.idOrdemServico,
(case when tecnico.razaosocial is not null then tecnico.razaosocial else ''Não definido'' end) as tecnicoServico
from OrdemServicoServico oss
inner join OrdemServico os on os.id = oss.idOrdemServico
left join parceiro tecnico on tecnico.id = oss.idTecnico
where (((os.idEmitente = @idEmitente) and (@idEmitente &gt; 0)) or ((os.idEmitente = @idEmitenteLogado) and (@idEmitente = 0)))
and (coalesce(os.idnCancelada, 0) = 0)
</Sql></Query><ResultSchema><DataSet Name="SQL Data Source 1"><View Name="ProdutosOS"><Field Name="dataOs" Type="DateTime" /><Field Name="totalOS" Type="Decimal" /><Field Name="totalCusto" Type="Decimal" /><Field Name="qtdProduto" Type="Decimal" /><Field Name="dscProduto" Type="String" /></View><View Name="OrdensDeServicoGeradas"><Field Name="id" Type="Int32" /><Field Name="dataOs" Type="DateTime" /><Field Name="valorTotalServicos" Type="Decimal" /><Field Name="valorTotalProdutos" Type="Decimal" /><Field Name="quantidadeTotalProdutos" Type="Decimal" /><Field Name="quantidadeTotalServicos" Type="Decimal" /><Field Name="statusOs" Type="String" /><Field Name="totalLiquido" Type="Decimal" /><Field Name="dataHoraEncerramento" Type="DateTime" /></View><View Name="ServicosOS"><Field Name="dataOs" Type="DateTime" /><Field Name="TotalOS" Type="Decimal" /><Field Name="TotalCusto" Type="Decimal" /><Field Name="qtdServico" Type="Decimal" /><Field Name="dscServico" Type="String" /></View><View Name="Emitentes"><Field Name="id" Type="Int32" /><Field Name="fantasia" Type="String" /><Field Name="razaosocial" Type="String" /></View><View Name="OrdemServicoPorTecnico"><Field Name="dataOs" Type="DateTime" /><Field Name="idOrdemServico" Type="Int32" /><Field Name="tecnicoServico" Type="String" /></View></DataSet></ResultSchema><ConnectionOptions CloseConnection="true" /><CalculatedFields><CalculatedField Name="OSAbertasHoje" Expression="CountNotNull(Iif([statusOs] == ''Aberta'' &amp;&amp; [dataOs] == Today(), [statusOs], null))" DataType="Auto" DataMember="OrdensDeServicoGeradas" /><CalculatedField Name="OSEncerradasHoje" Expression="CountNotNull(Iif([statusOs] == ''Encerrada'' &amp;&amp; [dataHoraEncerramento] == Today(),[statusOs], null))" DataType="Auto" DataMember="OrdensDeServicoGeradas" /><CalculatedField Name="OSAbertas" Expression="CountNotNull(Iif([statusOs] == ''Aberta'', [statusOs], null))" DataType="Auto" DataMember="OrdensDeServicoGeradas" /><CalculatedField Name="OSEncerradas" Expression="CountNotNull(Iif([statusOs] == ''Encerrada'', [statusOs] ,null))" DataType="Auto" DataMember="OrdensDeServicoGeradas" /></CalculatedFields></SqlDataSource></DataSources><Parameters><Parameter Name="idEmitente" Type="System.Int32, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Value="0" Description="Filial/empresa"><DynamicListLookUpSettings DataSourceName="dashboardSqlDataSource1" DataMember="Emitentes" ValueMember="id" DisplayMember="fantasia" /></Parameter><Parameter Name="idEmitenteLogado" Type="System.Int32, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Value="0" Visible="false" /></Parameters><Items><RangeFilter ComponentName="rangeFilterDashboardItem1" Name="Ordens de Serviço por Período" ShowCaption="true" ParentContainer="dashboardItemGroup1" DataSource="dashboardSqlDataSource1" DataMember="OrdensDeServicoGeradas" IsMasterFilterCrossDataSource="true"><DataItems><Measure DataMember="totalLiquido" DefaultId="DataItem1" /><Dimension DataMember="dataOs" DateTimeGroupInterval="MonthYear" DefaultId="DataItem0" /><Measure DataMember="valorTotalProdutos" DefaultId="DataItem2" /><Measure DataMember="valorTotalServicos" DefaultId="DataItem3" /></DataItems><Argument DefaultId="DataItem0" /><Series><Simple><Value DefaultId="DataItem1" /></Simple><Simple SeriesType="Line"><Value DefaultId="DataItem2" /></Simple><Simple SeriesType="Line"><Value DefaultId="DataItem3" /></Simple></Series><DateTimePeriods><DateTimePeriod Name="Último Ano"><StartLimit><FlowDateTimePeriodLimit Offset="-1" /></StartLimit><EndLimit><FlowDateTimePeriodLimit /></EndLimit></DateTimePeriod><DateTimePeriod Name="Este Ano"><StartLimit><FlowDateTimePeriodLimit /></StartLimit><EndLimit><FlowDateTimePeriodLimit Offset="1" /></EndLimit></DateTimePeriod><DateTimePeriod Name="Últimos 3 Meses"><StartLimit><FlowDateTimePeriodLimit Interval="Month" Offset="-3" /></StartLimit><EndLimit><FlowDateTimePeriodLimit Interval="Month" /></EndLimit></DateTimePeriod><DateTimePeriod Name="Último Mês"><StartLimit><FlowDateTimePeriodLimit Interval="Month" Offset="-1" /></StartLimit><EndLimit><FlowDateTimePeriodLimit Interval="Month" /></EndLimit></DateTimePeriod><DateTimePeriod Name="Este Mês"><StartLimit><FlowDateTimePeriodLimit Interval="Month" /></StartLimit><EndLimit><FlowDateTimePeriodLimit Interval="Month" Offset="1" /></EndLimit></DateTimePeriod></DateTimePeriods></RangeFilter><Gauge ComponentName="gaugeDashboardItem1" Name="Geral" ParentContainer="dashboardItemGroup3" DataSource="dashboardSqlDataSource1" DataMember="OrdensDeServicoGeradas" ContentLineCount="1" ViewType="CircularThreeFourth"><DataItems><Dimension DataMember="dataOs" DateTimeGroupInterval="MonthYear" DefaultId="DataItem2" /><Measure DataMember="OSEncerradas" Name="Encerradas" DefaultId="DataItem1" /><Measure DataMember="OSAbertas" Name="Abertas" DefaultId="DataItem0" /></DataItems><HiddenDimensions><Dimension DefaultId="DataItem2" /></HiddenDimensions><GaugeElement><ActualValue DefaultId="DataItem0" /><AbsoluteVariationNumericFormat /><PercentVariationNumericFormat /><PercentOfTargetNumericFormat /></GaugeElement><GaugeElement><ActualValue DefaultId="DataItem1" /><AbsoluteVariationNumericFormat /><PercentVariationNumericFormat /><PercentOfTargetNumericFormat /></GaugeElement></Gauge><Pie ComponentName="pieDashboardItem4" Name="Top 5 Produtos mais utilizados" ParentContainer="dashboardItemGroup2" DataSource="dashboardSqlDataSource1" DataMember="ProdutosOS" ShowPieCaptions="false"><DataItems><Dimension DataMember="dscProduto" SortOrder="Descending" SortByMeasure="DataItem2" TopNEnabled="true" TopNMeasure="DataItem2" DefaultId="DataItem0" /><Dimension DataMember="dataOs" SortOrder="Descending" DateTimeGroupInterval="MonthYear" TopNEnabled="true" TopNMeasure="DataItem2" DefaultId="DataItem1" /><Measure DataMember="qtdProduto" DefaultId="DataItem2"><NumericFormat FormatType="Number" Unit="Ones" IncludeGroupSeparator="true" /></Measure></DataItems><HiddenDimensions><Dimension DefaultId="DataItem1" /></HiddenDimensions><Arguments><Argument DefaultId="DataItem0" /></Arguments><Values><Value DefaultId="DataItem2" /></Values></Pie><Pie ComponentName="pieDashboardItem2" Name="Top 5 Serviços mais realizados" ParentContainer="dashboardItemGroup2" DataSource="dashboardSqlDataSource1" DataMember="ServicosOS" ShowPieCaptions="false"><DataItems><Dimension DataMember="dscServico" SortOrder="Descending" SortByMeasure="DataItem2" TopNEnabled="true" TopNMeasure="DataItem2" DefaultId="DataItem0" /><Measure DataMember="qtdServico" DefaultId="DataItem2"><NumericFormat FormatType="Number" Unit="Ones" IncludeGroupSeparator="true" /></Measure><Dimension DataMember="dataOs" DateTimeGroupInterval="MonthYear" DefaultId="DataItem1" /></DataItems><HiddenDimensions><Dimension DefaultId="DataItem1" /></HiddenDimensions><Arguments><Argument DefaultId="DataItem0" /></Arguments><Values><Value DefaultId="DataItem2" /></Values></Pie><Gauge ComponentName="gaugeDashboardItem3" Name="Hoje" ParentContainer="dashboardItemGroup3" DataSource="dashboardSqlDataSource1" DataMember="OrdensDeServicoGeradas" ContentLineCount="1" ViewType="CircularThreeFourth"><DataItems><Dimension DataMember="dataOs" DateTimeGroupInterval="DayMonthYear" DefaultId="DataItem0" /><Dimension DataMember="dataHoraEncerramento" DateTimeGroupInterval="DayMonthYear" DefaultId="DataItem2" /><Measure DataMember="OSAbertasHoje" Name="Abertas" DefaultId="DataItem3" /><Measure DataMember="OSEncerradasHoje" Name="Encerradas" DefaultId="DataItem1" /></DataItems><HiddenDimensions><Dimension DefaultId="DataItem0" /><Dimension DefaultId="DataItem2" /></HiddenDimensions><GaugeElement><ActualValue DefaultId="DataItem3" /><AbsoluteVariationNumericFormat /><PercentVariationNumericFormat /><PercentOfTargetNumericFormat /></GaugeElement><GaugeElement><ActualValue DefaultId="DataItem1" /><AbsoluteVariationNumericFormat /><PercentVariationNumericFormat /><PercentOfTargetNumericFormat /></GaugeElement></Gauge><TextBox ComponentName="textBoxDashboardItem1" Name="Dica" ParentContainer="dashboardItemGroup1"><Text>{\rtf1\deff0 \stshfdbch2\stshfloch1\stshfhich1\stshfbi3{\fonttbl{\f0 Calibri;}{\fhiminor\f1 Calibri;}{\fdbminor\f2 Times New Roman;}{\fbiminor\f3 Times New Roman;}{\f4 Segoe UI Semilight;}}{\colortbl ;\red0\green0\blue255 ;\red95\green139\blue149 ;\red51\green153\blue102 ;\red153\green51\blue153 ;\red51\green153\blue204 ;\red227\green166\blue83 ;\red63\green63\blue63 ;}{\*\defchp \hich\af1\loch\af1\dbch\af2\fcs1\af3\fs24}{\stylesheet {\ql\hich\af1\loch\af1\dbch\af2\fcs1\af3\fs24 Normal;}{\*\cs1\hich\af1\loch\af1\dbch\af2\fcs1\af3\fs24 Default Paragraph Font;}{\*\cs2\sbasedon1\hich\af1\loch\af1\dbch\af2\fcs1\af3\fs24 Line Number;}{\*\cs3\hich\af1\loch\af1\dbch\af2\fcs1\af3\fs24\ul\cf1 Hyperlink;}{\*\ts4\tsrowd\hich\af1\loch\af1\dbch\af2\fcs1\af3\fs24\ql\tscellpaddfl3\tscellpaddl108\tscellpaddfb3\tscellpaddfr3\tscellpaddr108\tscellpaddft3\tsvertalt\cltxlrtb Normal Table;}{\*\ts5\tsrowd\sbasedon4\hich\af1\loch\af1\dbch\af2\fcs1\af3\fs24\ql\trbrdrt\brdrs\brdrw10\trbrdrl\brdrs\brdrw10\trbrdrb\brdrs\brdrw10\trbrdrr\brdrs\brdrw10\trbrdrh\brdrs\brdrw10\trbrdrv\brdrs\brdrw10\tscellpaddfl3\tscellpaddl108\tscellpaddfr3\tscellpaddr108\tsvertalt\cltxlrtb Table Simple 1;}}{\*\listtable {\list\listtemplateid-1\listhybrid{\listlevel\levelnfc23\levelnfcn23\leveljc0\leveljcn0\levelfollow0\levelstartat1{\leveltext\leveltemplateid1867360704 \''01\u183\''b7;}{\levelnumbers;}\levellegal0\levelnorestart0\fi-360\li720\lin720}{\listlevel\levelnfc23\levelnfcn23\leveljc0\leveljcn0\levelfollow0\levelstartat1{\leveltext\leveltemplateid795475697 \''01o;}{\levelnumbers;}\levellegal0\levelnorestart0\fi-360\li1440\lin1440}{\listlevel\levelnfc23\levelnfcn23\leveljc0\leveljcn0\levelfollow0\levelstartat1{\leveltext\leveltemplateid264123472 \''01\u183\''b7;}{\levelnumbers;}\levellegal0\levelnorestart0\fi-360\li2160\lin2160}{\listlevel\levelnfc23\levelnfcn23\leveljc0\leveljcn0\levelfollow0\levelstartat1{\leveltext\leveltemplateid2143575664 \''01o;}{\levelnumbers;}\levellegal0\levelnorestart0\fi-360\li2880\lin2880}{\listlevel\levelnfc23\levelnfcn23\leveljc0\leveljcn0\levelfollow0\levelstartat1{\leveltext\leveltemplateid637463355 \''01\u183\''b7;}{\levelnumbers;}\levellegal0\levelnorestart0\fi-360\li3600\lin3600}{\listlevel\levelnfc23\levelnfcn23\leveljc0\leveljcn0\levelfollow0\levelstartat1{\leveltext\leveltemplateid1276499568 \''01o;}{\levelnumbers;}\levellegal0\levelnorestart0\fi-360\li4320\lin4320}{\listlevel\levelnfc23\levelnfcn23\leveljc0\leveljcn0\levelfollow0\levelstartat1{\leveltext\leveltemplateid379704411 \''01\u183\''b7;}{\levelnumbers;}\levellegal0\levelnorestart0\fi-360\li5040\lin5040}{\listlevel\levelnfc23\levelnfcn23\leveljc0\leveljcn0\levelfollow0\levelstartat1{\leveltext\leveltemplateid1390076276 \''01o;}{\levelnumbers;}\levellegal0\levelnorestart0\fi-360\li5760\lin5760}{\listlevel\levelnfc23\levelnfcn23\leveljc0\leveljcn0\levelfollow0\levelstartat1{\leveltext\leveltemplateid153274097 \''01\u183\''b7;}{\levelnumbers;}\levellegal0\levelnorestart0\fi-360\li6480\lin6480}{\listname ;}\listid792322531}}{\*\listoverridetable {\listoverride\listid792322531\listoverridecount0\ls1}}\nouicompat\splytwnine\htmautsp\alntblind\expshrtn\spltpgpar\nogrowautofit\sectd\pard\plain\ql{\lang1046\langfe1046\fcs1\af4\ltrch\fcs0\hich\af4\dbch\af2\loch\f4\fs20\cf0 Ao lado \u233\''e9 poss\u237\''edvel observar um gr\u225\''e1fico onde voc\u234\''ea pode estar interagindo, filtrando por per\u237\''edodos espec\u237\''edficos. As altera\u231\''e7\u245\''f5es no filtro de per\u237\''edodo ir\u227\''e3o impactar na quantidade de informa\u231\''e7\u245\''f5es exibidas em alguns dos gr\u225\''e1ficos acima.}\lang1046\langfe1046\fcs1\af4\ltrch\fcs0\hich\af4\dbch\af2\loch\f4\fs22\par\pard\plain\ql\li720\lin720\lang1046\langfe1046\fcs1\af4\ltrch\fcs0\hich\af4\dbch\af2\loch\f4\fs4\b\cf2\par{\listtext\pard\plain \u183\''b7\tab }\pard\plain\ilvl0\ls1\ql\fi-360\li720\lin720{\lang1046\langfe1046\fcs1\af4\ltrch\fcs0\hich\af4\dbch\af2\loch\f4\fs24\b\cf3 Cor }{\lang1046\langfe1046\fcs1\af4\ltrch\fcs0\hich\af4\dbch\af2\loch\f4\fs24\b\cf3 verde }{\lang1046\langfe1046\fcs1\af4\ltrch\fcs0\hich\af4\dbch\af2\loch\f4\fs24\b\cf3 representa o total l\u237\''edquido das OS''s;}\lang1046\langfe1046\fcs1\af4\ltrch\fcs0\hich\af4\dbch\af2\loch\f4\fs24\b\cf3\par{\listtext\pard\plain \u183\''b7\tab }\pard\plain\ilvl0\ls1\ql\fi-360\li720\lin720{\lang1046\langfe1046\fcs1\af4\ltrch\fcs0\hich\af4\dbch\af2\loch\f4\fs24\b\cf4 Cor }{\lang1046\langfe1046\fcs1\af4\ltrch\fcs0\hich\af4\dbch\af2\loch\f4\fs24\b\cf4 roxa }{\lang1046\langfe1046\fcs1\af4\ltrch\fcs0\hich\af4\dbch\af2\loch\f4\fs24\b\cf4 representa o valor total de Servi\u231\''e7os;}\lang1046\langfe1046\fcs1\af4\ltrch\fcs0\hich\af4\dbch\af2\loch\f4\fs24\b\cf4\par{\listtext\pard\plain \u183\''b7\tab }\pard\plain\ilvl0\ls1\ql\fi-360\li720\lin720{\lang1046\langfe1046\fcs1\af4\ltrch\fcs0\hich\af4\dbch\af2\loch\f4\fs24\b\cf5 Cor }{\lang1046\langfe1046\fcs1\af4\ltrch\fcs0\hich\af4\dbch\af2\loch\f4\fs24\b\cf5 azul }{\lang1046\langfe1046\fcs1\af4\ltrch\fcs0\hich\af4\dbch\af2\loch\f4\fs24\b\cf5 representa o valor total de Produtos;}\lang1046\langfe1046\fcs1\af4\ltrch\fcs0\hich\af4\dbch\af2\loch\f4\fs24\b\cf5\par\pard\plain\ql\li720\lin720\lang1046\langfe1046\fcs1\af4\ltrch\fcs0\hich\af4\dbch\af2\loch\f4\fs4\b\cf6\par\pard\plain\ql{\lang1046\langfe1046\fcs1\af4\ltrch\fcs0\hich\af4\dbch\af2\loch\f4\fs20\i\cf7 * O filtro de per\u237\''edodo n\u227\''e3o ter\u225\''e1 impacto nas Ordens de Servi\u231\''e7o por Status.}\fcs1\af0\ltrch\fcs0\hich\af0\dbch\af2\loch\f0\fs22\par{\*\themedata 
504b03041400020008000000210201159325ae000000270100000b0000005f72656c732f2e72656c738d8f310ec2300c45af1279a76e1910424dbbb07460415c204a9d34a24da224457036068ec415c8d82206164b5ffe7efffbfd7cd5ed7d1ad98d4234
ce72a88a121859e97a63358739a9cd1edaa63ed3285276c4c1f8c8f2898d1c8694fc0131ca8126110be7c9e68d72611229cba0d10b79159a705b963b0c4b06ac99aceb3984aeaf805d1e9efe613ba58ca4a393f34436fd88f87264b2089a12879499848b
791236b70c454e00864d8dab779b0f504b0304140002000800000021027f624aeb730000007b0000001c0000007468656d652f7468656d652f7468656d654d616e616765722e786d6c0d8cc10d83300c005789fc2f4e7954554460820e61810991888392
b465b73e3a5257a89fa7d3ddeff31da633ede6c5a5c62c1eae9d05c332e7254af0f06cebe50ed338906b1b277e9050e062b491eac8c3d6dae110ebac966a970f16756b2e899a6209b8147aeb2bedd85b7bc34451c0e0f807504b03041400020008000000
21020a5d705ea60000000c010000270000007468656d652f7468656d652f5f72656c732f7468656d654d616e616765722e786d6c2e72656c738dcf3d0ec2300c05e0ab44dea95b068450d32e2c5d11178852278d687e94a408cec6c091b8021113951818
9fecf7597e3d9e6d7fb333bb524cc63b0e4d55032327fd689ce6b064b5d943dfb5279a452e1b693221b1527189c39473382026399115a9f2815c99281fadc825468d41c88bd084dbbade61fc36606db261e41087b10176be07fac7f64a1949472f174b2e
ff3881b974a980226aca1c3eb1a90a030cbb16573f756f504b0304140002000800000021026d52debf9f050000fd1b0000160000007468656d652f7468656d652f7468656d65312e786d6ced594f6fdb3614ff2a84eead2c5b729da06e113b76bb356983
c4ebd0232dd3126b4a14483aa96f437b1c30605837ec3260b71d866d015a608765d887c9d661eb807c8551b42c5136d5244d566c587cb045eaf7deeffde17b22e5939f7eb979fb4944c03e621cd3b86d39d76b1640b14f47380edad6548cafb5acdbb76e
c27511a20801098ef93a6c5ba110c9ba6d735f4e437e9d262896f7c6944550c8210bec118307524944ec7aadd6b42388630bc430426debc1788c7d0406a94a2b57de23f22b163c9df009dbf315a32ea1b0a38993fef019ef1206f621695b9267440f06e8
89b000815cc81b6daba63e16b06fddb47329222a8435c1befa2c043389d1a4ae045930cc259dbebb7663b360a8cf195681bd5eafdb730a8d0a017d5f7aebac80dd7ecbe9e45a35d4fc72557bb7e6d5dc25018da1b122b0d6e974bcb5b240a3107057045a
b5a6bb512f0bb88580b7ea4367a3db6d9605bc42a0b922d0bfb1d6749704142a24389eacc0d3cc1629ca31634aee1af12d896fe56ba180d9da4a9b2b8845d5ba8be063cafa12a0b20c058e819825680c7d89ebc268c830540c701d41ed5636e7f3d5b994
0e709fe144b4adf713280ba4c09c1c7d7772f4029c1c1d1e3f7d79fcf4c7e367cf8e9ffe6092bc0be340977cfdcda77f7df511f8f3c5d7af9f7f5e21c07581dfbefff8d79f3fab400a1df9ea8bc3df5f1ebefaf2933fbe7d6ec26f3038d4f1031c210eee
a303b04ba3d43f03051ab2738a0c42884b2230945013b227c212f2fe0c1223b083ca317cc8645b3022ef4c1f97ecdd0bd9546013f25e189590db94920e65669fee293acda7691c54f0b3a90edc8570df48df5dca726f9ac8958d8d4abb212a99ba4364e2
61806224407a8f4e1032c93dc2b814df6dec33cae95880471874203607668087c22c751747324133a38d32eba5086d3f041d4a8c049b68bf0c95150289512922a568de81530123b3d530223a740b8ad068e8de8cf9a5c07321931e2042416f8438370a3d
60b392c9f7a06c51e615b04d665119ca049e18a15b90521dba4927dd104689d96e1c873af83d3e912b16821d2acc76d072cda4639910185767fe2146e29cc5fe010e42f36249ef4c99b146102dd7e88c8c218a170f80522b8f70fca6be4eb06cec577d7d
a9af6fc8879db19e96bb7925f03fdac337e134de41699d5cb5f0ab167ed5c2df50e1ff44e32e7ab5adefd6959ea872eb3ec684ec8919415b5c75792e5d1cf5e5a41a28a1fca89084f272c15702060caa6bc0a8f8108b702f8489e4711445c033dd010709
e5f28862552a57275e2cbd56735e7e4c957028b6e9683edf289d5f73456a14709daa91aa382b5de3c645e99c39f28c7c8e57c1e7bd99cfd6622acb08c0f47d84d3ac6766721f12344aa33f572d3041bbc81799be45ae2e3d6f3c84239425ce317be634ce
1ac9d6e981d4f8d61a17e53b4be67442b78ad0bb8cd4d54e4b9dbd5ab9242e8fc08134d3ab7b16f0a344eae4693b832488db962f32874e2df665b72b56ac53ab76bb4492302e36210fe762ea56feee272eecae7b6eaaee02869bbad419c91b2de7dd93db
cb0944e3b14c7cc54c31cceed1a9406c2f1c1d802199b25d282d77e74b6a84b97c78d41703268bd3cd565bb9e8b3c5bffc2e292b0a48921066eda9a5e77a8e57d7b9116aa4d9675718ff96be342ed117efffec4bba7ce526b83152a735b9356010a4ebb4
6d5126422adb4d1262bfcfe466429149c3803c06aafe44d277e2a9b1687fd1afec859279770b42b18b03c0b06c71226408ed88ccd353b4398b76989547a629eb2db9c13c99ff0ed13e2283b4729b69082c10e60d248b85022e27ce36d5d830e8ff9b3744
6ed586e8941d4341e59e6783e2eaed5e7b0aac5dd48a733e6deb156ed7bdb33f6d13799e01e9976ce498f9a4d8fe0ea87ac416fb4db924afb5b252cc2787d2ea96ee5faaeb5d6eaf8ab4b4aa56c1a5ee53b5f837aae27f0ae1dbc7df3384df3b57f4edd5
62b6b593901a2dfdb7b698b9f537504b030414000200080000002102e9278183ee00000010020000130000005b436f6e74656e745f54797065735d2e786d6cad914d4ec3301085af62795bc54e5920849274012cf959708191334e2ce21fd993aa9c8d05
47e20a4cd382102a48486c2cd9efcdfb66c66f2fafcd66e727b1c55c5c0cad5cab5a0a0c26f62e0cad9cc9561772d3358fcf098b606b28ad1c89d2a5d6c58ce8a1a89830b06263f6407ccd834e609e60407d56d7e7dac44018a8a27d86ec9a6bb4304f24
6e76fc7cc0669c8a145707e39ed54a486972068875bd0dfd374a752428ae5c3c6574a9acd820853e8958a41f091f85f7bc89ec7a140f90e90e3cdb34f198f8f55cabdfc34eb41bad7506fb6866cf256a8959fd097a0b81e7cdff833e867d76a097ffedde
01504b010214001400020008000000210201159325ae000000270100000b00000000000000000000000000000000005f72656c732f2e72656c73504b01021400140002000800000021027f624aeb730000007b0000001c00000000000000000000000000
d70000007468656d652f7468656d652f7468656d654d616e616765722e786d6c504b01021400140002000800000021020a5d705ea60000000c0100002700000000000000000000000000840100007468656d652f7468656d652f5f72656c732f7468656d
654d616e616765722e786d6c2e72656c73504b01021400140002000800000021026d52debf9f050000fd1b000016000000000000000000000000006f0200007468656d652f7468656d652f7468656d65312e786d6c504b01021400140002000800000021
02e9278183ee000000100200001300000000000000000000000000420800005b436f6e74656e745f54797065735d2e786d6c504b050600000000050005005d010000610900000000}{\*\colorschememapping 3c3f786d6c2076657273696f6e3d22312e
302220656e636f64696e673d227574662d38223f3e3c613a636c724d617020786d6c6e733a613d22687474703a2f2f736368656d61732e6f70656e786d6c666f726d6174732e6f72672f64726177696e676d6c2f323030362f6d61696e22206267313d22
6c743122207478313d22646b3122206267323d226c743222207478323d22646b322220616363656e74313d22616363656e74312220616363656e74323d22616363656e74322220616363656e74333d22616363656e74332220616363656e74343d226163
63656e74342220616363656e74353d22616363656e74352220616363656e74363d22616363656e74362220686c696e6b3d22686c696e6b2220666f6c486c696e6b3d22666f6c486c696e6b22202f3e}}</Text></TextBox><Card ComponentName="cardDashboardItem1" Name="OS por Técnico" ParentContainer="dashboardItemGroup2" DataSource="dashboardSqlDataSource1" DataMember="OrdemServicoPorTecnico"><InteractivityOptions MasterFilterMode="Single" /><DataItems><Dimension DataMember="dataOs" DateTimeGroupInterval="MonthYear" DefaultId="DataItem0" /><Dimension DataMember="tecnicoServico" Name="Nome do Técnico" DefaultId="DataItem1" /><Measure DataMember="idOrdemServico" Name="Quantidade de Serviços" SummaryType="Count" DefaultId="DataItem2" /></DataItems><HiddenDimensions><Dimension DefaultId="DataItem0" /></HiddenDimensions><SeriesDimensions><SeriesDimension DefaultId="DataItem1" /></SeriesDimensions><Card><ActualValue DefaultId="DataItem2" /><AbsoluteVariationNumericFormat /><PercentVariationNumericFormat /><PercentOfTargetNumericFormat /><LayoutTemplate MinWidth="125" MaxWidth="150" Type="Compact"><MainValue Visible="true" ValueType="Title" DimensionIndex="0" /><SubValue Visible="true" ValueType="Subtitle" DimensionIndex="0" /><BottomValue Visible="true" ValueType="ActualValue" DimensionIndex="0" /><BottomSubValue1 Visible="true" ValueType="AbsoluteVariation" DimensionIndex="0" /><BottomSubValue2 Visible="true" ValueType="PercentVariation" DimensionIndex="0" /><DeltaIndicator Visible="true" /><Sparkline Visible="true" /></LayoutTemplate></Card></Card></Items><Groups><Group ComponentName="dashboardItemGroup1" Name="Ordens de serviço por Período" ShowCaption="false"><InteractivityOptions IsMasterFilter="true" /></Group><Group ComponentName="dashboardItemGroup3" Name="Group 1" ShowCaption="false" /><Group ComponentName="dashboardItemGroup2" Name="" ShowCaption="false"><InteractivityOptions IgnoreMasterFilters="false" /></Group></Groups><ColorScheme><Entry DataSource="dashboardSqlDataSource1" DataMember="ServicosOS" Color="-8732862"><MeasureKey><Definition DataMember="TotalOS" /></MeasureKey></Entry><Entry DataSource="dashboardSqlDataSource1" DataMember="ServicosOS" Color="-3315350"><MeasureKey><Definition DataMember="TotalCusto" /></MeasureKey></Entry><Entry DataSource="dashboardSqlDataSource1" DataMember="OrdensDeServicoGeradas" Color="-13395508"><MeasureKey><Definition DataMember="valorTotalProdutos" /></MeasureKey></Entry><Entry DataSource="dashboardSqlDataSource1" DataMember="OrdensDeServicoGeradas" Color="-6736999"><MeasureKey><Definition DataMember="valorTotalServicos" /></MeasureKey></Entry><Entry DataSource="dashboardSqlDataSource1" DataMember="OrdensDeServicoGeradas" Color="-13395610"><MeasureKey><Definition DataMember="totalLiquido" /></MeasureKey></Entry></ColorScheme><LayoutTree><LayoutGroup Orientation="Vertical" Weight="100"><LayoutGroup Weight="73.5042735042735"><LayoutGroup Orientation="Vertical" DashboardItem="dashboardItemGroup3" Weight="39.854111405835546"><LayoutItem DashboardItem="gaugeDashboardItem1" Weight="49.667774086378735" /><LayoutItem DashboardItem="gaugeDashboardItem3" Weight="50.332225913621265" /></LayoutGroup><LayoutGroup DashboardItem="dashboardItemGroup2" Weight="60.145888594164454"><LayoutGroup Orientation="Vertical" Weight="69.239250275633964"><LayoutItem DashboardItem="pieDashboardItem4" Weight="49.667774086378735" /><LayoutItem DashboardItem="pieDashboardItem2" Weight="50.332225913621265" /></LayoutGroup><LayoutItem DashboardItem="cardDashboardItem1" Weight="30.760749724366043" /></LayoutGroup></LayoutGroup><LayoutGroup DashboardItem="dashboardItemGroup1" Weight="26.495726495726494"><LayoutItem DashboardItem="rangeFilterDashboardItem1" Weight="69.628647214854112" /><LayoutItem DashboardItem="textBoxDashboardItem1" Weight="30.371352785145888" /></LayoutGroup></LayoutGroup></LayoutTree></Dashboard>')
GO
SET IDENTITY_INSERT [dbo].[Dashboard] OFF
GO
INSERT INTO VERSAO(VERSAO, VERSAOATUALIZACAO, DATAATUALIZACAO) VALUES(407, 665, GETDATE());