home
***
CD-ROM
|
disk
|
FTP
|
other
***
search
/
95.86.62.111
/
95.86.62.111.tar
/
95.86.62.111
/
85
/
wtrg
/
sp_AnalMatDok.sql
< prev
next >
Wrap
Text File
|
2009-02-24
|
13KB
|
278 lines
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