SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



ALTER                        PROCEDURE sp_Anal_MatDok
	@Sifra_Nal Smallint,
	@Sifra_Dok Smallint,
	@Sifra_OE Smallint,
	@KoiOE Varchar(400),
	@Sifra_Prim Smallint,
	@KoiOE_Prim char(6) = Null,
	@Broj_Nal_Od Int,
	@Broj_Nal_Do Int,
	@Broj_Dok_Od Int,
	@Broj_Dok_Do Int,
	@Datum_Dok_Od Smalldatetime,
	@Datum_Dok_Do Smalldatetime,
	@Datum_Nal_Od Smalldatetime,
	@Datum_Nal_Do Smalldatetime,
	@Sifra_Kup Char(6),
	@Sifra_Za Char(1),
	@VlIzl Char(1),
	@PecFisc char(1) = Null,
	@Sifra_Nivo Char(2) = Null,
	@KFSifra_Nivo Char(1) = Null,
	@KFSifra_Prim Char(1) = Null,
	@Sifra_Pat	smallint = Null,
	@Sifra_Mest	int = Null,
	@Uces_Od	decimal(6,2) = Null,
	@Uces_Do	decimal(6,2) = Null,
	@Sifra_Drg	smallint = Null,
	@Lokacija	varchar(10) = Null,
	@NemaKomint	char(1) = Null,
	@NemaPatnik	char(1) = Null,
	@Sifra_Oper	varchar(20) = Null,
	@Sifra_Obj	smallint = Null,
	@PoKojDatum	char(1) = 'D',     -- D - Datum_Dok,      V - Datum_Vnes
	@Koi_Gradovi	varchar(300) = Null,
	@Kto		char(6) = Null,
	@BezOdbRabat	char(2) = 'NN',	-- DD - Bez odbien rabat so Danok	DN - Bez odbien rabat bez Danok
	@Sifra_Div	smallint = Null,
	@NemaDivizija	char(1) = Null,
	@PoGradovi	char(1) = 'N',
	@Sifra_Prev	char(6) = Null,
	@Spremil	varchar(5) = Null,
	@RezVoTabela	char(1) = 'N',
	@SoTezini	char(1) = 'N',
	@KDrugo1        smallint = NULL, 
	@KDrugo2        smallint = NULL,
	@KDrugo3        smallint = NULL,
	@KDrugo4        smallint = NULL,
	@KDrugo5        smallint = NULL,
	@KDrugo6        smallint = NULL,
	@KDrugo7        smallint = NULL
AS
	
	
	If @PoKojDatum = 'V' Or @PecFisc = 'D'
	        If @Datum_Dok_Do Is Not Null
		Set @Datum_Dok_Do = @Datum_Dok_Do + 1
   	Declare @SSQL Varchar(8000)
	If @RezVoTabela = 'D'
	Begin
		Set @SSQL = 'SELECT D.Sifra_OE, D.Sifra_Nal, D.Broj_Nal, D.Datum_Dok, D.VlIzl, D.Toc200, D.MagVr, D.MagVrDan, D.Danok1, D.Danok2 
				FROM DOKR D
				INNER JOIN TipDok TD ON D.Sifra_Dok=TD.Sifra_Dok
				LEFT OUTER JOIN Komint K ON D.Sifra_Kup=K.Sifra_Kup
				INNER JOIN Orged O ON D.Sifra_OE=O.Sifra_OE
				LEFT OUTER JOIN Nalozi N ON TD.Sifra_Nal=N.Sifra_Nal AND D.Broj_Nal=N.Broj_Nal
				LEFT OUTER JOIN Orged OP ON D.Sifra_Prim=OP.Sifra_OE 		
				LEFT OUTER JOIN KObjekti KO ON KO.Sifra_Kup=D.Sifra_Kup and KO.Sifra_Obj=D.Sifra_Obj '
	End
	Else
	Begin
		SET @SSQL = 'SELECT D.Sifra_OE, D.Sifra_Dok, D.Broj_Dok, D.Sifra_Prim, D.VlIzl, D.Sifra_Za, D.Sifra_Nal, D.Broj_Nal, D.Identif_Br, D.Sifra_Kup, D.Sifra_Obj, D.Datum_Dok, D.Rok, 
					DateADD(day, D.Rok, D.Datum_Dok)DatumDosp, D.Kto, D.Toc200, D.MagVr, D.MagVrDan, D.Marza, D.Danok1, D.Danok2, D.PDanok1, D.PDanok2, D.PTrosok, D.DanDokCena, 
					D.Dat_Pec_Fisc, 
					Sum(S.Kolic*S.NabCena) NabVr, Sum(S.Kolic*S.MagCena) MagVrednost, sum(S.DokCena*S.Kolic) PoDokCena, TD.ImeDok,
					K.ImeKup, K.SMesto,
					O.ImeOrg As IORG, OP.ImeOrg As IORG_Prima, KO.ImeObj, 
					Sum(S.Kolic*S.TrsCena) TrsVrednost, D.Plat_PerDan, '
		If @BezOdbRabat = 'DD'
			Set @SSQL = @SSQL + ' Sum(Case When S.VlIzl = ''I'' Then (S.Kolic*dbo.fn_VratiCena(S.DokCena, S.Posn, S.DanDokCena, ''D'')) Else 0 End) VrednBezRabat, '
		Else If @BezOdbRabat = 'DN'
			Set @SSQL = @SSQL + ' Sum(Case When S.VlIzl = ''I'' Then (S.Kolic*dbo.fn_VratiCena(S.DokCena, S.Posn, S.DanDokCena, ''N'')) Else 0 End) VrednBezRabat, '
		Else 
			Set @SSQL = @SSQL + ' Null as VrednBezRabat, '
		If @PoGradovi = 'D'
			Set @SSQL = @SSQL + ' (Case When KO.Sifra_Grad Is Not Null Then KO.Sifra_Grad Else K.Sifra_Grad End) Sifra_Grad, G.ImeGrad, '
		Else
			Set @SSQL = @SSQL + ' Null as Sifra_Grad, Null as ImeGrad, '
		If @SoTezini = 'D'
			Set @SSQL = @SSQL + ' Sum(S.Kolic*A.Tezina/1000) Tezina '
		Else 
			Set @SSQL = @SSQL + ' Null as  Tezina '
		Set @SSQL = @SSQL + ' FROM DOKR D
					 Left Outer Join Stavr S On D.DokrID=S.DokrID	
					 INNER JOIN TipDok TD ON D.Sifra_Dok=TD.Sifra_Dok
					 LEFT OUTER JOIN Komint K ON D.Sifra_Kup=K.Sifra_Kup
					 INNER JOIN Orged O ON D.Sifra_OE=O.Sifra_OE
					 LEFT OUTER JOIN Nalozi N ON TD.Sifra_Nal=N.Sifra_Nal AND D.Broj_Nal=N.Broj_Nal
					 LEFT OUTER JOIN Orged OP ON D.Sifra_Prim=OP.Sifra_OE 		
					 LEFT OUTER JOIN KObjekti KO ON KO.Sifra_Kup=D.Sifra_Kup and KO.Sifra_Obj=D.Sifra_Obj '
		If @Sifra_Drg Is Not Null Or @Lokacija Is Not Null Or @SoTezini = 'D'
			Set @SSQL = @SSQL + ' Left Outer Join Katart A On A.Sifra_art=S.Sifra_art '
		If @PoGradovi = 'D'
			Set @SSQL = @SSQL + ' Left Outer Join Grad G On G.Sifra_Grad = (Case When KO.Sifra_Grad Is Not Null Then KO.Sifra_Grad Else K.Sifra_Grad End) '
	End
	If @Sifra_Nal Is Null
		SET @SSQL = @SSQL + 'WHERE 1=1 	'
	Else
		SET @SSQL = @SSQL + 'WHERE D.Sifra_Nal=''' + Cast(@Sifra_Nal As Varchar(10)) + ''' '
	If @KFSifra_Nivo Is Null
		Begin
			If @Sifra_Nivo Is Not Null
				Set @SSQL = @SSQL + 'And D.Sifra_Nivo=''' + @Sifra_Nivo + ''' '
		End
	Else If @KFSifra_Nivo='D' And @Sifra_Nivo Is Not Null
		Set @SSQL = @SSQL + 'And (D.Sifra_Nivo<>''' + @Sifra_Nivo + ''' Or D.Sifra_Nivo Is Null) '
	If @PecFisc = 'D'
		SET @SSQL = @SSQL + ' AND D.Pec_Fisc > 0 '
	Else If @PecFisc = 'N'
		SET @SSQL = @SSQL + ' AND (D.Pec_Fisc = 0 or D.Pec_Fisc is Null) '
	If @Sifra_Dok Is NOT NULL
		SET @SSQL = @SSQL + 'AND D.Sifra_Dok=''' + Cast(@Sifra_Dok As Varchar(10)) + ''' '
   	If @Sifra_OE Is NOT Null
      		SET @SSQL = @SSQL + 'AND D.Sifra_OE=' + Cast(@Sifra_OE As Varchar(6)) + ' '
   	If @KoiOE Is NOT Null
      		SET @SSQL = @SSQL + 'AND D.Sifra_OE In (' + @KoiOE + ') '
	If @KFSifra_Prim Is Null
		Begin
			If @Sifra_Prim Is Not Null
				SET @SSQL = @SSQL + 'AND D.Sifra_Prim=' + Cast(@Sifra_Prim As Varchar(6)) + ' '
		End
	Else If @KFSifra_Prim='D' And @Sifra_Prim Is Not Null
		Set @SSQL = @SSQL + 'And (D.Sifra_Prim<>' + Cast(@Sifra_Prim As Varchar(6)) + ' Or D.Sifra_Prim Is Null) '
	If @KoiOE_Prim Is Not Null
		Set @SSQL = @SSQL + 'And D.Sifra_Prim In (Select Sifra_OE From SGrOrg Where Sif_GrOrg=''' + @KoiOE_Prim + ''') '
	If @Sifra_Pat IS NOT NULL
		SET @SSQL = @SSQL + ' AND D.Sifra_Pat = ' + Cast(@Sifra_Pat as varchar(5)) + ' '
-- Ima kontrola vo podgotovkata ako se pusti filterot @NemaPatnik da ne se pusta filterot @Sifra_pat
	If @NemaPatnik = 'D'
		SET @SSQL = @SSQL + ' AND D.Sifra_Pat is Null and D.Sifra_za in (1, 2) '
	If @Broj_Nal_Od Is NOT Null
	   	SET @SSQL = @SSQL + 'AND D.Broj_Nal>=''' + Cast(@Broj_Nal_Od As Varchar(35)) + ''' '
   	If @Broj_Nal_Do Is NOT Null
 		SET @SSQL = @SSQL + 'AND D.Broj_Nal<=''' + Cast(@Broj_Nal_Do As Varchar(35)) + ''' '
	If @Broj_Dok_Od Is NOT Null
	   	SET @SSQL = @SSQL + 'AND D.Broj_Dok>=''' + Cast(@Broj_Dok_Od As Varchar(35)) + ''' '
   	If @Broj_Dok_Do Is NOT Null
 		SET @SSQL = @SSQL + 'AND D.Broj_Dok<=''' + Cast(@Broj_Dok_Do As Varchar(35)) + ''' '
	If @PecFisc = 'D'
	     Begin
		If @Datum_Dok_Od Is NOT Null
			SET @SSQL = @SSQL + 'AND D.Dat_Pec_Fisc>=''' + Cast(@Datum_Dok_Od As Varchar(35)) + ''' '
	   	If @Datum_Dok_Do Is NOT Null
 			SET @SSQL = @SSQL + 'AND D.Dat_Pec_Fisc<''' + Cast(@Datum_Dok_Do As Varchar(35)) + ''' '
	     End
	Else
	     Begin
		If @PoKojDatum = 'V'
		      Begin
			If @Datum_Dok_Od Is NOT Null				SET @SSQL = @SSQL + 'AND D.Datum_Vnes >= ''' + Cast(@Datum_Dok_Od As Varchar(35)) + ''' '
			If @Datum_Dok_Do Is NOT Null
	 			SET @SSQL = @SSQL + 'AND D.Datum_Vnes < ''' + Cast(@Datum_Dok_Do As Varchar(35)) + ''' '
		      End
		Else
		      Begin
			If @Datum_Dok_Od Is NOT Null
				SET @SSQL = @SSQL + 'AND D.Datum_Dok>=''' + Cast(@Datum_Dok_Od As Varchar(35)) + ''' '
		   	If @Datum_Dok_Do Is NOT Null
 				SET @SSQL = @SSQL + 'AND D.Datum_Dok<=''' + Cast(@Datum_Dok_Do As Varchar(35)) + ''' '
		      End
	     End
	If @Datum_Nal_Od Is NOT Null
	   	SET @SSQL = @SSQL + 'AND N.Datum_Nal>=''' + Cast(@Datum_Nal_Od As Varchar(35)) + ''' '
   	If @Datum_Nal_Do Is NOT Null
 		SET @SSQL = @SSQL + 'AND N.Datum_Nal<=''' + Cast(@Datum_Nal_Do As Varchar(35)) + ''' '
	If @Sifra_Kup Is NOT Null
		SET @SSQL = @SSQL + 'AND D.Sifra_Kup=''' + Cast(@Sifra_Kup As Varchar(35))+ ''' '
-- Ima kontrola vo podgotovkata ako se pusti filterot @NemaKomint da ne se pusta filterot @Sifra_Kup
	If @NemaKomint = 'D'
		SET @SSQL = @SSQL + 'AND D.Sifra_Kup Is Null and D.Sifra_za in (1, 2) ' 
	If @Sifra_Div IS NOT NULL
		SET @SSQL = @SSQL + ' AND D.Sifra_Div = ' + Cast(@Sifra_Div as varchar(5)) + ' '
-- Ima kontrola vo podgotovkata ako se pusti filterot @NemaDivizija da ne se pusta filterot @Sifra_Div
	If @NemaDivizija = 'D'
		SET @SSQL = @SSQL + 'AND D.Sifra_Div Is Null ' 
   	If @Sifra_Obj Is NOT Null
      		SET @SSQL = @SSQL + 'AND D.Sifra_Obj=' + Cast(@Sifra_Obj As Varchar(6)) + ' '
	If @Sifra_Za Is NOT Null
		SET @SSQL = @SSQL + 'AND D.Sifra_Za=''' + Cast(@Sifra_Za As Varchar(35))+ ''' '
	If @VlIzl Is NOT Null
		SET @SSQL = @SSQL + 'AND D.VlIzl=''' + Cast(@VlIzl As Varchar(5))+ ''' '
   	If @Sifra_Mest Is NOT Null
      		SET @SSQL = @SSQL + 'AND D.Sifra_Mest=''' + Cast(@Sifra_Mest As Varchar(10)) + ''' '
   	If @Uces_Od Is NOT Null
      		SET @SSQL = @SSQL + 'AND S.Uces >=''' + Cast(@Uces_Od As Varchar(10)) + ''' '
   	If @Uces_Do Is NOT Null
      		SET @SSQL = @SSQL + 'AND S.Uces <=''' + Cast(@Uces_Do As Varchar(10)) + ''' '
   	If @Sifra_Drg Is NOT Null
      		SET @SSQL = @SSQL + 'AND A.Sifra_Drg =''' + Cast(@Sifra_Drg As Varchar(10)) + ''' '
   	If @Lokacija IS NOT NULL
      		SET @SSQL=@SSQL+'AND (A.Lokacija='''+lTrim(rtrim(@Lokacija))+''') '
   	If @Sifra_Oper IS NOT NULL
      		SET @SSQL=@SSQL+'AND D.Sifra_Oper=''' + @Sifra_Oper + ''' '
   	If @Kto IS NOT NULL
      		SET @SSQL=@SSQL+'AND D.Kto=''' + @Kto + ''' '
   	If @Sifra_Prev IS NOT NULL
      		SET @SSQL=@SSQL+'AND D.Sifra_Prev=''' + @Sifra_Prev + ''' '
   	If @Spremil IS NOT NULL
      		SET @SSQL=@SSQL+'AND D.Spremil=''' + @Spremil + ''' '
	If @Koi_Gradovi Is Not NULL	
		Begin
		    Set @SSQL = @SSQL + ' And ((KO.Sifra_Grad Is Not Null And KO.Sifra_Grad In (' + @Koi_Gradovi + ')) 
			OR ( KO.Sifra_Grad Is Null and K.Sifra_Grad In ( ' + @Koi_Gradovi + '))) ' 	
		End
	If @KDrugo1 Is NOT NULL
		Set @SSQL = @SSQL + ' And ((KO.KDrugo1 Is Not Null And KO.KDrugo1 = ' + Cast(@KDrugo1 As Varchar(6)) + ')
			  Or (KO.KDrugo1 Is Null And K.KDrugo1 = ' + Cast(@KDrugo1 As Varchar(6)) + ')) '
	If @KDrugo2 Is NOT NULL
		Set @SSQL = @SSQL + ' And ((KO.KDrugo2 Is Not Null And KO.KDrugo2 = ' + Cast(@KDrugo2 As Varchar(6)) + ')
			  Or (KO.KDrugo2 Is Null And K.KDrugo2 = ' + Cast(@KDrugo2 As Varchar(6)) + ')) '
	If @KDrugo3 Is NOT NULL
		Set @SSQL = @SSQL + ' And ((KO.KDrugo3 Is Not Null And KO.KDrugo3 = ' + Cast(@KDrugo3 As Varchar(6)) + ')
			  Or (KO.KDrugo3 Is Null And K.KDrugo3 = ' + Cast(@KDrugo3 As Varchar(6)) + ')) '
	If @KDrugo4 Is NOT NULL
		Set @SSQL = @SSQL + ' And ((KO.KDrugo4 Is Not Null And KO.KDrugo4 = ' + Cast(@KDrugo4 As Varchar(6)) + ')
			  Or (KO.KDrugo4 Is Null And K.KDrugo1 = ' + Cast(@KDrugo4 As Varchar(6)) + ')) '
	If @KDrugo5 Is NOT NULL
		Set @SSQL = @SSQL + ' And ((KO.KDrugo5 Is Not Null And KO.KDrugo5 = ' + Cast(@KDrugo5 As Varchar(6)) + ')
			  Or (KO.KDrugo5 Is Null And K.KDrugo5 = ' + Cast(@KDrugo5 As Varchar(6)) + ')) '
	If @KDrugo6 Is NOT NULL
		Set @SSQL = @SSQL + ' And ((KO.KDrugo6 Is Not Null And KO.KDrugo6 = ' + Cast(@KDrugo6 As Varchar(6)) + ')
			  Or (KO.KDrugo6 Is Null And K.KDrugo6 = ' + Cast(@KDrugo6 As Varchar(6)) + ')) '
	If @KDrugo7 Is NOT NULL
		Set @SSQL = @SSQL + ' And ((KO.KDrugo7 Is Not Null And KO.KDrugo7 = ' + Cast(@KDrugo7 As Varchar(6)) + ')
			  Or (KO.KDrugo7 Is Null And K.KDrugo7 = ' + Cast(@KDrugo7 As Varchar(6)) + ')) '

	If @RezVoTabela <> 'D' Or @RezVoTabela Is Null
	Begin
		Set @SSQL = @SSQL + 'Group By D.Sifra_OE, D.Sifra_Dok, D.Broj_Dok, D.Sifra_Prim, D.VlIzl, D.Sifra_Za, D.Sifra_Nal, D.Broj_Nal, D.Identif_Br, D.Sifra_Kup, D.Sifra_Obj, 
					D.Datum_Dok, D.Rok, D.Kto, D.Toc200, D.MagVr, D.MagVrDan, D.Marza, D.Danok1, D.Danok2, D.PDanok1, D.PDanok2, D.PTrosok, D.DanDokCena, 
					D.Dat_Pec_Fisc, TD.ImeDok, K.ImeKup, K.SMesto, O.ImeOrg, OP.ImeOrg, KO.ImeObj, D.Plat_PerDan '
		If @PoGradovi = 'D'
			Set @SSQL = @SSQL + ', (Case When KO.Sifra_Grad Is Not Null Then KO.Sifra_Grad Else K.Sifra_Grad End), G.ImeGrad '
	End
	If @RezVoTabela = 'D'
	Begin
		Create Table ##AMD
		(
		 Sifra_OE	smallint, 
		 Sifra_Nal	smallint,
		 Broj_Nal	int, 
		 Datum_Dok	smalldatetime,
		 VlIzl		char(1), 
		 Toc200	decimal(18,6), 
		 MagVr		decimal(18,6), 
		 MagVrDan	decimal(18,6), 
		 Danok1	decimal(18,6), 
		 Danok2	decimal(18,6)
		) 
		Insert Into ##AMD Exec(@SSQL)
	End
	Else
		EXEC(@SSQL)


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

