USE [InnFront_ent]
GO
/****** Object:  StoredProcedure [dbo].[Facturas_Cerradas]    Script Date: 06/26/2015 09:49:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Facturas_Cerradas]
                @dia_inicio datetime,
			    @dia_fin    datetime,
                @filter     varchar(8000),
	  		    @orden      tinyint,
	  		    @CodEmpresa char(3)
AS

/* Procedimiento que genera un cursor con los datos requeridos para el  listado de facturas cerradas
   con sus correspondientes notas agrupadas por cuenta/mon_cob. dia_inicio y dia_fin: rango de fechas de cierre  */

begin
SET NOCOUNT ON
  
declare @pref_fact char(4),
        @pref_no_fact char(4),
	@cSql Varchar(100)  

Select @pref_fact    = SubString(prefijo_fact,1,3) + '-' ,  
        @pref_no_fact = SubString(prefijo_no_fact,1,3) + '-'   
  From Hotetabl

Declare @Mone_Base integer, @da integer, @pt integer
Select @Mone_Base = id_mone from hotetabl
Select @da = con0_da, @pt = con0_pt from hoteempr where id_mone = @Mone_Base

/************************************************* Facturas  de Habitaciones ***************************************************************/
Select   hfac.id_hfac, enco.cuenta,
	 hfac.nro_cheque,  
     nro_documento   =  CASE WHEN hfac.factura = 1 then @pref_fact  else @pref_no_fact  END  + 
    			       SUBSTRING(CONVERT(varchar(255),hfac.nro_documento),1,7),
     id_origen          = 'H-'+ habi.habitacion,
	 referencia         = habi.habitacion,
     refer_thab			= thab.tipo_hab,
     cliente            = hcli.nombre,
	 contrato           = cont.desc_cont,
	 agencia            = agen.agencia,
     importe_moncob     =  COALESCE( Sum(  COALESCE(hfco.aplicado / hfex.tasa_monfac * hfco.tasa_monpag, 0) ), 0  ),
     mon_cob            =  COALESCE(mone.cod_mone,sicla.cod_mone),
     importe_monfact    =  Sum (COALESCE(hfco.aplicado,0)),
     mon_fact           =  sicla.cod_mone,
     hfac.fecha_cierre,
     hfac.id_operador,
     tcob.cod_tcob, 
	 enco.descripcion,
     hfac.factura, 
     nro_factura        =   hfac.nro_documento,
	 hhab.fecha_ent,
	 hhab.fecha_sal,
	 paxs               = hcli.paxs,
     Hospedaje = ingresos.Hospedaje,
     ayb = ingresos.ayb,
     otros = ingresos.otros,
     Depositos  = ingresos.depositos,
	 impuestos = ingresos.impuestos,
	 hfac.origen,
     operador = oper.nombre,
     terminal = term.nombre
     Into #temp_fact
     From Hotehfac hfac 
          Inner Join Hotehfex hfex   on hfac.id_hfac   = hfex.id_hfac
          Inner Join Hotehhab hhab   on hfex.id_origen = hhab.id_face
          Inner Join Hotecont cont   on hhab.id_cont   = cont.id_cont
          Inner Join Hoteagen agen   on cont.id_agen   = agen.id_agen
          Inner Join Hotehabi habi   on hhab.id_habi   = habi.id_habi
          inner join Hotethab thab on habi.id_thab = thab.id_thab
          Inner Join Siclamon sicla  on hfac.id_mone   = sicla.id_mone
          left join Operador oper   on hfac.id_operador = oper.id_oper
          left join terminales term on hfac.terminal =  term.id_terminal
          Left  Join Hotehfco hfco   on hfac.id_hfac   = hfco.id_hfac
          Left  Join Hotetcob tcob   on hfco.id_tcob   = tcob.id_tcob
          Left  Join Siclamon mone   on tcob.id_mone   = mone.id_mone
          Left  Join Hoteenco enco   on hfco.id_cuenta = enco.id_cuenta 
          Inner Join ( Select hcli.id_face,nombre = Min(nombre), paxs = Count(id_facn) 
                         From hotehcli hcli 
                         Group by hcli.id_face) hcli on  hfex.id_origen = hcli.id_face 
	  Left Join ( Select hfac.nro_cheque, 
                         Hospedaje = Sum ( Case When thab.id_con0 is not null 
                                		     Then Convert(numeric(19,2), Round( COALESCE( 
								   Convert(numeric(19,2),importe_cargo * CASE WHEN hfel.corrige_a is null THEN 1 ELSE -1 END) -
		  			 	      		   Convert(numeric(19,2),Coalesce(importe_dcto,0.00)) -
						      		   Convert(numeric(19,2),Coalesce(impu.impuesto, 0.00)) , 0.00),2)
								  )
                                		     Else 0.00
                     	   			 End ),
  	 		 ayb = Sum ( Case When rald.id_pre0 is not null
                                		     Then Convert(numeric(19,2), Round( COALESCE( 
								   Convert(numeric(19,2),importe_cargo * CASE WHEN hfel.corrige_a is null THEN 1 ELSE -1 END) -
		  			 	      		   Convert(numeric(19,2),Coalesce(importe_dcto,0.00)) -
						      		   Convert(numeric(19,2),Coalesce(impu.impuesto, 0.00)) , 0.00),2)
								  )
                                		     Else 0.00
                     	   			 End
			  		      ),
	 		 otros	=  Sum ( Case When thab.id_con0 is null and rald.id_pre0 is null and pre0.id_con0 != @da and pre0.id_con0 != @pt
                                      		   Then Convert(numeric(19,2), COALESCE(
								   Round(Convert(numeric(19,2),importe_cargo * CASE WHEN hfel.corrige_a is null THEN 1 ELSE -1 END)-
		  			 	      		   Convert(numeric(19,2), Coalesce(importe_dcto,0.00)) -
						      		   Convert(numeric(19,2), Coalesce(impu.impuesto ,0.00)) ,0.00 ),2) )
                                      			Else 0.00
                     	  	 		 End ),
                         depositos  = Sum ( Case When (pre0.id_con0 = @da or pre0.id_con0 = @pt)
                                      		   Then Convert(numeric(19,2), COALESCE(
								   Round(Convert(numeric(19,2),importe_cargo * CASE WHEN hfel.corrige_a is null THEN 1 ELSE -1 END)-
		  			 	      		   Convert(numeric(19,2), Coalesce(importe_dcto,0.00)) -
						      		   Convert(numeric(19,2), Coalesce(impu.impuesto ,0.00)) ,0.00 ),2) )
                                      			Else 0.00
                     	  	 		 End ),
			 impuestos = ROUND( Sum( Coalesce(impu.impuesto,0.00) ) , 4)
 		     From Hotehfac hfac 
		          Inner Join Hotehfex hfex  on hfac.id_hfac   = hfex.id_hfac
		          Inner Join Hotehhab hhab  on hfex.id_origen = hhab.id_face
		          Inner Join Siclamon sicla on hfac.id_mone   = sicla.id_mone
			  Left  Join Hotehfel hfel  on hfac.id_hfac   = hfel.id_hfac
			  Left  Join hotepre0 pre0  on hfel.id_pre0   =  pre0.id_pre0
			  left  join hotecon0 con0  on pre0.id_con0   =  con0.id_con0
                          Left  join Hotecon1 con1  on con0.id_con1   =  con1.id_con1
			  Left  join ( Select Distinct id_con0 From hotethab) thab on con0.id_con0 = thab.id_con0
                          Left  join ( Select Distinct id_pre0, id_ralo From hoterald inner join hoterali on hoterald.id_rali = hoterali.id_rali) rald 
                                  on pre0.id_pre0 = rald.id_pre0 and rald.id_ralo = hhab.id_ralo
			  Left  join ( Select hotehfel.id_hfel, impuesto = Sum(Coalesce(importe_imp,0.00)) 
  					 From hotehfei inner join hotehfel on hotehfei.id_hfel = hotehfel.id_hfel
        				               inner join hotehfex on hotehfex.id_hfac = hotehfel.id_hfac
        				               inner join hotehfac on hotehfac.id_hfac = hotehfex.id_hfac
					  Where hotehfac.origen = '1' and DateDiff(day, @dia_inicio, fecha_cierre) >= 0 and
	                                                                  DateDiff(day, @dia_fin, fecha_cierre) <= 0 
					  Group by hotehfel.id_hfel 
				     ) impu on hfel.id_hfel = impu.id_hfel
                     Where  hfac.origen = '1' and DateDiff(day, @dia_inicio, fecha_cierre) >= 0 and
	                                          DateDiff(day, @dia_fin, fecha_cierre) <= 0 
	        Group By hfac.nro_cheque ) ingresos on hfac.nro_cheque = ingresos.nro_cheque
   Where hfac.origen = '1' and 
          DateDiff(day, @dia_inicio, fecha_cierre) >= 0 and
          DateDiff(day, @dia_fin   , fecha_cierre) <= 0 
   Group By hfac.id_hfac,enco.cuenta,mone.cod_mone,hfac.nro_cheque, thab.tipo_hab,
                  sicla.cod_mone,hcli.nombre,oper.nombre,term.nombre,
                  hfac.factura,hfac.nro_documento,habi.habitacion,hfac.origen,ingresos.depositos,
                  hfac.fecha_cierre ,hfac.id_operador,hfac.terminal,tcob.cod_tcob,ingresos.Hospedaje,ingresos.ayb,ingresos.otros,ingresos.impuestos,
		  enco.descripcion,hhab.fecha_ent,hhab.fecha_sal, hcli.paxs, agen.agencia,cont.desc_cont
	 	  
Union All /************************************************* Notas de Facturas de Habitaciones ***************************************************************/
   Select hfac.id_hfac,enco.cuenta,
         hfac.nro_cheque,  
         nro_documento          = CASE WHEN hfac.factura = 1 then @pref_fact  else @pref_no_fact  END +  CONVERT(varchar(16),nota.nro_nota), 
         id_origen              = 'N'+ CASE WHEN nota.debito = 1 THEN 'D-' ELSE'C-' END + 'H-' + habi.habitacion,
	 referencia         	= habi.habitacion,
     refer_thab			= thab.tipo_hab,
         cliente                = hcli.nombre,
	 contrato               = cont.desc_cont,
	 agencia		= agen.agencia,
         importe_moncob         = COALESCE(noco.entregado,0),
         mon_cob                = mone.cod_mone,
         importe_monfact        = COALESCE(noco.aplicado,0),
         mon_fact               = sicla.cod_mone,
         nota.fecha_nota,
         nota.id_operador,
         tcob.cod_tcob, 
	 enco.descripcion,
         hfac.factura, 
         nro_factura        =   hfac.nro_documento,
	 hhab.fecha_ent,
	 hhab.fecha_sal,
	 hcli.paxs,
         Hospedaje = ingresos.Hospedaje,
	 ayb = ingresos.ayb,
         otros = ingresos.otros,
         Depositos  = ingresos.depositos,
	 impuestos = ingresos.impuestos,
	 hfac.origen,
         operador = oper.nombre,
         terminal = term.nombre
     From  Hotenota nota
          Inner Join Hotehfac hfac   on nota.id_hfac    = hfac.id_hfac
          left join Operador oper   on nota.id_operador = oper.id_oper
          left join terminales term on nota.terminal =  term.id_terminal
          Inner  Join Hotenoco noco  on nota.id_nota    = noco.id_nota
          Inner  Join Hoteenco enco  on noco.id_cuenta  = enco.id_cuenta 
          Inner Join Hotehfex hfex   on hfac.id_hfac    = hfex.id_hfac
          Inner Join hotehhab hhab   on hfex.id_origen  = hhab.id_face
	  Inner Join Hotecont cont   on hhab.id_cont    = cont.id_cont
	  Inner Join Hoteagen agen   on cont.id_agen    = agen.id_agen
          Inner Join Hotehabi habi   on hhab.id_habi    = habi.id_habi
          inner join Hotethab thab on habi.id_thab = thab.id_thab
          Inner Join Siclamon sicla  on hfac.id_mone    = sicla.id_mone
          Inner  Join Hotetcob tcob  on noco.id_tcob    = tcob.id_tcob
          Inner  Join Siclamon mone  on tcob.id_mone    = mone.id_mone
          Inner Join 
                 ( Select hcli.id_face,Min(nombre) nombre, paxs = Count(id_facn) 
		     From hotehcli hcli Group by hcli.id_face)  hcli  on  hfex.id_origen = hcli.id_face 
  	  Left Join ( Select hfac.nro_cheque, Hospedaje 	= Sum ( Case When thab.id_con0 is not null
                        		     	     Then Convert(numeric(19,2), Round( COALESCE( 
							  Convert(numeric(19,2),lnot.importe_nota * CASE WHEN nota.debito = 1 THEN 1 ELSE -1 END ) -
					      		  Convert(numeric(19,2),Coalesce(impu.impuesto,0.00)) , 0),2) )
                        		     	     Else 0.00
             	   			 	End
		  		              ),
	 		 ayb = Sum ( Case When rald.id_pre0 is not null
                                		     Then Convert(numeric(19,2), Round( COALESCE( 
								   Convert(numeric(19,2),lnot.importe_nota  * CASE WHEN nota.debito = 1 THEN 1 ELSE -1 END) -
						      		   Convert(numeric(19,2),Coalesce(impu.impuesto, 0.00)) , 0.00),2)
								  )
                                		     Else 0.00
                     	   			 End
			  		      ),
                         depositos  = Sum ( Case When (pre0.id_con0 = @da or pre0.id_con0 = @pt)
                                     		   Then Convert(numeric(19,2), COALESCE(
								   Round(Convert(numeric(19,2),lnot.importe_nota * CASE WHEN  nota.debito = 1  THEN 1 ELSE -1 END)-
						      		   Convert(numeric(19,2), Coalesce(impu.impuesto ,0.00)) ,0.00 ),2) )
                                      			Else 0.00
                     	  	 		 End ),
	 		 otros	=  Sum ( Case When thab.id_con0 is null and rald.id_pre0 is null and pre0.id_con0 != @da and pre0.id_con0 != @pt
                                      		   Then Convert(numeric(19,2), COALESCE(
								   Round(Convert(numeric(19,2),lnot.importe_nota * CASE WHEN  nota.debito = 1  THEN 1 ELSE -1 END)-
						      		   Convert(numeric(19,2), Coalesce(impu.impuesto ,0.00)) ,0.00 ),2) )
                                      			Else 0.00
                     	  	 		 End ),
			 impuestos     = ROUND( Sum( Coalesce(impu.impuesto,0.00) ) , 4) 
      			 From  Hotenota nota
		             Inner Join Hotehfac hfac   on nota.id_hfac    = hfac.id_hfac
			     Left  Join Hotelnot lnot   on nota.id_nota    = lnot.id_nota
			     Left  Join hotepre0 pre0   on lnot.id_pre0    = pre0.id_pre0
			     Left  join hotecon0 con0   on pre0.id_con0    = con0.id_con0
			     Left  join ( Select Distinct id_con0 From hotethab) thab on con0.id_con0 = thab.id_con0
			     Left  join ( Select lnot.id_lnot, 
       					         impuesto = Sum(Coalesce(importe,0.00)) 
  					 From hotenoim noim
					     inner join hotelnot lnot on noim.id_lnot = lnot.id_lnot 
        				     inner join hotenota nota on lnot.id_nota = nota.id_nota
        				     inner join hotehfac hfac on nota.id_hfac = hfac.id_hfac
					  Where hfac.origen = '1'  and
                                                DateDiff(day, @dia_inicio, fecha_nota) >= 0 and
          	                                DateDiff(day, @dia_fin, fecha_nota) <= 0 
					  Group by lnot.id_lnot
				     ) impu on lnot.id_lnot = impu.id_lnot
                              Left  join ( Select Distinct id_pre0, id_ralo 
                                                               From hoterald inner join hoterali on hoterald.id_rali = hoterali.id_rali) rald 
                                                                              on pre0.id_pre0 = rald.id_pre0 
                        Where  origen = '1'  and
         			DateDiff(day, @dia_inicio, fecha_nota) >= 0 and
         			DateDiff(day, @dia_fin, fecha_nota) <= 0
  	          Group By hfac.nro_cheque
		   ) ingresos on hfac.nro_cheque = ingresos.nro_cheque
   Where hfac.origen = '1' and
         DateDiff(day, @dia_inicio, fecha_nota) >= 0 and
         DateDiff(day, @dia_fin, fecha_nota) <= 0 
Union All /* ************************************************ Facturas  de  Folios Externos ************************************************************** */
  Select hfac.id_hfac, enco.cuenta,
         hfac.nro_cheque,
         nro_documento        =  CASE WHEN hfac.factura = 1 then @pref_fact else @pref_no_fact END  +  
			              SUBSTRING(CONVERT(varchar(20),hfac.nro_documento),1,7),
         id_origen             = 'T-'+ Convert(varchar(20),hcre.num_tarj),
	 referencia            = Convert(varchar(20),hcre.num_tarj),
     refer_thab			= space(9),
         hcre.nombre_titular,
	 contrato              = ctipo.desc_tipo,
	 agencia   	       = ctipo.desc_tipo,
         importe_moncob        =  COALESCE(  Sum( COALESCE(hfco.aplicado / hfex.tasa_monfac * hfco.tasa_monpag,0 ) ) , 0),
         mon_cob               =  COALESCE(mone.cod_mone,sicla.cod_mone),
         importe_monfact       =  Sum (COALESCE(hfco.aplicado,0)), 
         mon_fact     	       = sicla.cod_mone,
         hfac.fecha_cierre,
         hfac.id_operador,
         tcob.cod_tcob, 
 	 enco.descripcion,
         hfac.factura, 
         nro_factura        =   hfac.nro_documento,
	 fecha_ent          = GETDATE(),
	 fecha_sal    	    = GETDATE(),
	 paxs               = 1,
         Hospedaje = ingresos.Hospedaje,
	 ayb = ingresos.ayb,
         otros = ingresos.otros,
         Depositos  = ingresos.depositos,
	 impuestos = ingresos.impuestos,
	 hfac.origen,
         operador = oper.nombre,
         terminal = term.nombre
   From Hotehfac hfac 
          left join Operador oper   on hfac.id_operador = oper.id_oper
          left join terminales term on hfac.terminal =  term.id_terminal
          Inner Join Hotehfex hfex on hfac.id_hfac    = hfex.id_hfac
          Inner Join hcretarj hcre on hfex.id_origen  = hcre.id_tarj
	  Inner Join hcretipo ctipo on hcre.id_tipo   = ctipo.id_tipo
          Inner Join Siclamon sicla on hfac.id_mone   = sicla.id_mone
          Left  Join Hotehfco hfco  on hfac.id_hfac   = hfco.id_hfac
          Left  Join Hotetcob tcob  on hfco.id_tcob   = tcob.id_tcob
          Left  Join Siclamon mone  on tcob.id_mone   = mone.id_mone
          Left  Join Hoteenco enco  on hfco.id_cuenta = enco.id_cuenta 
	  Left Join ( Select hfac.nro_cheque,  Hospedaje = Sum ( Case When thab.id_con0 is not null
                                		     Then Convert(numeric(19,2), Round( COALESCE( 
								   Convert(numeric(19,2),importe_cargo * CASE WHEN hfel.corrige_a is null THEN 1 ELSE -1 END/ hfel.tasa) -
		  			 	      		   Convert(numeric(19,2),Coalesce(importe_dcto/ hfel.tasa,0.00)) -
						      		   Convert(numeric(19,2),Coalesce(impu.impuesto/ hfel.tasa, 0.00)) , 0.00),2)
								  )
                                		     Else 0.00
                     	   			 End
			  		      ),
	 		 ayb = Sum ( Case When rald.id_pre0 is not null
                                		     Then Convert(numeric(19,2), Round( COALESCE( 
								   Convert(numeric(19,2),importe_cargo * CASE WHEN hfel.corrige_a is null THEN 1 ELSE -1 END / hfel.tasa) -
		  			 	      		   Convert(numeric(19,2),Coalesce(importe_dcto/ hfel.tasa,0.00)) -
						      		   Convert(numeric(19,2),Coalesce(impu.impuesto/ hfel.tasa, 0.00)) , 0.00),2)
								  )
                                		     Else 0.00
                     	   			 End
			  		      ),
                         depositos  = Sum ( Case When (pre0.id_con0 = @da or pre0.id_con0 = @pt)
                                      		   Then Convert(numeric(19,2), COALESCE(
								   Round(Convert(numeric(19,2),importe_cargo * CASE WHEN hfel.corrige_a is null THEN 1 ELSE -1 END/ hfel.tasa)-
		  			 	      		   Convert(numeric(19,2), Coalesce(importe_dcto/ hfel.tasa,0.00)) -
						      		   Convert(numeric(19,2), Coalesce(impu.impuesto/ hfel.tasa ,0.00)) ,0.00 ),2) )
                                      			Else 0.00
                     	  	 		 End ),
	 		 otros	=  Sum ( Case When thab.id_con0 is null and rald.id_pre0 is null and pre0.id_con0 != @da and pre0.id_con0 != @pt
                                      		   Then Convert(numeric(19,2), COALESCE(
								   Round(Convert(numeric(19,2),importe_cargo * CASE WHEN hfel.corrige_a is null THEN 1 ELSE -1 END/ hfel.tasa)-
		  			 	      		   Convert(numeric(19,2), Coalesce(importe_dcto/ hfel.tasa,0.00)) -
						      		   Convert(numeric(19,2), Coalesce(impu.impuesto/ hfel.tasa ,0.00)) ,0.00 ),2) )
                                      			Else 0.00
                     	  	 		 End ),
			 impuestos     = ROUND( Sum( Coalesce(impu.impuesto/ hfel.tasa,0.00) ) , 4)
		     From Hotehfac hfac 
		          Inner Join Hotehfex hfex  on hfac.id_hfac   = hfex.id_hfac
			  Inner Join hcretarj hcre  on hfex.id_origen  = hcre.id_tarj
		          Inner Join Siclamon sicla on hfac.id_mone   = sicla.id_mone
			  Left  Join Hotehfel hfel  on hfac.id_hfac   = hfel.id_hfac
			  Left  Join hotepre0 pre0  on hfel.id_pre0   =  pre0.id_pre0
			  left  join hotecon0 con0  on pre0.id_con0   =  con0.id_con0
			  Left  join ( Select Distinct id_con0 From hotethab) thab on con0.id_con0 = thab.id_con0
			  Left  join ( Select hotehfel.id_hfel, 
       					      impuesto = Sum(Coalesce(importe_imp,0.00)) 
  					 From hotehfei 
					     inner join hotehfel on hotehfei.id_hfel = hotehfel.id_hfel
        				     inner join hotehfex on hotehfex.id_hfac = hotehfel.id_hfac
        				     inner join hotehfac on hotehfac.id_hfac = hotehfex.id_hfac
					  Where hotehfac.origen = '2'  and
                                                DateDiff(day, @dia_inicio, fecha_cierre) >= 0 and
                                                DateDiff(day, @dia_fin, fecha_cierre) <= 0 
					  Group by hotehfel.id_hfel 
				     ) impu on hfel.id_hfel = impu.id_hfel
                          Left  join ( Select Distinct id_pre0, id_ralo 
                                                  From hoterald inner join hoterali on hoterald.id_rali = hoterali.id_rali) rald 
                                                                              on pre0.id_pre0 = rald.id_pre0 
                    Where  hfac.origen = '2'   and
                                  DateDiff(day, @dia_inicio, fecha_cierre) >= 0 and
	                    DateDiff(day, @dia_fin, fecha_cierre) <= 0 
	     Group By hfac.nro_cheque
		  ) ingresos on hfac.nro_cheque = ingresos.nro_cheque
   Where hfac.origen = '2' and
                Datediff(day,@dia_inicio,fecha_cierre)>=0 and 
                Datediff(day,fecha_cierre,@dia_fin )>=0
   Group By hfac.id_hfac,enco.cuenta,mone.cod_mone,hfac.nro_cheque,sicla.cod_mone,oper.nombre,term.nombre,
                    hfac.factura,hfac.nro_documento,hcre.num_tarj,hfac.origen,ingresos.depositos,
                    hcre.nombre_titular,ingresos.Hospedaje,ingresos.ayb,ingresos.otros,ingresos.impuestos,
                    hfac.fecha_cierre ,hfac.id_operador,hfac.terminal,tcob.cod_tcob, enco.descripcion, ctipo.desc_tipo  

Union All /************************************************* Notas de Facturas  de Folios Externos ***************************************************************/
   Select hfac.id_hfac, enco.cuenta,
          hfac.nro_cheque,  
          nro_documento         =  CASE WHEN hfac.factura = 1 then @pref_fact  else @pref_no_fact  END +  CONVERT(varchar(16),nota.nro_nota), 
          id_origen             =  'N'+ CASE WHEN nota.debito = 1 THEN 'D-' ELSE'C-' END + 'T-'+ Convert(varchar(20),hcre.num_tarj),
	  referencia            = Convert(varchar(20),hcre.num_tarj),
      refer_thab			= Space(9),
          cliente               =  hcre.nombre_titular,
          contrato	        = ctipo.desc_tipo,
	  agencia	        = ctipo.desc_tipo,
          importe_moncob        =  COALESCE(noco.entregado,0),
          mon_cob               =  mone.cod_mone,
          importe_monfact       =  COALESCE(noco.aplicado,0),
          mon_fact              = sicla.cod_mone,
          nota.fecha_nota,
          nota.id_operador,
          tcob.cod_tcob, 
	  enco.descripcion,
          hfac.factura, 
          nro_factura        =   hfac.nro_documento,
	  fecha_ent	     =  GETDATE(),
	  fecha_sal	     =  GETDATE(),
	  paxs               = 1,
          Hospedaje = ingresos.Hospedaje,
	  ayb = ingresos.ayb,
          otros = ingresos.otros,
          Depositos  = ingresos.depositos,
	  impuestos = ingresos.impuestos,
	  hfac.origen,
         operador = oper.nombre,
         terminal = term.nombre
     From  Hotenota nota
          left Join Hotehfac hfac     on nota.id_hfac        = hfac.id_hfac
          left join Operador oper     on nota.id_operador = oper.id_oper
          inner join terminales term   on nota.terminal =  term.id_terminal
          Inner  Join Hotenoco noco    on nota.id_nota       = noco.id_nota
          Inner  Join Hoteenco enco    on noco.id_cuenta  = enco.id_cuenta 
          Inner Join Hotehfex hfex     on hfac.id_hfac       = hfex.id_hfac
          Inner Join hcretarj hcre     on hfex.id_origen    = hcre.id_tarj
	  Inner Join hcretipo ctipo    on hcre.id_tipo   = ctipo.id_tipo
          Inner Join Siclamon sicla    on hfac.id_mone       = sicla.id_mone
          Inner  Join Hotetcob tcob    on noco.id_tcob      = tcob.id_tcob
          Inner  Join Siclamon mone    on tcob.id_mone       = mone.id_mone
  	  Left Join ( Select hfac.nro_cheque,  Hospedaje = Sum ( Case When thab.id_con0 is not null
                                		     Then Convert(numeric(19,2), Round( COALESCE( 
								   Convert(numeric(19,2),lnot.importe_nota * CASE WHEN nota.debito = 1 THEN 1 ELSE -1 END) -
						      		   Convert(numeric(19,2),Coalesce(impu.impuesto, 0.00)) , 0.00),2)
								  )
                                		     Else 0.00
                     	   			 End
			  		      ),
	 		 ayb = Sum ( Case When rald.id_pre0 is not null
                                		     Then Convert(numeric(19,2), Round( COALESCE( 
								   Convert(numeric(19,2),lnot.importe_nota * CASE WHEN nota.debito = 1 THEN 1 ELSE -1 END) -
						      		   Convert(numeric(19,2),Coalesce(impu.impuesto, 0.00)) , 0.00),2)
								  )
                                		     Else 0.00
                     	   			 End
			  		      ),
                         depositos  = Sum ( Case When (pre0.id_con0 = @da or pre0.id_con0 = @pt)
                                      		   Then Convert(numeric(19,2), COALESCE(
								   Round(Convert(numeric(19,2),lnot.importe_nota * CASE WHEN  nota.debito = 1  THEN 1 ELSE -1 END)-
						      		   Convert(numeric(19,2), Coalesce(impu.impuesto ,0.00)) ,0.00 ),2) )
                                      			Else 0.00
                     	  	 		 End ),
	 		 otros	=  Sum ( Case When thab.id_con0 is null and rald.id_pre0 is null and pre0.id_con0 != @da and pre0.id_con0 != @pt
                                      		   Then Convert(numeric(19,2), COALESCE(
								   Round(Convert(numeric(19,2),lnot.importe_nota * CASE WHEN nota.debito = 1 THEN 1 ELSE -1 END)-
						      		   Convert(numeric(19,2), Coalesce(impu.impuesto ,0.00)) ,0.00 ),2) )
                                      			Else 0.00
                     	  	 		 End ),
			 impuestos     = ROUND( Sum( Coalesce(impu.impuesto,0.00) ) , 4)
      			 From  Hotenota nota
		             Inner Join Hotehfac hfac   on nota.id_hfac    = hfac.id_hfac
			     Left  Join Hotelnot lnot   on nota.id_nota    = lnot.id_nota
			     Left  Join hotepre0 pre0   on lnot.id_pre0    = pre0.id_pre0
			     Left  join hotecon0 con0   on pre0.id_con0    = con0.id_con0
			     Left  join ( Select Distinct id_con0 From hotethab) thab on con0.id_con0 = thab.id_con0
			     Left  join ( Select lnot.id_lnot, 
       					         impuesto = Sum(Coalesce(importe,0.00)) 
  					 From hotenoim noim
					     inner join hotelnot lnot on noim.id_lnot = lnot.id_lnot 
        				     inner join hotenota nota on lnot.id_nota = nota.id_nota
        				     inner join hotehfac hfac on nota.id_hfac = hfac.id_hfac
					  Where hfac.origen = '1'  and
                                               DateDiff(day, @dia_inicio, fecha_nota) >= 0 and
	                                       DateDiff(day, @dia_fin, fecha_nota) <= 0 
					  Group by lnot.id_lnot
				     ) impu on lnot.id_lnot = impu.id_lnot
                              Left  join ( Select Distinct id_pre0, id_ralo 
                                                               From hoterald inner join hoterali on hoterald.id_rali = hoterali.id_rali) rald 
                                                                              on pre0.id_pre0 = rald.id_pre0 
                        Where  origen = '2' and
			         DateDiff(day, @dia_inicio, fecha_nota) >= 0 and
   				 DateDiff(day, @dia_fin, fecha_nota) <= 0  
	          Group By hfac.nro_cheque
		   ) ingresos on hfac.nro_cheque = ingresos.nro_cheque
   Where hfac.origen = '2' and
         DateDiff(day, @dia_inicio, fecha_nota) >= 0 and
         DateDiff(day, @dia_fin, fecha_nota) <= 0 
Union All 
 /* ************************************************ Facturas  de  Agencias  ************************************************************** */
Select  hfac.id_hfac,enco.cuenta,
        hfac.nro_cheque,
        nro_documento =  CASE WHEN hfac.factura = 1  then @pref_fact  else @pref_no_fact   END   + 
		             SUBSTRING(CONVERT(varchar(255),hfac.nro_documento),1,7),
        id_origen        = 'A-' + agen.agencia,
	referencia       = COALESCE(faca.habitacion, ''),
        refer_thab			= space(9),
        cliente     	 = COALESCE(faca.nombre, ''),
        contrato    	 = cont.desc_cont,
	agencia     	 = agen.agencia,
        importe_moncob   = COALESCE(  Sum(  COALESCE(hfco.aplicado / hfag.tasa * hfco.tasa_monpag ,0 ) ) , 0 ),
        mon_cob          = COALESCE(mone.cod_mone,sicla.cod_mone),
        importe_monfact  = COALESCE( Sum ( COALESCE(hfco.aplicado,0) ), 0), 
        mon_fact 	 = sicla.cod_mone,
        hfac.fecha_cierre,
        hfac.id_operador,
        tcob.cod_tcob, 
	enco.descripcion,
         hfac.factura, 
         nro_factura        =   hfac.nro_documento,
	fecha_ent          = hfag.fecha_desde    ,
	fecha_sal	   = hfag.fecha_hasta    ,
	paxs		   = COALESCE(faca.paxs, 0),
        Hospedaje = ingresos.Hospedaje,
	ayb = ingresos.ayb,
        otros = ingresos.otros,
        Depositos  = ingresos.depositos,
	impuestos = Coalesce(Sum ( COALESCE(impuage.impuestos,0.00)),0.00),
	hfac.origen,
        operador = oper.nombre,
        terminal = term.nombre
     From Hotehfac hfac 
          left join Operador oper   on hfac.id_operador = oper.id_oper
          left join terminales term on hfac.terminal =  term.id_terminal
          Inner Join Hotehfag hfag  on hfac.id_hfac    = hfag.id_hfac
          Inner Join Hotecont cont  on hfag.id_cont    = cont.id_cont
          Inner Join Hoteagen agen  on cont.id_agen    = agen.id_agen
          Inner Join Siclamon sicla on hfac.id_mone    = sicla.id_mone
          Left  Join Hotehfco hfco  on hfac.id_hfac    = hfco.id_hfac
          Left  Join Hotetcob tcob  on hfco.id_tcob    = tcob.id_tcob
          Left  Join Siclamon mone  on tcob.id_mone    = mone.id_mone
          Left  Join Hoteenco enco  on hfco.id_cuenta  = enco.id_cuenta 
          Left  Join ( Select id_faca,
			      nombre = Min(nombre), 
       		              paxs   = Count(id_facn),
			      habitacion = Min(Habi.habitacion)
  		 	 From hotehhab hhab 
    			    Inner Join Hotehcli hcli on hhab.id_face = hcli.id_face
			    Inner Join Hotehabi habi on hhab.id_habi = habi.id_habi
 			 Where id_faca is not null
 			 Group by id_faca
		      ) faca on hfag.id_faca = faca.id_faca
	  Left Join (Select hfac.nro_cheque,  
		     	 Hospedaje = Sum ( Case When hfar.id_csim is not null -- Concepto Simple
		     	     			Then CASE WHEN csim.tipo_csim = 1 THEN Round( COALESCE( Convert(numeric(19,2),hfar.importe / hfag.tasa  * 
									   		    	        CASE WHEN hfar.corrige_a is null THEN 1 ELSE -1 END  -
		      		  		     			   		    	        Coalesce(0,0.00) ), 0), 2) 
									     ELSE 0
						     END
		     	     		    Else CASE WHEN cfag.id_uhab is not null THEN Round( COALESCE( Convert(numeric(19,2),hfar.importe  / hfag.tasa * 
									   		    	    	  CASE WHEN hfar.corrige_a is null THEN 1 ELSE -1 END  -
		      		  		     			   		    	    	  Coalesce(0,0.00) ), 0), 2) 
							 			     ELSE 0
						 END		
				 	   End
		              		 ),
		       	 ayb = Sum ( Case When hfar.id_csim is not null -- Concepto Simple
		     	     			Then CASE WHEN csim.tipo_csim = 2 THEN Round( COALESCE( Convert(numeric(19,2),hfar.importe / hfag.tasa * 
									   		    	        CASE WHEN hfar.corrige_a is null THEN 1 ELSE -1 END  -
		      		  		     			   		    	        Coalesce(0,0.00) ), 0), 2) 
									     ELSE 0
						     END
		     	     		    Else CASE WHEN cfag.id_uhab is null and
							   cfag.id_ralo is not null  THEN Round( COALESCE( Convert(numeric(19,2),hfar.importe / hfag.tasa * 
									   		    	    	   CASE WHEN hfar.corrige_a is null THEN 1 ELSE -1 END  -
		      		  		     			   		    	    	   Coalesce(0,0.00) ), 0), 2) 
							 			     ELSE 0
						 END		
				 	   End 
				    ),
                         depositos = 0.00,
		     	 otros = Sum ( Case When hfar.id_csim is not null -- Concepto Simple
		     	     			Then CASE WHEN csim.tipo_csim = 3 THEN Round( COALESCE( Convert(numeric(19,2),hfar.importe / hfag.tasa * 
									   		    	        CASE WHEN hfar.corrige_a is null THEN 1 ELSE -1 END -
		      		  		     			   		    	        Coalesce(0,0.00) ), 0), 2) 
									     ELSE 0
						     END
		     	     		    Else CASE WHEN cfag.id_uhab is null and 
						   cfag.id_ralo is null THEN Round( COALESCE( Convert(numeric(19,2),hfar.importe / hfag.tasa * 
									   		    	      CASE WHEN hfar.corrige_a is null THEN 1 ELSE -1 END -
		      		  		     			   		    	      Coalesce(0,0.00) ), 0), 2) 
							 			ELSE 0
						 END		
				 	   End
		              		 ),
                        impuestos = Convert(numeric(19,2), 0)
		   From  Hotehfar hfar
		         Left  Join Hotecsim csim  on hfar.id_csim  = csim.id_csim
		         Inner Join Hotecfag cfag  on hfar.id_cfag  = cfag.id_cfag
		         Inner Join hotehfac hfac  on hfar.id_hfac  = hfac.id_hfac
		         Inner Join hotehfag hfag  on hfar.id_hfac  = hfag.id_hfac
		    Where  hfac.origen = '3'  and
                           DateDiff(day, @dia_inicio, fecha_cierre) >= 0 and
                           DateDiff(day, @dia_fin, fecha_cierre) <= 0 
		    Group By hfac.nro_cheque
	  ) ingresos on hfac.nro_cheque = ingresos.nro_cheque
 	Left  join ( Select hfac2.nro_cheque, impuestos = Sum(Coalesce(Convert(Numeric(17,2),(importe_imp*cantidad_h*datediff(day,fecha_1,fecha_2))/hfag2.tasa),000.0000))
  					 From hotehfai hfai2  inner join hotehfal hfal2 on hfal2.id_hfal = hfai2.id_hfal
        				                                              inner join hotehfac hfac2 on hfac2.id_hfac = hfal2.id_hfac
                                                                                                   inner join hotehfag hfag2 on hfac2.id_hfac = hfag2.id_hfac
					 Where hfac2.origen = '3'  
					 Group by hfac2.nro_cheque, hfag2.tasa
				     ) impuage on hfac.nro_cheque = impuage.nro_cheque
   Where hfac.origen = '3' and
         DateDiff(day, @dia_inicio, fecha_cierre) >= 0 and
	 DateDiff(day, @dia_fin   , fecha_cierre) <= 0 
   Group By hfac.id_hfac,enco.cuenta,mone.cod_mone,hfac.nro_cheque,sicla.cod_mone,oper.nombre,term.nombre,impuage.nro_cheque,
            hfac.factura,hfac.nro_documento,cont.contrato,agen.agencia,hfac.origen, impuage.impuestos,ingresos.depositos,
            hfac.fecha_cierre ,hfac.id_operador,hfac.terminal,tcob.cod_tcob,ingresos.Hospedaje,ingresos.ayb,ingresos.otros, ingresos.impuestos,
	    enco.descripcion,hfag.fecha_desde,hfag.fecha_hasta,faca.paxs, cont.desc_cont, faca.nombre, faca.habitacion
Union All /************************************************* Notas de Facturas  de Agencias ***************************************************************/
   Select hfac.id_hfac, enco.cuenta,
          hfac.nro_cheque,  
          nro_documento         =  CASE WHEN hfac.factura = 1 then @pref_fact  else @pref_no_fact  END +  CONVERT(varchar(16),nota.nro_nota), 
          id_origen             =   'N'+ CASE WHEN nota.debito = 1 THEN 'D-' ELSE'C-' END + 'A-'  +  agen.agencia,
	  referencia		=  COALESCE(faca.habitacion, ''),
      refer_thab			= space(9),
          cliente               =  cont.contrato,
          contrato	        = cont.desc_cont,
	  agencia	        = agen.agencia,
          importe_moncob        =  COALESCE(noco.entregado,0),
          mon_cob               =  mone.cod_mone,
          importe_monfact       =  COALESCE(noco.aplicado,0),
          mon_fact              =  sicla.cod_mone,
          nota.fecha_nota,
          nota.id_operador,
          tcob.cod_tcob, 
	  enco.descripcion,
         hfac.factura, 
         nro_factura        =   hfac.nro_documento,
	  fecha_ent          = hfag.fecha_desde    ,
	  fecha_sal	     = hfag.fecha_hasta    ,
	  paxs               = COALESCE(faca.paxs, 0),
          Hospedaje = ingresos.Hospedaje,
	  ayb = ingresos.ayb,
          otros = ingresos.otros,
          Depositos  = ingresos.depositos,

	  impuestos = ingresos.impuestos,
	  hfac.origen,
         operador = oper.nombre,
         terminal = term.nombre
     From  Hotenota nota
          left join Operador oper   on nota.id_operador = oper.id_oper
          left join terminales term on nota.terminal =  term.id_terminal
          Inner Join Hotehfac hfac    on nota.id_hfac        = hfac.id_hfac
          Inner  Join Hotenoco noco  on nota.id_nota       = noco.id_nota
          Inner  Join Hoteenco enco  on noco.id_cuenta  = enco.id_cuenta 
          Inner Join Hotehfag hfag     on hfac.id_hfac       = hfag.id_hfac
          Inner Join Hotecont cont    on hfag.id_cont        = cont.id_cont
          Inner Join Hoteagen agen  on cont.id_agen      = agen.id_agen
          Inner Join Siclamon sicla     on hfac.id_mone     = sicla.id_mone
          Inner  Join Hotetcob tcob    on noco.id_tcob      = tcob.id_tcob
          Inner  Join Siclamon mone   on tcob.id_mone     = mone.id_mone
          Left   Join ( Select id_faca,
       		              paxs = Count(id_facn),
			      habitacion = Min(Habi.habitacion)
  		 	 From hotehhab hhab 
    			    Inner Join Hotehcli hcli on hhab.id_face = hcli.id_face
			    Inner Join Hotehabi habi on hhab.id_habi = habi.id_habi
 			 Where id_faca is not null
 			 Group by id_faca
		      ) faca on hfag.id_faca = faca.id_faca
  	  Left Join ( Select hfac.nro_cheque, Hospedaje = Sum ( Case When thab.id_con0 is not null
                                		     Then Convert(numeric(19,2), Round( COALESCE( 
								   Convert(numeric(19,2),lnot.importe_nota * CASE WHEN nota.debito = 1 THEN 1 ELSE -1 END) -
						      		   Convert(numeric(19,2),Coalesce(impu.impuesto, 0.00)) , 0.00),2)
								  )
                                		     Else 0.00
                     	   			 End
			  		      ),
	 		 ayb = Sum ( Case When rald.id_pre0 is not null
                                		     Then Convert(numeric(19,2), Round( COALESCE( 
								   Convert(numeric(19,2),lnot.importe_nota * CASE WHEN nota.debito = 1 THEN 1 ELSE -1 END) -
						      		   Convert(numeric(19,2),Coalesce(impu.impuesto, 0.00)) , 0.00),2)
								  )
                                		     Else 0.00
                     	   			 End
			  		      ),
                         depositos  = Sum ( Case When (pre0.id_con0 = @da or pre0.id_con0 = @pt)
                                      		   Then Convert(numeric(19,2), COALESCE(
								   Round(Convert(numeric(19,2),lnot.importe_nota * CASE WHEN  nota.debito = 1  THEN 1 ELSE -1 END)-
						      		   Convert(numeric(19,2), Coalesce(impu.impuesto ,0.00)) ,0.00 ),2) )
                                      			Else 0.00
                     	  	 		 End ),
	 		 otros	=  Sum ( Case When thab.id_con0 is null and rald.id_pre0 is null and pre0.id_con0 != @da and pre0.id_con0 != @pt
                                      		   Then Convert(numeric(19,2), COALESCE(
								   Round(Convert(numeric(19,2),lnot.importe_nota * CASE WHEN nota.debito = 1 THEN 1 ELSE -1 END)-
						      		   Convert(numeric(19,2), Coalesce(impu.impuesto ,0.00)) ,0.00 ),2) )
                                      			Else 0.00
                     	  	 		 End ),
			 impuestos     = ROUND( Sum( Coalesce(impu.impuesto,0.00) ) , 4)  
      			 From  Hotenota nota
		             Inner Join Hotehfac hfac   on nota.id_hfac    = hfac.id_hfac
			     Left  Join Hotelnot lnot   on nota.id_nota    = lnot.id_nota
			     Left  Join hotepre0 pre0   on lnot.id_pre0    = pre0.id_pre0
			     Left  join hotecon0 con0   on pre0.id_con0    = con0.id_con0
			     Left  join ( Select Distinct id_con0 From hotethab) thab on con0.id_con0 = thab.id_con0
			     Left  join ( Select lnot.id_lnot, 
       					         impuesto = Sum(Coalesce(importe,0.00)) 
  					 From hotenoim noim
					     inner join hotelnot lnot on noim.id_lnot = lnot.id_lnot         			
        				     inner join hotenota nota on lnot.id_nota = nota.id_nota
        				     inner join hotehfac hfac on nota.id_hfac = hfac.id_hfac
					  Where hfac.origen = '1'   and
                                                DateDiff(day, @dia_inicio, fecha_nota) >= 0 and
	                                        DateDiff(day, @dia_fin, fecha_nota) <= 0 
					  Group by lnot.id_lnot
				     ) impu on lnot.id_lnot = impu.id_lnot
                              Left  join ( Select Distinct id_pre0, id_ralo 
                                                               From hoterald inner join hoterali on hoterald.id_rali = hoterali.id_rali) rald 
                                                                              on pre0.id_pre0 = rald.id_pre0 
                        Where  hfac.origen = '3' and
			         DateDiff(day, @dia_inicio, fecha_nota) >= 0 and
			         DateDiff(day, @dia_fin, fecha_nota) <= 0 
                         Group By hfac.nro_cheque
		   ) ingresos on hfac.nro_cheque = ingresos.nro_cheque
   Where  hfac.origen = '3' and
         DateDiff(day, @dia_inicio, fecha_nota) >= 0 and
         DateDiff(day, @dia_fin, fecha_nota) <= 0 
  Order by cuenta, mon_cob, hfac.nro_cheque, mon_fact 

  Select @cSql = 
     CASE 
        WHEN @orden = 1 THEN ' Order by id_origen,nro_cheque,cuenta,mon_cob,mon_fact'
        WHEN @orden = 2 THEN ' Order by nro_cheque,cuenta,mon_cob,mon_fact'
	WHEN @orden = 3 THEN ' Order by mon_fact,nro_cheque,cuenta,mon_cob'
	WHEN @orden = 4 or @orden = 5 THEN ' Order by cuenta,mon_cob,nro_cheque,mon_fact,nro_factura'
	WHEN @orden = 6 THEN ' Order by nro_factura,mon_fact,cuenta,mon_cob,nro_cheque'
    WHEN @orden = 7 THEN ' Order by id_origen,nro_cheque,cuenta,mon_cob,mon_fact'
     ELSE ''
  END     
 
 Execute ('Select ''001'' as empresa,* from #temp_fact' + @filter + @cSql) 

end
