/****** Object:  StoredProcedure [dbo].[sp_conBalanceGeneralMineduc]    Script Date: 05/15/2014 12:50:17 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER      PROCEDURE [dbo].[sp_conBalanceGeneralMineduc]
--(
--@fechai date,
--@fechaf date,
--@codunidesde int=0,
--@codunihasta int=0
@mesi int=6,
@mesf int=6,
@periodo int = 2013,
@soloSaldo int = 1,
@soloMovimiento int = 1,
@codctaini varchar(50)=0,
@codctafin varchar(50)=99999999
,@unineg int=0
--)	
AS 

SELECT      conComprobantes.cuenta, 
			COALESCE(SUM(conComprobantes.debe),0) AS Debitos, 
			COALESCE(SUM(conComprobantes.haber),0) AS Creditos, 
			CASE WHEN SUM(debe) > SUM(haber) THEN SUM(debe) - SUM(haber) ELSE 0 END AS Deudor,
			CASE WHEN SUM(debe) < SUM(haber) THEN SUM(haber) - SUM(debe) ELSE 0 END AS Acreedor
INTO #A
FROM         conComprobantes
WHERE  (conComprobantes.periodo = @periodo)
AND    (conComprobantes.mes BETWEEN @mesi AND @mesf)
AND    (conComprobantes.cuenta BETWEEN @codctaini AND @codctafin)
AND    ((conComprobantes.uni_negocio = @unineg OR @unineg = -1) OR (conComprobantes.uni_negocio BETWEEN 9 AND 10397 AND @unineg = -2))
GROUP BY  conComprobantes.cuenta
ORDER BY  conComprobantes.cuenta

SELECT      PC.cuenta, 
			PC.descripcion, 
			COALESCE (A.Debitos, 0) AS Debitos, 
			COALESCE (A.Creditos, 0) AS Creditos, 
			COALESCE (A.Deudor, 0) AS Deudor, 
			COALESCE (A.Acreedor, 0) AS Acreedor, 
			CASE WHEN Naturaleza.marcaCtacte <= 1 AND Debitos - Creditos > 0 THEN Debitos - Creditos ELSE 0 END AS Activo, 
			CASE WHEN Naturaleza.marcaCtacte <= 1 AND Creditos - Debitos > 0 THEN Creditos - Debitos ELSE 0 END AS Pasivo, 
			CASE WHEN Naturaleza.marcaCtacte >= 2 AND Debitos - Creditos > 0 THEN Debitos - Creditos ELSE 0 END AS Perdida, 
			CASE WHEN Naturaleza.marcaCtacte >= 2 AND Creditos - Debitos > 0 THEN Creditos - Debitos ELSE 0 END AS Ganancia, 
			PC.naturaleza, 
			PC.clase, 
			PC.subclase, 
			PC.imputacion,
			naturaleza.marcaCtacte,
			naturaleza.descripcion AS NombreNaturaleza 
INTO #B
FROM       conPlanCuentas AS Naturaleza INNER JOIN
           conPlanCuentas AS PC ON Naturaleza.naturaleza = PC.naturaleza LEFT OUTER JOIN
           #A AS A ON PC.cuenta = A.cuenta		
WHERE    PC.periodo = @periodo
AND		(Naturaleza.periodo = @periodo) 
AND		(Naturaleza.clase = 0) 
AND		(Naturaleza.subclase = 0) 
AND		(Naturaleza.imputacion = 0)
--AND		  ((A.Debitos + A.Creditos) > 0)
AND		  (((A.Debitos + A.Creditos) > 0) OR (@soloMovimiento = 0))
AND		  ((A.Debitos <> A.Creditos) OR (@soloSaldo = 0))


SELECT		999999997 as cuenta,
			'SUMAS PARCIALES' as descripcion, 
			SUM(debitos) as debitos, 
			SUM(creditos) as creditos, 
			SUM(deudor) as deudor, 
			SUM(acreedor) as acreedor, 
			SUM(Activo) as activo , 
			SUM(Pasivo) as pasivo, 
			SUM(Perdida)as perdida, 
			SUM(Ganancia) as ganancia, 
			9 as Tiponaturaleza, 
			9999 as naturaleza, 
			9999 as clase, 
			9999 as subclase, 
			9999 as imputacion
INTO #D
FROM #B

INSERT INTO #D
SELECT		999999998 as cuenta,
			'RESULTADOS' as descripcion, 
			0 as debitos, 
			0 as creditos, 
			0 as deudor, 
			0 as acreedor, 
			CASE WHEN activo < pasivo THEN pasivo - activo  ELSE 0 END AS activo,
			CASE WHEN activo > pasivo THEN activo - pasivo ELSE 0 END AS pasivo, 
			CASE WHEN perdida < ganancia THEN ganancia - perdida ELSE 0 END,-- AS ganancia, 
			CASE WHEN perdida > ganancia THEN perdida - ganancia ELSE 0 END,-- AS perdida,
			9 as Tiponaturaleza, 
			9999 as naturaleza, 
			9999 as clase, 
			9999 as subclase, 
			9999 as imputacion
FROM #D

INSERT INTO #D
SELECT		999999999 as cuenta,
			'SUMAS IGUALES' as descripcion, 
			SUM(debitos) as debitos, 
			SUM(creditos) as creditos, 
			SUM(deudor) as deudor, 
			SUM(acreedor) as acreedor, 
			SUM(activo) AS activo,
			SUM(pasivo) AS pasivo, 
			SUM(perdida) AS perdida,
			SUM(ganancia) AS ganancia, 
			9 as Tiponaturaleza, 
			9999 as naturaleza, 
			9999 as clase, 
			9999 as subclase, 
			9999 as imputacion
FROM #D

--SELECT '#B', * FROM #B


SELECT      B.*, 
            PCNat.marcaCtacte AS TipoNaturaleza, 	
			PCNat.cuenta AS cuentaNaturaleza, 
			PCCla.cuenta AS cuentaClase, 
            PCSub.cuenta AS cuentaSubClase 
           ,PCNat.descripcion AS descripcionNaturaleza, PCCla.descripcion AS descripcionClase, PCSub.descripcion AS descripcionSubClase
INTO #C
FROM         #B B INNER JOIN
                      conPlanCuentas AS PCNat ON B.naturaleza = PCNat.naturaleza INNER JOIN
                      conPlanCuentas AS PCCla ON B.naturaleza = PCCla.naturaleza AND B.clase = PCCla.clase INNER JOIN
                      conPlanCuentas AS PCSub ON B.naturaleza = PCSub.naturaleza AND B.clase = PCSub.clase AND B.subclase = PCSub.subclase
WHERE       (PCNat.clase = 0) 
AND			(PCNat.subclase = 0) 
AND			(PCNat.imputacion = 0) 
AND			(PCNat.periodo = @periodo) 
AND			(PCCla.subclase = 0) 
AND			(PCCla.imputacion = 0) 
AND			(PCCla.periodo = @periodo) 
AND			(PCSub.imputacion = 0) 
AND			(PCSub.periodo = @periodo)


SELECT     cuenta, descripcion, Debitos, Creditos, Deudor, Acreedor, Activo, Pasivo, Perdida, Ganancia, TipoNaturaleza, naturaleza, clase, subclase, imputacion, NombreNaturaleza
FROM         #C
union
SELECT     DISTINCT cuentaNaturaleza, descripcionNaturaleza, 0, 0, 0, 0, 0, 0, 0, 0, TipoNaturaleza, naturaleza, 0, 0, 0, NombreNaturaleza
FROM         #C
union
SELECT     DISTINCT cuentaClase, descripcionClase, 0, 0, 0, 0, 0, 0, 0, 0, TipoNaturaleza, naturaleza, clase, 0, 0, NombreNaturaleza
FROM         #C
union
SELECT     DISTINCT cuentaSubClase, descripcionSubClase, 0, 0, 0, 0, 0, 0, 0, 0, TipoNaturaleza, naturaleza, clase, subclase, 0, NombreNaturaleza
FROM         #C
union
SELECT *, 'TOTALES' FROM #D 



--SELECT * FROM #C