if exists (select * from sys.procedures where name='sp_StokIslemleriListesi') drop proc sp_StokIslemleriListesi go ------------------------------------------------ CREATE PROCEDURE [dbo].[sp_StokIslemleriListesi] @tarih1 varchar(20), @tarih2 varchar(20), @dep varchar(500), @orderby int, @arama varchar(100), @GosterilecekKayit int, @IslemTuru varchar(20), @Durumu int, @IslemTipi varchar(30), @IslemNoLike bit = 0 , @FatSerNo varchar(3), @eFaturaNo varchar(30) --with encryption AS declare @str varchar(max) declare @rc int declare @tmp table (trc int) declare @ayrac varchar(2) declare @peFaturaNo varchar(50) if @eFaturaNo<>'' select @peFaturaNo=UPPER(@eFaturaNo) else set @peFaturaNo='' set @ayrac='"'; set @str='select count(*) from (select IslemNo from STOKHARDETBAG where FI = ''E'' and TP=''TF'' AND FaturaTarihi >='''+ @Tarih1+''' and FaturaTarihi <= '''+@Tarih2+''''; if @peFaturaNo<>'' set @str=@str+'and upper(eFaturaNo) like upper('''+@peFaturaNo+''') ' if @FatSerNo<>'' set @str=@str+'and Replace(FatSerNo,'''''''','''+@ayrac+''') = '''+@FatSerNo+''' ' if @dep <> '' set @str=@str+' and Departman in ('+@Dep+')' if @IslemTuru <> 'Tümü' set @str=@str+' and IslemTuru = '''+@IslemTuru+'''' if @IslemTipi <> 'Tümü' and @IslemTipi <> '' set @str=@str+' and IslemTipi like ''%'+@IslemTipi+'%''' if @Durumu = 1 set @str=@str+' and IslemTuru in (''Alış'',''Alış İade'',''Satış'',''Satış İade'') and FisTuru = ''I'' and AcikKapali=''A'' ' --Açıklar /* and not exists (select IslemNo from STOKHAR where STOKHAR.IslemNo3=STOKHARDETBAG.IslemNo)*/ if @Durumu = 2 set @str=@str+' and not(IslemTuru in (''Alış'',''Alış İade'',''Satış'',''Satış İade'') and FisTuru = ''I'' and AcikKapali=''A'') ' --Kapalılar if @Durumu = 3 set @str=@str+' and FisTuru > ''2''' --Stokİşlemi değilse if @arama <> '' begin if @orderby=0 begin if @IslemNoLike = 0 set @str=@str+'and CAST(IslemNo as nvarchar(20)) = '''+@arama+''' ' else set @str=@str+'and IslemNo like ''%'+@arama+'%'' ' end; if @orderby=1 set @str=@str+'and upper(CariAdi) like upper(''%'+@arama+'%'') ' if @orderby=2 set @str=@str+'and upper(FaturaNo) like upper('''+@arama+'%'') ' if @orderby=3 set @str=@str+'and upper(SiparisTarihi) like upper(''%'+@arama+'%'') ' if @orderby=4 set @str=@str+'and upper(IrsNo) like upper('''+@arama+'%'') ' if @orderby=5 set @str=@str+'and upper(IslemNo) like upper('''+@arama+'%'') ' end; set @str=@str+')Y'; insert @tmp exec(@str) select @rc = trc from @tmp; declare @tstr varchar(1000); if @orderby=0 set @tstr='IslemNo' if @orderby=1 set @tstr='CariAdi' if @orderby=2 set @tstr='FaturaNo' if @orderby=3 set @tstr='SiparisTarihi' if @orderby=4 set @tstr='IrsNo' if @orderby=5 set @tstr='IslemNo' set @str='select * from STOKHARDETBAG '+ 'where IslemNo in (select X.IslemNo '+ 'from (select ROW_NUMBER() over (order by '+@tstr+') as sira, IslemNo '+ 'from STOKHARDETBAG where FI = ''E'' and TP=''TF'' and FaturaTarihi >='''+@Tarih1+''' and FaturaTarihi <= '''+@Tarih2+''''; if @peFaturaNo<>'' set @str=@str+'and upper(eFaturaNo) like upper('''+@peFaturaNo+''') ' if @FatSerNo<>'' set @str=@str+'and Replace(FatSerNo,'''''''','''+@ayrac+''') = '''+@FatSerNo+''' ' if @dep <> '' set @str=@str+' and Departman in ('+@Dep+')' if @IslemTuru <> 'Tümü' set @str=@str+' and IslemTuru = '''+@IslemTuru+'''' if @IslemTipi <> 'Tümü' and @IslemTipi <> '' set @str=@str+' and IslemTipi = '''+@IslemTipi+'''' if @Durumu = 1 set @str=@str+' and IslemTuru in (''Alış'',''Alış İade'',''Satış'',''Satış İade'') and FisTuru = ''I'' and AcikKapali=''A'' ' --Açıklar /* and not exists (select IslemNo from STOKHAR where STOKHAR.IslemNo3=STOKHARDETBAG.IslemNo)*/ if @Durumu = 2 set @str=@str+' and not(IslemTuru in (''Alış'',''Alış İade'',''Satış'',''Satış İade'') and FisTuru = ''I'' and AcikKapali=''A'') ' --Kapalılar if @Durumu = 3 set @str=@str+' and FisTuru > ''2''' --Stokİşlemi değilse if @arama <> '' begin if @orderby=0 begin if @IslemNoLike = 0 set @str=@str+'and CAST(IslemNo as nvarchar(20)) = '''+@arama+''' ' else set @str=@str+'and IslemNo like ''%'+@arama+'%'' ' end; if @orderby=1 set @str=@str+'and upper(CariAdi) like upper(''%'+@arama+'%'') ' if @orderby=2 set @str=@str+'and upper(FaturaNo) like upper('''+@arama+'%'') ' if @orderby=3 set @str=@str+'and upper(SiparisTarihi) like upper(''%'+@arama+'%'') ' if @orderby=4 set @str=@str+'and upper(IrsNo) like upper('''+@arama+'%'') ' if @orderby=5 set @str=@str+'and upper(IslemNo) like upper('''+@arama+'%'') ' end; set @str=@str+')X where X.sira >'+convert(varchar,@rc - @GosterilecekKayit) +') order by '; if @orderby=0 set @str=@str+'IslemNo' if @orderby=1 set @str=@str+'CariAdi' if @orderby=2 set @str=@str+'FaturaNo' if @orderby=3 set @str=@str+'SiparisTarihi' if @orderby=4 set @str=@str+'IrsNo' if @orderby=5 set @str=@str+'IslemNo' --select @str as strislemsql exec(@str) GO ------------------------------------------------ if exists (select * from sys.procedures where name='sp_FaturaIslemleriListesi') drop proc sp_FaturaIslemleriListesi go ------------------------------------------------ CREATE PROCEDURE [dbo].[sp_FaturaIslemleriListesi] @tarih1 varchar(20), @tarih2 varchar(20), @dep varchar(500), @orderby int, @arama varchar(100), @GosterilecekKayit int, @IslemTuru varchar(20), @Durumu int, @IslemTipi varchar(30), @FatSerNo varchar(3), @eFaturaNo varchar(30), @FaturaTuru varchar(1), @IslemNoLike bit = 0 AS declare @str nvarchar(max) declare @rc int declare @tmp table (trc int) declare @ayrac varchar(2) declare @peFaturaNo varchar(50) if @eFaturaNo<>'' select @peFaturaNo=UPPER(@eFaturaNo) else set @peFaturaNo='' set @ayrac='"'; set @str='select count(*) from (select IslemNo from STOKHARDETBAG where FisTuru = ''F'' AND FaturaTarihi >='''+ @Tarih1+ ''' and FaturaTarihi <= '''+@Tarih2+''''; if @peFaturaNo<>'' set @str=@str+'and upper(eFaturaNo) like upper(''%'+@peFaturaNo+'%'') ' if @FatSerNo<>'' set @str=@str+'and Replace(FatSerNo,'''''''','''+@ayrac+''') = '''+@FatSerNo+''' ' if @dep <> '' set @str=@str+' and Departman in ('+@Dep+')' if @IslemTuru <> 'Tümü' set @str=@str+' and IslemTuru = '''+@IslemTuru+'''' if @IslemTipi <> 'Tümü' and @IslemTipi <> '' set @str=@str+' and IslemTipi like ''%'+@IslemTipi+'%''' if @arama <> '' begin if @orderby=0 begin if @IslemNoLike = 0 set @str=@str+'and CAST(IslemNo as nvarchar(20)) = '''+@arama+''' ' else set @str=@str+'and IslemNo like ''%'+@arama+'%'' ' end; if @orderby=1 set @str=@str+'and upper(FaturaNo) like upper('''+@arama+'%'') ' if @orderby=2 set @str=@str+'and upper(CariAdi) like upper(''%'+@arama+'%'') ' if @orderby=3 set @str=@str+'and upper(CariKodu) like upper('''+@arama+'%'') ' if @orderby=4 set @str=@str+'and upper(KULADI) like upper(''%'+@arama+'%'') ' if @orderby=5 set @str=@str+'and upper(SaticiAdi) like upper(''%'+@arama+'%'') ' if @orderby=6 set @str=@str+'and upper(eFaturaDurumu) like upper(''%'+@arama+'%'') ' if @orderby=7 set @str=@str+'and upper(YaziciCount) like upper(''%'+@arama+'%'') ' if @orderby=8 set @str=@str+'and upper(FaturaTarihi) like upper(''%'+@arama+'%'') ' if @orderby=9 set @str=@str+'and ((upper(Ack1) like upper(''%'+@arama+'%'')) or (upper(Ack2) like upper(''%'+@arama+'%'')) or (upper(Ack3) like upper(''%'+@arama+'%'')) or (upper(Ack4) like upper(''%'+@arama+'%'')) or EXISTS (Select 1 from ACARI AC where AC.ISLEMNO = IslemNo and upper(Aciklama2) like upper(''%'+@arama+'%'')) or EXISTS (Select 1 from ACIKLAMA AC where AC.FATURANO = IslemNo and upper(TeslimEden) like upper(''%'+@arama+'%'')) or EXISTS (Select 1 from ACIKLAMA AC where AC.FATURANO = IslemNo and upper(TeslimAlan) like upper(''%'+@arama+'%''))) ' end; set @str=@str+')Y'; insert @tmp exec(@str) select @rc = trc from @tmp; declare @tstr varchar(100); if @orderby in (0,9) set @tstr='IslemNo' if @orderby=1 set @tstr='FaturaNo' if @orderby=2 set @tstr='CariAdi' if @orderby=3 set @tstr='CariKodu' if @orderby=4 set @tstr='KULADI' if @orderby=5 set @tstr='SaticiAdi' if @orderby=6 set @tstr='eFaturaDurumu' if @orderby=7 set @tstr='YaziciCount' if @orderby=8 set @tstr='FaturaTarihi' set @str='select '''' as plaka, *, '+ 'CASE YazarKasaFisNo when 0 then ''TICARIFATURA'' '+ 'when 1 then ''TEMELFATURA'' '+ 'when 2 then ''IHRACAT'' else ''TICARIFATURA'' end + '' / ''+ case FaturaTipi when 0 then ''SATIS'' when 1 then ''TEVKIFAT'' when 2 then ''IADE'' when 3 then ''IHRACKAYITLI'' when 4 then ''ISTISNA'' '+ 'when 5 then ''MOBIL'' else ''SATIS'' end FaturaSenaryoTip '+ 'from STOKHARDETBAG '+ 'where IslemNo in (select X.IslemNo '+ 'from (select ROW_NUMBER() over (order by '+@tstr+') as sira, IslemNo '+ 'from STOKHARDETBAG where FisTuru = '''+@FaturaTuru+''' and FaturaTarihi >='''+@Tarih1+''' and FaturaTarihi <= '''+@Tarih2+''''; if @peFaturaNo<>'' set @str=@str+'and upper(eFaturaNo) like upper(''%'+@peFaturaNo+'%'') ' if @FatSerNo<>'' set @str=@str+'and Replace(FatSerNo,'''''''','''+@ayrac+''') = '''+@FatSerNo+''' ' if @dep <> '' set @str=@str+' and Departman in ('+@Dep+')' if @IslemTuru <> 'Tümü' set @str=@str+' and IslemTuru = '''+@IslemTuru+'''' if @IslemTipi <> 'Tümü' and @IslemTipi <> '' set @str=@str+' and IslemTipi = '''+@IslemTipi+'''' if @arama <> '' begin if @orderby=0 begin if @IslemNoLike = 0 set @str=@str+'and CAST(IslemNo as nvarchar(20)) = '''+@arama+''' ' else set @str=@str+'and IslemNo like ''%'+@arama+'%'' ' end; if @orderby=1 set @str=@str+'and upper(FaturaNo) like upper('''+@arama+'%'') ' if @orderby=2 set @str=@str+'and upper(CariAdi) like upper(''%'+@arama+'%'') ' if @orderby=3 set @str=@str+'and upper(CariKodu) like upper('''+@arama+'%'') ' if @orderby=4 set @str=@str+'and upper(KULADI) like upper(''%'+@arama+'%'') ' if @orderby=5 set @str=@str+'and upper(SaticiAdi) like upper(''%'+@arama+'%'') ' if @orderby=6 set @str=@str+'and upper(eFaturaDurumu) like upper(''%'+@arama+'%'') ' if @orderby=7 set @str=@str+'and upper(YaziciCount) like upper(''%'+@arama+'%'') ' if @orderby=8 set @str=@str+'and upper(FaturaTarihi) like upper(''%'+@arama+'%'') ' if @orderby=9 set @str=@str+'and ((upper(Ack1) like upper(''%'+@arama+'%'')) or (upper(Ack2) like upper(''%'+@arama+'%'')) or (upper(Ack3) like upper(''%'+@arama+'%'')) or (upper(Ack4) like upper(''%'+@arama+'%'')) or EXISTS (Select 1 from ACARI AC where AC.ISLEMNO = IslemNo and upper(Aciklama2) like upper(''%'+@arama+'%'')) or EXISTS (Select 1 from ACIKLAMA AC where AC.FATURANO = IslemNo and upper(TeslimEden) like upper(''%'+@arama+'%'')) or EXISTS (Select 1 from ACIKLAMA AC where AC.FATURANO = IslemNo and upper(TeslimAlan) like upper(''%'+@arama+'%''))) ' end; set @str=@str+')X where X.sira >'+convert(varchar,@rc - @GosterilecekKayit) +') order by '; if @orderby in (0,9) set @str=@str+'IslemNo' if @orderby=1 set @str=@str+'FaturaNo' if @orderby=2 set @str=@str+'CariAdi' if @orderby=3 set @str=@str+'CariKodu' if @orderby=4 set @str=@str+'KULADI' if @orderby=5 set @str=@str+'SaticiAdi' if @orderby=6 set @str=@str+'eFaturaDurumu' if @orderby=7 set @str=@str+'YaziciCount' if @orderby=8 set @str=@str+'FaturaTarihi' exec(@str) go ------------------------------------------------------------------------------------------- UPDATE StokVergiMuafiyetleri SET MuafiyetKodu = '0',MuafiyetAciklamasi = 'YOK' where ISNULL(MuafiyetKodu,'') = '' go ------------------------------------------------------------------------------------------- if exists (select * from sys.procedures where name='spStokFiyatDegistir') drop proc spStokFiyatDegistir go ------------------------------------------------------------------------------------------- CREATE PROCEDURE [dbo].[spStokFiyatDegistir] @GelenStokKodu nvarchar(30), @FiyatAdi nvarchar(30), @Fiyat float, @DovizKodu nvarchar(3), @Iskonto1 float = 0, @Iskonto2 float = 0, @Iskonto3 float = 0, @Iskonto4 float = 0 as begin DECLARE @DovizKuru float DECLARE @Query nvarchar(max),@Fyt nvarchar(3) DECLARE @ResultDovizKodu nvarchar(3) IF LEN(@FiyatAdi) = 7 SET @Fyt = SUBSTRING(@FiyatAdi,1,2) ELSE SET @Fyt = SUBSTRING(@FiyatAdi,1,3); if @DovizKodu ='' BEGIN SET @Query = 'select @DovizKod = '+@Fyt+'DovizKodu from StokFiyat where Kodu = '''+@GelenStokKodu+''''; EXEC sp_executesql @Query, N'@DovizKod nvarchar(3) OUTPUT', @DovizKod = @ResultDovizKodu OUTPUT ; SET @DovizKodu = @ResultDovizKodu; END; SET @DovizKuru = (Select GunFiyati from DOVIZT where DovizKodu = @DovizKodu) SET @Fiyat = @Fiyat / @DovizKuru; SET @Query = 'UPDATE StokFiyat SET '+@Fyt+'Fiyat = (CASE WHEN '+@Fyt+'Kdv = ''H'' THEN '+CAST(@Fiyat as nvarchar(50))+' ELSE ('+CAST(@Fiyat as nvarchar(50))+'*(100+SatisKdv))/100 END), '+@Fyt+'DovizKodu='''+@DovizKodu+''' where Kodu =''' + @GelenStokKodu + ''''; EXEC sp_executesql @Query; SET @Query = 'UPDATE StokFiyat SET '+@Fyt+'FiyatH = (CASE WHEN '+@Fyt+'Kdv = ''D'' THEN '+CAST(@Fiyat as nvarchar(50))+' ELSE ('+CAST(@Fiyat as nvarchar(50))+'*(100+SatisKdv))/100 END) where Kodu = '''+@GelenStokKodu+''''; EXEC sp_executesql @Query; IF @Fyt in ('F1','F2','F3','F4') BEGIN /*SET @Query = 'UPDATE STOKKARTI SET '+@Fyt+'Fiyat = '+CAST(@Fiyat as nvarchar(50))+', '+@Fyt+'DovizKodu='''+@DovizKodu+''' where Kodu =''' + @GelenStokKodu+''''; EXEC sp_executesql @Query; */ UPDATE sk SET sk.F1Fiyat = sf.F1Fiyat, sk.F1DovizKodu = sf.F1DovizKodu, sk.F2Fiyat = sf.F2Fiyat, sk.F2DovizKodu = sf.F2DovizKodu, sk.F3Fiyat = sf.F3Fiyat, sk.F3DovizKodu = sf.F3DovizKodu, sk.F4Fiyat = sf.F3Fiyat, sk.F4DovizKodu = sf.F3DovizKodu, sk.AlisIndirimYuzde = (CASE WHEN @Iskonto1 > 0 THEN @Iskonto1 ELSE sk.AlisIndirimYuzde END), sk.AlisIndirimYuzde2 = (CASE WHEN @Iskonto2 > 0 THEN @Iskonto2 ELSE sk.AlisIndirimYuzde2 END), sk.AlisIndirimYuzde3 = (CASE WHEN @Iskonto3 > 0 THEN @Iskonto3 ELSE sk.AlisIndirimYuzde3 END), sk.AlisIndirimYuzde4 = (CASE WHEN @Iskonto4 > 0 THEN @Iskonto4 ELSE sk.AlisIndirimYuzde4 END) FROM STOKKARTI sk,StokFiyat sf WHERE sk.Kodu = sf.Kodu and sk.Kodu = @GelenStokKodu END; end; go ------------------------------------------------------------------------------------- declare @CNTNAME NVARCHAR(1000),@SQL NVARCHAR(MAX) select @CNTNAME=name from sys.default_constraints where name like 'DF__ACARI__Update_Da%' SET @SQL='ALTER TABLE [dbo].[ACARI] DROP CONSTRAINT ['+ CONVERT(VARCHAR(100),@CNTNAME)+']' EXEC(@SQL) go ------------------------------------------------------------------------------------- if not exists (select * from dbo.syscolumns where id = object_id(N'ACARI') and name=N'Update_Date') begin alter table ACARI ALTER COLUMN Update_Date DATETIME end GO ------------------------------------------------------------------------------------- --ALTER TABLE [dbo].[ACARI] ADD DEFAULT (getdate()) FOR [Update_Date] --GO ------------------------------------------------------------------------------------- if not exists (select * from dbo.syscolumns where id = object_id(N'ASABIT') and name=N'T1') begin ALTER TABLE ASABIT ADD T1 int end GO ------------------------------------------------------------------------------------- if not exists (select * from dbo.syscolumns where id = object_id(N'ASABIT') and name=N'T2') begin ALTER TABLE ASABIT ADD T2 int end GO ------------------------------------------------------------------------------------- UPDATE ASABIT SET T1=8 , T2=12 go ------------------------------------------------------------------------------------- if exists (select * from sys.procedures where name='sp_mb_XML_InvoiceLine_List_delphi') drop proc sp_mb_XML_InvoiceLine_List_delphi go ------------------------------------------------------------------------------------- create proc [dbo].[sp_mb_XML_InvoiceLine_List_delphi] @IslemNo nvarchar(30) as ;with stokdetbag AS (SELECT DB.IslemNo, CASE WHEN ISNULL (DD.Kdv,0)=0 AND DB.OzelKod1='31' AND DB.YazarKasaFisNo<>2 THEN '351;Vergiden muaf.' WHEN ISNULL (DD.Kdv,0)=0 AND DB.OzelKod1='31' AND DB.YazarKasaFisNo=2 THEN '11/1-a Mal ihracatı' when SUBSTRING(ISNULL(DB.OzelKod1,'00'), 1, 1)=3 and ISNULL (DD.Kdv,0)=0 and DB.EF_ISTISNA=0 then '701;11/1-c md. Kapsamındaki İhraç Kayıtlı Satış' when DD.TopTutar=0 and DB.EF_ISTISNA=0 then '351;Kısmibedelsiz' WHEN ISNULL (DD.Kdv,0)=0 AND (select MuafiyetKodu+';'+MuafiyetAciklamasi from StokVergiMuafiyetleri where StokKodu=DD.StokKodu and MuafiyetKodu=DB.EF_ISTISNA and MuafiyetKodu<>'000')=';' THEN '351;Vergiden muaf.' WHEN ISNULL (DD.Kdv,0)=0 AND (select MuafiyetKodu+';'+MuafiyetAciklamasi from StokVergiMuafiyetleri where StokKodu=DD.StokKodu and MuafiyetKodu=DB.EF_ISTISNA and MuafiyetKodu<>'000')<>';' THEN (select MuafiyetKodu+';'+MuafiyetAciklamasi from StokVergiMuafiyetleri where StokKodu=DD.StokKodu and MuafiyetKodu=DB.EF_ISTISNA) ELSE '' END AS "MUAFIYET" from STOKHARDETBAG DB join STOKHAR DD ON DB.IslemNo in (DD.IslemNo,DD.IslemNo3) WHERE @IslemNo in (DD.IslemNo, DD.IslemNo3)) SELECT ROW_NUMBER() OVER (ORDER BY LST.StokKodu ) "SiraNo" , LST.StokKodu, LST.StokAdi, LST.IskOran, --LST.IskOran, LST.Kdv, LST.Birim, LST.ISO_BIRIM, MAX(LST.MUAFIYET) "MUAFIYET", round((LST.ToplamBirimFiyat),4)ToplamBirimFiyat, SUM(LST.Miktar) Miktar, round(SUM(LST.KdvsizSatirToplami) ,2)KdvsizSatirToplami, round(SUM(LST.SatirIskontoToplami),2) SatirIskontoToplami, round(SUM(LST.Isksuz_Satir_Toplami),2) Isksuz_Satir_Toplami, round(SUM(LST.KdvToplami) ,2) KdvToplami, round(SUM(LST.Alt_Iskonto_Toplami),2) Alt_Iskonto_Toplami, round(SUM(LST.Tutar),2) Tutar, round(SUM(LST.SonTutar),2) SonTutar, LST.Iskonto_Aciklama, round(LST.OTVOrani,2) OTVOrani, round(sum(LST.OTVsizSatirToplami),2) OTVsizSatirToplami, round(sum(LST.OtvToplami),2) OtvToplami FROM( SELECT DD.StokKodu, DD.StokAdi AS StokAdi, (FaturaMiktari) as Miktar, (SonTutar-DD.KdvTutari)/ (DovizKuru) as KdvsizSatirToplami, (SonTutar-DD.OTVTutari) /(DovizKuru) as OTVsizSatirToplami, DD.Iskonto1 AS IskOran, ((FaturaMiktari* BirimFiyat*BirimMiktar) -DD.Tutar ) /(DovizKuru) as SatirIskontoToplami, (FaturaMiktari* BirimFiyat*BirimMiktar) /(DovizKuru) as Isksuz_Satir_Toplami,--tutar DD.Kdv, DD.OTVOrani, (DD.KdvTutari) / (DovizKuru) as KdvToplami, (DD.OTVTutari) / (DovizKuru) as OtvToplami, (Birim) as Birim, (DD.Tutar -(SonTutar-DD.KdvTutari)) / (DovizKuru) as Alt_Iskonto_Toplami, (DD.Tutar) / DovizKuru as Tutar, (SonTutar) / DovizKuru as SonTutar, (DD.BirimFiyat*DD.BirimMiktar) / (DovizKuru) AS ToplamBirimFiyat,--Fiyat (SELECT Top 1 MUAFIYET FROM stokdetbag b where b.IslemNo in (DD.IslemNo, DD.IslemNo3) and isnull(MUAFIYET,'')<>'' ) AS "MUAFIYET", ( SELECT IsoBirimKodu FROM BIRIM b where b.BirimAdi=DD.Birim ) AS ISO_BIRIM, CASE WHEN DD.Iskonto1 >0 then '%'+CAST(DD.Iskonto1 as varchar(10)) end + CASE WHEN DD.Iskonto2 >0 then '%'+CAST(DD.Iskonto2 as varchar(10)) else '' end+ CASE WHEN DD.Iskonto3 >0 then '%'+CAST(DD.Iskonto3 as varchar(10)) else '' end+ CASE WHEN DD.Iskonto4 >0 then '%'+CAST(DD.Iskonto4 as varchar(10)) else '' end as "Iskonto_Aciklama" from STOKHAR DD --CROSS APPLY STOKHARDETBAG DB ON DB.IslemNo in (DD.IslemNo,DD.IslemNo3) where (DD.IslemTuru in ('Satış' ,'Alış İade' ) AND @IslemNo in (DD.IslemNo, DD.IslemNo3)) --AND DD.Tutar >0 )LST GROUP BY LST.StokKodu, LST.StokAdi, LST.IskOran, LST.Kdv, LST.Birim, LST.ISO_BIRIM, LST.ToplamBirimFiyat, Iskonto_Aciklama, LST.OTVOrani; go ------------------------------------------------------------------------------------- if exists (select * from sys.procedures where name='sp_Kart_Bakim') drop proc sp_Kart_Bakim go ------------------------------------------------------------------------------------- create proc sp_Kart_Bakim as update StokBarkodlari set AnaBirim=0 where AnaBirim is null or AnaBirim='' or AnaBirim<>1 UPDATE StokBarkodlari SET Barkodu=StokKodu WHERE Barkodu IN ('',' ',NULL) ----ÇİFT KAYIT STOKKARTLARI LİSTESİNİ VERİR. --select * from STOKKARTI SK1 WHERE Kodu in (SELECT Kodu FROM STOKKARTI group by Kodu having count(Kodu)>1) -- AND NoId =(SELECT MIN(NoId) FROM STOKKARTI SK3 where SK3.Kodu=SK1.Kodu) --select * from STOKKARTI SK1 WHERE Kodu in (SELECT Kodu FROM STOKKARTI group by Kodu having count(Kodu)>1) --ÇİFT KAYITLARDAN İLK AÇILANLARI SİLER. EN SON KAYIT KALIR. DELETE from STOKKARTI WHERE Kodu in (SELECT Kodu FROM STOKKARTI group by Kodu having count(Kodu)>1) AND NoId <>(SELECT max(NoId) FROM STOKKARTI SK3 where SK3.Kodu=STOKKARTI.Kodu) --Stokfiyat Kodu 1 den fazla olan kayıtlardan son kayıt hariç siler DELETE from StokFiyat WHERE Kodu in (SELECT Kodu FROM StokFiyat group by Kodu having count(Kodu)>1) AND NoId <>(SELECT max(NoId) FROM StokFiyat SK3 where SK3.Kodu= StokFiyat.Kodu) --StokBarkodu 1 den fazla olan kayıtlardan son kayıt hariç siler DELETE from StokBarkodlari WHERE Barkodu in (SELECT Barkodu FROM StokBarkodlari group by Barkodu having count(Barkodu)>1) AND StokBarkodlariNoId <>(SELECT max(StokBarkodlariNoId) FROM StokBarkodlari SK3 where SK3.Barkodu= StokBarkodlari.Barkodu) --stokkartı olmayan stokfiyat'-Stokbarkodlari silme ---Tabloda karşılılğı olmayanları silme !!! DELETE FROM StokVergiMuafiyetleri WHERE NOT EXISTS (SELECT * FROM STOKKARTI WHERE Kodu=StokVergiMuafiyetleri.StokKodu) DELETE FROM StokFiyat WHERE NOT EXISTS (SELECT * FROM STOKKARTI WHERE Kodu=StokFiyat.Kodu) DELETE FROM StokBarkodlari WHERE NOT EXISTS (SELECT * FROM STOKKARTI WHERE Kodu=StokBarkodlari.StokKodu) DELETE FROM StokDB WHERE NOT EXISTS (SELECT * FROM STOKKARTI WHERE Kodu=StokDB.StokKodu) --Birden fazla BirimAdı olan kayıtları temizler. İlk kayıt kalacak diğerleri gidecek. delete from BIRIM WHERE BirimNo in (select BirimNo from BIRIM B1 WHERE BirimAdi in (SELECT BirimAdi FROM BIRIM group by BirimAdi having count(BirimAdi)>1) AND BirimNo <>(SELECT MIN(BirimNo) FROM BIRIM B3 where B3.BirimAdi=B1.BirimAdi)) GO ------------------------------------------------------------------------------------- if exists (select * from sys.procedures where name='sp_SN_Bakim') drop proc sp_SN_Bakim go ------------------------------------------------------------------------------------- create proc sp_SN_Bakim as exec sp_Grup_Bakim exec sp_StokDoviz_Bakim exec sp_StokFiyat_Bakim exec sp_StokFiyatBakim exec sp_Hareket_Bakim exec sp_Kart_Bakim go ------------------------------------------------------------------------------------- if exists (select * from sys.procedures where name='sp_StokDoviz_Bakim') drop proc sp_StokDoviz_Bakim go ------------------------------------------------------------------------------------- create proc sp_StokDoviz_Bakim as UPDATE STOKHAR SET DovizKuru = (CASE WHEN DovizKodu in ('YP', 'TL') or SonTutar = 0 or SonTutarDov = 0 THEN 1 ELSE SonTutar / SonTutarDov END) UPDATE STOKHARDETBAG SET DovizKur = (CASE WHEN DovizKodu in ('YP', 'TL') or Tutar = 0 or DovizTut = 0 THEN 1 ELSE Tutar / DovizTut END) UPDATE ACARI SET DovizKur = 1 where DOVIZKODU in ('YP', 'TL') UPDATE KASA SET DovizKur = 1 where DovizKodu in ('YP', 'TL') UPDATE ACARI SET DovizKur = (CASE WHEN BORC >0 AND DovizBorc > 0 THEN BORC / DovizBorc WHEN ALACAK > 0 and DovizAlacak > 0 THEN ALACAK / DovizAlacak ELSE 1 END) UPDATE TEKLIFHAR SET DovizKur = (CASE WHEN DovizKodu in ('YP', 'TL') or SonTutar = 0 or SonTutarDov = 0 THEN 1 ELSE SonTutar / SonTutarDov END) UPDATE TEKLIFHARDETBAG SET DovizKur = (CASE WHEN DovizKodu in ('YP', 'TL') or Tutar = 0 or DovizTut = 0 THEN 1 ELSE Tutar / DovizTut END) UPDATE SIPARISHAR SET DovizKur = (CASE WHEN DovizKodu in ('YP', 'TL') or SonTutar = 0 or SonTutarDov = 0 THEN 1 ELSE SonTutar / SonTutarDov END) UPDATE SIPARISHARDETBAG SET DovizKur = (CASE WHEN DovizKodu in ('YP', 'TL') or Tutar = 0 or DovizTut = 0 THEN 1 ELSE Tutar / DovizTut END) UPDATE KASA SET DovizKur = (CASE WHEN BORC > 0 and DovizBorc > 0 THEN BORC / DovizBorc WHEN ALACAK > 0 and DovizAlacak > 0 THEN ALACAK / DovizAlacak ELSE 1 END) select 'STH', DovizKodu,DovizKuru,max(FaturaTarihi) mxTarih, min(FaturaTarihi) mnTrh from STOKHAR group by DovizKodu,DovizKuru UNION select 'STHDB',DovizKodu,DovizKur,max(FaturaTarihi) mxTarih, min(FaturaTarihi) mnTrh from STOKHARDETBAG group by DovizKodu,DovizKur UNION select 'ACARI',DOVIZKODU,DovizKur,max(TARIH) mxTarih, min(TARIH) mnTrh from ACARI group by DOVIZKODU,DovizKur UNION select 'KASA',DovizKodu,DovizKur,max(TARIH) mxTarih, min(TARIH) mnTrh from KASA group by DovizKodu,DovizKur ORDER BY 1,2,3 select 'Kasa_DovizKodu_olmayanlar',DovizKodu,DovizKur,ISLEMNO from KASA where DovizKodu not in (select DovizKodu from DOVIZT ) UNION select 'Cari_DovizKodu_olmayanlar',DOVIZKODU ,DovizKur,ISLEMNO from ACARI where DOVIZKODU not in (select DovizKodu from DOVIZT ) UNION select 'STOKHAR_DovizKodu_olmayanlar',DovizKodu,DovizKuru,IslemNo from STOKHAR where DovizKodu not in (select DovizKodu from DOVIZT ) UNION select 'STOKHARDETBAG_DovizKodu_olmayanlar',DovizKodu,DovizKuru,IslemNo from STOKHAR where DovizKodu not in (select DovizKodu from DOVIZT ) go ------------------------------------------------------------------------------------- if exists (select * from sys.procedures where name='sp_Grup_Bakim') drop proc sp_Grup_Bakim go ------------------------------------------------------------------------------------- CREATE PROCEDURE sp_Grup_Bakim AS BEGIN INSERT INTO GRUP (GrupAdi,Turu,KULNO,KULADI) select DISTINCT UreticiFirmaKodu,'U',1,'Admin' from STOKKARTI where UreticiFirmaKodu <> '' and UreticiFirmaKodu is not null and NOT EXISTS (Select 1 from GRUP where GrupAdi = UreticiFirmaKodu and Turu = 'U') -- Grup Adı insert INSERT INTO GRUP (GrupAdi,Turu,KULNO,KULADI) select DISTINCT GrupAdi,'G',1,'Admin' from STOKKARTI sk where GrupAdi <> '' and GrupAdi is not null and NOT EXISTS (Select 1 from GRUP g where g.GrupAdi = sk.GrupAdi and g.Turu = 'G') -- Cari Tip İnsert INSERT INTO GRUP (GrupAdi,Turu,KULNO,KULADI) select DISTINCT Tipi,'T',1,'Admin' from AMUSTERI m where Tipi <> '' and Tipi is not null and NOT EXISTS (Select 1 from GRUP g where g.GrupAdi = m.Tipi and g.Turu = 'T') -- Cari O1 İnsert INSERT INTO GRUP (GrupAdi,Turu,KULNO,KULADI) select DISTINCT OZELKOD1,'O1',1,'Admin' from AMUSTERI m where OZELKOD1 <> '' and OZELKOD1 is not null and NOT EXISTS (Select 1 from GRUP g where g.GrupAdi = m.OZELKOD1 and g.Turu = 'O1') -- Cari O2 İnsert INSERT INTO GRUP (GrupAdi,Turu,KULNO,KULADI) select DISTINCT OZELKOD2,'O2',1,'Admin' from AMUSTERI m where OZELKOD2 <> '' and OZELKOD2 is not null and NOT EXISTS (Select 1 from GRUP g where g.GrupAdi = m.OZELKOD2 and g.Turu = 'O2') -- Cari O3 İnsert INSERT INTO GRUP (GrupAdi,Turu,KULNO,KULADI) select DISTINCT OZELKOD3,'O3',1,'Admin' from AMUSTERI m where OZELKOD3 <> '' and OZELKOD3 is not null and NOT EXISTS (Select 1 from GRUP g where g.GrupAdi = m.OZELKOD3 and g.Turu = 'O3') -- Cari O4 İnsert INSERT INTO GRUP (GrupAdi,Turu,KULNO,KULADI) select DISTINCT OZELKOD4,'O4',1,'Admin' from AMUSTERI m where OZELKOD4 <> '' and OZELKOD4 is not null and NOT EXISTS (Select 1 from GRUP g where g.GrupAdi = m.OZELKOD4 and g.Turu = 'O4') END; go ------------------------------------------------------------------------------------- if exists (select * from sys.procedures where name='sp_StokFiyat_Bakim') drop proc sp_StokFiyat_Bakim go ------------------------------------------------------------------------------------- CREATE PROCEDURE sp_StokFiyat_Bakim AS begin declare @i int,@sorgu nvarchar(max),@cnt nvarchar(2) set @i = 1; while @i <= 20 begin SET @cnt = CAST(@i as varchar(2)); SET @sorgu = ' UPDATE StokFiyat SET F'+@cnt+'Fiyat = CASE WHEN F'+@cnt+'Kdv = ''H'' THEN F'+@cnt+'FiyatH ELSE (F'+@cnt+'FiyatH*(100 + SatisKdv)/100) END'; -- print @sorgu EXEC sp_executesql @sorgu; SET @i += 1; end; update sk SET sk.F1Fiyat = sf.F1Fiyat, sk.F2Fiyat = sf.F2Fiyat, sk.F3Fiyat = sf.F3Fiyat, sk.F4Fiyat = sf.F4Fiyat from STOKKARTI sk,StokFiyat sf where sk.Kodu = sf.Kodu end; go ------------------------------------------------------------------------------------- if exists (select * from sys.procedures where name='spDevirBakim') drop proc spDevirBakim go ------------------------------------------------------------------------------------- CREATE PROC spDevirBakim @DBE nvarchar(30) as --exec('use '+@DBE) -- spDevirBakim 'Ent_MLTSBS' exec('update '+@DBE+'.dbo.STOKHAR set StokKodu = (select top 1 Kodu from '+@DBE+'.dbo.STOKKARTI where STOKKARTI.StokNo = STOKHAR.StokNo or STOKKARTI.StokAdi = STOKHAR.StokAdi) where StokKodu in ('''','' '',null)') delete from STOKHAR where StokKodu in ('',' ',null) GO ------------------------------------------------------------------------------------- if exists (select * from sys.procedures where name='sp_StokFiyatBakim') drop proc sp_StokFiyatBakim go ------------------------------------------------------------------------------------- CREATE PROC sp_StokFiyatBakim as --Update STOKHAR SET Iskonto1 = 0 where Iskonto1 IS NULL declare @1 nvarchar(max) declare @2 nvarchar (max) declare @i int declare @s int set @i=1 set @s=10 WHILE @i <= @s begin set @1 ='Update STOKHAR SET Iskonto' + convert(varchar,@i)+'= 0 where Iskonto' + convert(varchar,@i)+' IS NULL' exec (@1) SET @i = @i + 1 end --- Update STOKHAR SET IndirimTutari = 0 where IndirimTutari IS NULL Update STOKHAR SET EkIskTutTop = 0 where EkIskTutTop IS NULL Update STOKHAR SET IndirimTutari = 0 where IndirimTutari IS NULL Update STOKHAR SET SatirIskTut = 0 where SatirIskTut IS NULL ---EkIskTut1-3 - AltIskYuzde1-3 alanlarına update set @i=1 set @s=3 WHILE @i <= @s begin set @1 ='Update STOKHAR SET EkIskTut'+convert(varchar,@i)+' = 0 where EkIskTut'+convert(varchar,@i)+' IS NULL' set @2 ='Update STOKHAR SET AltIskYuzde'+convert(varchar,@i)+' = 0 where AltIskYuzde'+convert(varchar,@i)+' IS NULL' exec (@1) exec (@2) SET @i = @i + 1 end --- Update StokFiyat SET F1DovizKodu = 'YP' where F1DovizKodu IS NULL set @i=1 set @s=20 WHILE @i <= @s begin set @1 ='Update StokFiyat SET F'+ convert(varchar,@i)+'DovizKodu = ''YP'' where F'+ convert(varchar,@i)+'DovizKodu IS NULL' exec (@1) SET @i = @i + 1 end --- GO ------------------------------------------------------------------------------------- if exists (select * from sys.procedures where name='sp_Hareket_Bakim') drop proc sp_Hareket_Bakim go ------------------------------------------------------------------------------------- CREATE PROCEDURE sp_Hareket_Bakim AS DELETE FROM STOKHAR WHERE Miktar in (NULL,'',' ') DELETE FROM SIPARISHAR WHERE Miktar in (NULL,'',' ') DELETE FROM TEKLIFHAR WHERE Miktar in (NULL,'',' ') ---DEPO VİRMANDAN GELEN İŞLEMNO OLMAYAN KAYITLAR İÇİN UPDATE STOKHAR SET IslemNo=IslemNo-1 where IslemNo in( select IslemNo from STOKHAR SH WHERE NOT EXISTS (SELECT * FROM STOKHARDETBAG SD WHERE SD.IslemNo=SH.IslemNo) AND IslemTuru='Depo Vir.') --STOKHAR OLUP STOKHARDETBAG OLMAYAN KAYITLARI GÖRMEK İÇİN select IslemNo from STOKHAR SH WHERE NOT EXISTS (SELECT * FROM STOKHARDETBAG SD WHERE SD.IslemNo=SH.IslemNo) --STOKHAR OLUP STOKHARDETBAG OLMAYAN KAYITLARI SİLMEK İÇİN DELETE FROM STOKHAR WHERE IslemNo IN (select IslemNo from STOKHAR SH WHERE NOT EXISTS (SELECT * FROM STOKHARDETBAG SD WHERE SD.IslemNo=SH.IslemNo) AND IslemTuru<>'Depo Vir.' ) --Birden fazla Kaydı olan BirimAdını listeler --select BirimNo from BIRIM B1 WHERE BirimAdi in (SELECT BirimAdi FROM BIRIM group by BirimAdi having count(BirimAdi)>1) -- AND BirimNo <>(SELECT MIN(BirimNo) FROM BIRIM B3 where B3.BirimAdi=B1.BirimAdi) go