Logo Version: TIGER 3 2.73
Örnek Firma Nr: 104
Öncelikle Sysye girilip firma tanımlarında silinmesi isdenen firmaların dönemleri çıkarılır ve sonrasında firma çıkar yapılır.
Sonrasında sqlden filtre yapılarak viewler ve tablolar silinmelidir(önce viewler silinir );
Viewler:
Tablolar:
Not: Hassas bir işlem olduğundan muhakkak yedek alınız. Sorumluluk size aittir…
SORUN: CARİ – MALZEME – BANKA HESAP HAREKET GÖRMEMESİNE RAĞMEN SİLİNEMEMESİ. (LOGO GO3)
TRACE KONTROLLERİ YAPILARAK İLGİLİ TABLOLARIN REFERANSLARI KONTROL EDİLDİ VE KAYIT YOKDU:
UPDATE LG_122_CLCARD SET USEDINPERIODS='0' WHERE ( LOGICALREF = 575)
UPDATE LG_122_ITEMS SET USEDINPERIODS='0' WHERE ( LOGICALREF = 1999)
UPDATE LG_122_BANKACC SET USEDINPERIODS='0' WHERE ( LOGICALREF = 4)
YUKARDAKİ KODLAR ÇALIŞTIRILIP KONTROLLER SAĞLANDI…
LOGO Tigerda tabloları sürüme göre güncellerken alınan LV_TRDGRP collation hatası(view kontrolü sonrası tespit edilen – sorunu yaratan tablo alanı: LG_XXX_XX_CLFLINE – TIGER3 VERSION 2.75) :
SQL_Latin1_General_CP1254_Ci_AS
Bendeki çözümü: sql serverde collationı değiştirmek oldu
SELECT TOP 100 PERCENT
X.Ay,
X.Gun,
X.Ambar,
x.Stok_AktifPasif,
x.Stok_Kodu,
x.Stok_Adı,
x.MalzemeAciklamasi2,
x.MalzemeAciklamasi3,
x.MalzemeAciklamasi4,
x.[Bırım],
x.Malzeme_Turu,
x.GrupKodu,
x.OzelKod,
x.OzelKod2,
x.OzelKod3,
x.OzelKod4,
x.OzelKod5,
GunHareketMiktar =SUM([Miktar]),
Stok = round((select sum(CAST((CASE WHEN S.IOCODE IN (1, 2) THEN 1 ELSE -1 END) * S.AMOUNT * (CASE WHEN ISNULL(UINFO2, 0) = 0 THEN 1 ELSE UINFO2 END) / (CASE WHEN ISNULL(UINFO1, 0) = 0 THEN 1 ELSE UINFO1 END) AS float))
from LG_211_01_STLINE S where S.STFICHEREF IN (SELECT STF.LOGICALREF FROM LG_211_01_STFICHE STF
WHERE
STF.LOGICALREF = S.STFICHEREF AND STF.CANCELLED=0) AND S.STOCKREF=X.ITEMLOGICALREF AND S.DATE_<=X.Gun AND S.SOURCEINDEX=X.SOURCEINDEX AND S.CANCELLED = 0 AND S.LINETYPE = 0 AND S.IOCODE IN (1, 2, 3, 4) AND S.LPRODSTAT = 0 ),4)
FROM (SELECT
STLINE.SOURCEINDEX,
ITEMLOGICALREF=STLINE.STOCKREF,
Ay=CAST(YEAR(STFICHE.DATE_) AS VARCHAR)+(CASE WHEN LEN(MONTH(STFICHE.DATE_))=1 THEN '0'+CAST(MONTH(STFICHE.DATE_) AS VARCHAR) ELSE CAST(MONTH(STFICHE.DATE_) AS VARCHAR) END),
Gun=STLINE.DATE_,
STFICHE.FICHENO,
Ambar = (SELECT
CAST(NR AS varchar) + '-' + NAME
FROM L_CAPIWHOUSE CAPIWHOUSE
WHERE CAPIWHOUSE.NR = STLINE.SOURCEINDEX
AND CAPIWHOUSE.FIRMNR = 211),
Isyeri = (SELECT
CAST(NR AS varchar) + '-' + NAME
FROM L_CAPIDIV CAPIDIV
WHERE CAPIDIV.NR = STFICHE.BRANCH
AND CAPIDIV.FIRMNR = 211),
STFICHE.LOGICALREF [STFICHE_REFERANS],
Stok_AktifPasif =case when ITEMS.ACTIVE=0 then 'Aktif' else 'Pasif' end,
Stok_Kodu = ITEMS.CODE,
Stok_Adı = ITEMS.NAME,
MalzemeAciklamasi2 = ITEMS.NAME2,
MalzemeAciklamasi3 = ITEMS.NAME3,
MalzemeAciklamasi4 = ITEMS.NAME4,
[Bırım] = BIRIM.CODE,
Malzeme_Turu =
CASE ITEMS.CARDTYPE
WHEN 1 THEN 'TICARI MAL'
WHEN 2 THEN 'KARMA KOLI'
WHEN 3 THEN 'DEPOZITOLU MAL'
WHEN 4 THEN 'SABIT KIYMET'
WHEN 10 THEN 'HAMMADDE'
WHEN 11 THEN 'YARI MAMUL'
WHEN 12 THEN 'MAMUL'
WHEN 13 THEN 'TUKETIM MALI'
WHEN 20 THEN 'GENEL MALZEME SINIFI'
WHEN 21 THEN 'TABLOLU MALZEME SINIFI'
ELSE CONVERT(varchar(20), CARDTYPE)
END,
GrupKodu = ITEMS.STGRPCODE,
OzelKod = ITEMS.SPECODE,
OzelKod2 = ITEMS.SPECODE2,
OzelKod3 = ITEMS.SPECODE3,
OzelKod4 = ITEMS.SPECODE4,
OzelKod5 = ITEMS.SPECODE5,
[Miktar] = CAST((CASE
WHEN STLINE.IOCODE IN (1, 2) THEN 1
ELSE -1
END) * STLINE.AMOUNT * (CASE
WHEN ISNULL(UINFO2, 0) = 0 THEN 1
ELSE UINFO2
END) / (CASE
WHEN ISNULL(UINFO1, 0) = 0 THEN 1
ELSE UINFO1
END) AS float)
FROM LG_211_01_STFICHE STFICHE WITH (NOLOCK)
LEFT OUTER JOIN LG_211_01_STLINE STLINE WITH (NOLOCK)
ON STFICHE.LOGICALREF = STLINE.STFICHEREF
LEFT OUTER JOIN LG_211_ITEMS ITEMS WITH (NOLOCK)
ON STLINE.STOCKREF = ITEMS.LOGICALREF
LEFT OUTER JOIN LG_211_UNITSETL BIRIM WITH (NOLOCK)
ON BIRIM.UNITSETREF = ITEMS.UNITSETREF
AND BIRIM.MAINUNIT = 1
WHERE STFICHE.CANCELLED = 0
AND STLINE.CANCELLED = 0
AND STLINE.LINETYPE = 0
AND STLINE.IOCODE IN (1, 2, 3, 4)
AND STLINE.LPRODSTAT = 0
AND YEAR(STLINE.DATE_)>=2021 AND MONTH(STLINE.DATE_)=10 ) AS X
GROUP BY
X.Ay,
X.Gun,
X.Ambar,
x.Stok_AktifPasif,
x.SOURCEINDEX,
x.ITEMLOGICALREF,
x.Stok_Kodu,
x.Stok_Adı,
x.MalzemeAciklamasi2,
x.MalzemeAciklamasi3,
x.MalzemeAciklamasi4,
x.Bırım,
x.Malzeme_Turu,
x.GrupKodu,
x.OzelKod,
x.OzelKod2,
x.OzelKod3,
x.OzelKod4,
x.OzelKod5
ORDER BY X.Stok_Kodu , X.Ambar, X.Gun
SELECT
X.Isyeri,
X.Ambar,
x.Stok_Kodu,
x.Stok_Adı,
x.MalzemeAciklamasi2,
x.MalzemeAciklamasi3,
x.MalzemeAciklamasi4,
x.[Bırım],
x.Malzeme_Turu,
x.GrupKodu,
x.OzelKod,
x.OzelKod2,
x.OzelKod3,
x.OzelKod4,
x.OzelKod5,
Miktar = ROUND(SUM(x.[Miktar]), 4)
FROM (SELECT
Ambar = (SELECT
CAST(NR AS varchar) + '-' + NAME
FROM L_CAPIWHOUSE CAPIWHOUSE
WHERE CAPIWHOUSE.NR = STLINE.SOURCEINDEX
AND CAPIWHOUSE.FIRMNR = 211),
Isyeri = (SELECT
CAST(NR AS varchar) + '-' + NAME
FROM L_CAPIDIV CAPIDIV
WHERE CAPIDIV.NR = STFICHE.BRANCH
AND CAPIDIV.FIRMNR = 211),
STFICHE.LOGICALREF [STFICHE_REFERANS],
Stok_Kodu = ITEMS.CODE,
Stok_Adı = ITEMS.NAME,
MalzemeAciklamasi2 = ITEMS.NAME2,
MalzemeAciklamasi3 = ITEMS.NAME3,
MalzemeAciklamasi4 = ITEMS.NAME4,
[Bırım] = BIRIM.CODE,
Malzeme_Turu =
CASE ITEMS.CARDTYPE
WHEN 1 THEN 'TICARI MAL'
WHEN 2 THEN 'KARMA KOLI'
WHEN 3 THEN 'DEPOZITOLU MAL'
WHEN 4 THEN 'SABIT KIYMET'
WHEN 10 THEN 'HAMMADDE'
WHEN 11 THEN 'YARI MAMUL'
WHEN 12 THEN 'MAMUL'
WHEN 13 THEN 'TUKETIM MALI'
WHEN 20 THEN 'GENEL MALZEME SINIFI'
WHEN 21 THEN 'TABLOLU MALZEME SINIFI'
ELSE CONVERT(varchar(20), CARDTYPE)
END,
GrupKodu = ITEMS.STGRPCODE,
OzelKod = ITEMS.SPECODE,
OzelKod2 = ITEMS.SPECODE2,
OzelKod3 = ITEMS.SPECODE3,
OzelKod4 = ITEMS.SPECODE4,
OzelKod5 = ITEMS.SPECODE5,
[Miktar] = CAST((CASE
WHEN STLINE.IOCODE IN (1, 2) THEN 1
ELSE -1
END) * STLINE.AMOUNT * (CASE
WHEN ISNULL(UINFO2, 0) = 0 THEN 1
ELSE UINFO2
END) / (CASE
WHEN ISNULL(UINFO1, 0) = 0 THEN 1
ELSE UINFO1
END) AS float)
FROM LG_211_01_STFICHE STFICHE WITH (NOLOCK)
LEFT OUTER JOIN LG_211_01_STLINE STLINE WITH (NOLOCK)
ON STFICHE.LOGICALREF = STLINE.STFICHEREF
LEFT OUTER JOIN LG_211_ITEMS ITEMS WITH (NOLOCK)
ON STLINE.STOCKREF = ITEMS.LOGICALREF
LEFT OUTER JOIN LG_211_UNITSETL BIRIM WITH (NOLOCK)
ON BIRIM.UNITSETREF = ITEMS.UNITSETREF
AND BIRIM.MAINUNIT = 1
WHERE STFICHE.CANCELLED = 0
AND STLINE.CANCELLED = 0
AND STLINE.LINETYPE = 0
AND STLINE.IOCODE IN (1, 2, 3, 4)
AND STLINE.LPRODSTAT = 0
AND STLINE.DATE_ <= GETDATE()) AS X
GROUP BY X.Isyeri,
X.Ambar,
x.Stok_Kodu,
x.Stok_Adı,
x.MalzemeAciklamasi2,
x.MalzemeAciklamasi3,
x.MalzemeAciklamasi4,
x.Bırım,
x.Malzeme_Turu,
x.GrupKodu,
x.OzelKod,
x.OzelKod2,
x.OzelKod3,
x.OzelKod4,
x.OzelKod5
Not: İşyerinin olması akıl karıştırabilir(toplamda bu ambarda şu stok olmalı gibi carpraz kotnrol yapılırsa logodan stok bazen farklı işyerinden düşünce toplam diğer işyerinde olduğu için) dolayısıyla select ve group bydan kaldırılırsa daha sağlıklı kontrol sağlanabilir (ambar şu stok şu)
select *
from (select *,
row_number() over(partition by URUN_NO order by BASLANGIC_TARIHI desc) as rn
from BM_T_V_FIYAT_TANIM_BILGI WITH(NOLOCK)) as T
where rn = 1
ÖRNEK:
--CREATE PROCEDURE [dbo].[P_HTML]
--as
declare @bodyOzet varchar(max)
declare @bodyDetay varchar(max)
/*
CREATE TABLE [dbo].[HTML](
[HTMLCODE] [nvarchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
--DOSYAYA KAYDETMESI ICIN AYAR ACILMALI
sp_configure 'show advanced options', '1'
RECONFIGURE
GO
-- this enables xp_cmdshell
sp_configure 'xp_cmdshell', '1'
RECONFIGURE
GO
--DOSYAYA KAYDETME KOMUTU
declare @sql varchar(8000)
select @sql = 'bcp "select * from [DATABASE].[dbo].HTML" queryout E:\SONUC.html -c -t, -T -S' + @@servername
exec master..xp_cmdshell @sql
--ISLEM SORGUSU
exec dbo.P_HTML
*/
set @bodyDetay = cast( (
select td = cast(day(Tarih) as varchar)+'/'+cast(month(Tarih) as varchar)+'/'+cast(Year(Tarih) as varchar)+'</td><td>' + UrunKodu + '</td><td>' + UrunAdi + '</td><td>' + Barkod + '</td><td>' + cast( Miktar as varchar(30) ) + '</td><td>' + cast( SatirNetTutar as varchar(30) )
from (
select top 100 percent
Tarih= (select SF.DATE FROM StockFiches SF WHERE SF.Id=SL.StockFicheId),
UrunKodu= (select P.Code FROM Products P WHERE P.Id=SL.StockId),
UrunAdi= (select P.ProductName FROM Products P WHERE P.Id=SL.StockId),
Barkod= (select P.Barcode FROM Barcodes P WHERE P.ProductId=SL.StockId),
Miktar= SL.Amount,
SatirNetTutar=SL.LineNet
from StockLines SL
) as d order by Tarih desc
for xml path( 'tr' ), type ) as varchar(max) )
set @bodyDetay = N'Malzeme Bazlı Satışlar ('+cast(GETDATE() as varchar)+') <table cellpadding="2" cellspacing="0" border="1">'
+ '<tr><th>Tarih</th><th>Ürün Kodu</th><th>Ürün Adı</th><th>Barkod</th><th>Miktar</th><th>Tutar</th></tr>'
+ replace( replace( @bodyDetay, '<', '<' ), '>', '>' )
+ '</table>'
/*aşağısı özet*/
set @bodyOzet = cast( (
select td = cast(day(Tarih) as varchar)+'/'+cast(month(Tarih) as varchar)+'/'+cast(Year(Tarih) as varchar)+'</td><td>' + OdemeTuru + '</td><td>' + Cast(Toplam as Varchar) + '</td><td>' + Cast(IndirimToplam as Varchar) + '</td><td>' + cast( NetToplam as varchar(30) )
from (
select Tarih,OdemeTuru,Sum(Toplam) Toplam, Sum(IndirimToplam) IndirimToplam,Sum(NetToplam) NetToplam from (
SELECT
CONVERT(DATE, F.CreatedDate) AS Tarih
, CASE F.IO WHEN 0 THEN L.Total ELSE 0 - L.Total END AS Toplam
, CASE F.IO WHEN 0 THEN L.TotalDiscount ELSE 0 - L.TotalDiscount END AS IndirimToplam
, CASE F.IO WHEN 0 THEN L.LineNet ELSE 0 - L.LineNet END AS NetToplam
, L.VatInc KDVDH
, L.Vat KDV
, CASE F.PayType
WHEN 0 THEN 'Nakit Kasa'
WHEN 1 THEN 'Banka Pos'
WHEN 2 THEN 'Kredi Kartı'
WHEN 3 THEN 'İkram'
END OdemeTuru
FROM StockLines L
Left Join StockFiches F ON F.Id = L.StockFicheId
WHERE
F.FicheType in (0,1)
) as t group by Tarih,OdemeTuru
) as d order by Tarih desc
for xml path( 'tr' ), type ) as varchar(max) )
set @bodyOzet = N'Özet Bazlı Satışlar ('+cast(GETDATE() as varchar)+') <table cellpadding="2" cellspacing="0" border="1">'
+ '<tr><th>Tarih</th><th>Odeme Turu</th><th>Toplam</th><th>Indirim Toplam</th><th>NetToplam</th></tr>'
+ replace( replace( @bodyOzet, '<', '<' ), '>', '>' )
+ '</table><br>' + @bodyDetay
truncate table HTML
insert into HTML(HTMLCODE) VALUES(@bodyOzet)
GO
/*birinci yöntem logo ve sql server üzerinden:
hangi firma silinecekse dönemi silinir sonra firması silinir
örnek ilk önce 531li viewleri sil
sonrada tablolardan 531 sil
*/
/*İŞLEM ÖNCESİ VERİTABANININ YEDEK ALINMASI TAVSİYE EDİLİR*/
/*ÖNCE SYSDEN İLGİLİ FİRMANIN DÖNEMİ ÇIKARILIR VE SONRASINDA FİRMA ÇIKAR YAPILIR*/
/*AŞAĞIDAKİ İŞLEMLERE FİRMAYA BAĞLI VİEW,TABLO,PROSEDUR,FONKSIYONLARI SİLMEKTEDİR*/
DECLARE @firmno varchar(3)='999' /*İŞLEM YAPILMASI İSTENİLEN FİRMA NOSU YAZ*/
DECLARE @view_name SYSNAME
DECLARE @table_name SYSNAME
DECLARE @procedure_name SYSNAME
DECLARE @function_name SYSNAME
/*VIEWLERI SIL BAŞLANGIÇ*/
DECLARE Logo_objs CURSOR FOR
SELECT name
FROM sysobjects
WHERE (name LIKE 'LV_' + @firmno + '%' AND xtype='V' )
ORDER BY NAME
OPEN Logo_objs
FETCH NEXT FROM Logo_objs INTO @view_name
WHILE @@FETCH_STATUS = 0
BEGIN
EXECUTE('DROP VIEW '+ @view_name)
FETCH NEXT FROM Logo_objs into @view_name
END
CLOSE Logo_objs
DEALLOCATE Logo_objs
/*VIEWLERI SIL BİTİŞ*/
/*TABLOLARI SIL BAŞLANGIÇ*/
DECLARE Logo_objs CURSOR FOR
SELECT name
FROM sysobjects
WHERE (name LIKE 'LG_' + @firmno + '%' AND XTYPE = 'U')
ORDER BY NAME
OPEN Logo_objs
FETCH NEXT FROM Logo_objs INTO @table_name
WHILE @@FETCH_STATUS = 0
BEGIN
EXECUTE('DROP TABLE '+ @table_name)
FETCH NEXT FROM Logo_objs into @table_name
END
CLOSE Logo_objs
DEALLOCATE Logo_objs
/*TABLOLARI SIL BİTİŞ*/
/*PROSEDURLERİ SIL BAŞLANGIÇ*/
DECLARE Logo_objs CURSOR FOR
SELECT name
FROM sysobjects
WHERE (name LIKE '%' + @firmno + '%' AND XTYPE = 'P')
ORDER BY NAME
OPEN Logo_objs
FETCH NEXT FROM Logo_objs INTO @procedure_name
WHILE @@FETCH_STATUS = 0
BEGIN
EXECUTE('DROP PROCEDURE '+ @procedure_name)
FETCH NEXT FROM Logo_objs into @procedure_name
END
CLOSE Logo_objs
DEALLOCATE Logo_objs
/*PROSEDURLERİ SIL BİTİŞ*/
/*FONKSİYONLARI SIL BAŞLANGIÇ*/
DECLARE Logo_objs CURSOR FOR
SELECT name
FROM sys.sql_modules m
INNER JOIN sys.objects o ON m.object_id=o.object_id
WHERE (name like 'LG_' + @firmno + '%' AND type ='FN')
ORDER BY NAME
OPEN Logo_objs
FETCH NEXT FROM Logo_objs INTO @function_name
WHILE @@FETCH_STATUS = 0
BEGIN
EXECUTE('DROP FUNCTION '+ @function_name)
FETCH NEXT FROM Logo_objs into @function_name
END
CLOSE Logo_objs
DEALLOCATE Logo_objs
/*FONKSİYONLARI SIL BİTİŞ*/
SELECT
OGRENCI_NO= CLCARD.CODE ,
OGRENCI_ADI= CLCARD.DEFINITION_,
DOVIZ_TURU= CASE CCURRENCY WHEN 0 THEN 'TL' WHEN 160 THEN 'TL' WHEN 1 THEN 'USD' WHEN 17 THEN 'GBP' WHEN 20 THEN 'EUR' ELSE '' END,
/*TL_BAKIYE= ROUND((SUM((1 - CLFLINE.SIGN) * CLFLINE.AMOUNT) - SUM(CLFLINE.SIGN * CLFLINE.AMOUNT)),2),
RD_BAKIYE= ROUND((SUM((1 - CLFLINE.SIGN) * (CLFLINE.AMOUNT/NULLIF(CLFLINE.REPORTRATE,0)))
-
SUM(CLFLINE.SIGN * (CLFLINE.AMOUNT/NULLIF(CLFLINE.REPORTRATE,0)))),2),*/
/*BAKIYE= CASE WHEN CCURRENCY IN (0,160) THEN
ROUND((SUM((1 - CLFLINE.SIGN) * CLFLINE.AMOUNT) - SUM(CLFLINE.SIGN * CLFLINE.AMOUNT)),2)
ELSE
ROUND((SUM((1 - CLFLINE.SIGN) * (CLFLINE.AMOUNT/NULLIF(CLFLINE.REPORTRATE,0)))
-
SUM(CLFLINE.SIGN * (CLFLINE.AMOUNT/NULLIF(CLFLINE.REPORTRATE,0)))),2)
END ,*/
BAKIYE= CASE WHEN CCURRENCY IN (0,160) THEN
ROUND((SUM((1 - CLFLINE.SIGN) * CLFLINE.AMOUNT) - SUM(CLFLINE.SIGN * CLFLINE.AMOUNT)),2)
ELSE
ROUND((SUM((1 - CLFLINE.SIGN) * (CLFLINE.REPORTNET))
-
SUM(CLFLINE.SIGN * (CLFLINE.REPORTNET))),2)
END
FROM
LG_118_01_CLFLINE CLFLINE INNER JOIN
LG_118_CLCARD CLCARD ON CLFLINE.CLIENTREF = CLCARD.LOGICALREF AND CLFLINE.CANCELLED = 0
WHERE CLCARD.CYPHCODE='UNI' AND CLFLINE.CANCELLED=0
and CLFLINE.DATE_<='09/01/2020' /*ay/gun/yıl*/
GROUP BY CLCARD.CCURRENCY, CLCARD.CODE, CLCARD.DEFINITION_, CLCARD.ACTIVE
HAVING (CLCARD.CODE LIKE '%') and (CLCARD.DEFINITION_ LIKE '%')
AND (CLCARD.ACTIVE = 0) ORDER BY CLCARD.CODE DESC
SELECT DISTINCT M.P_KODU,
M.CARI_KODU,M.CARI_ADI,M.GUN,
(
SELECT TOP 1 WP.DATE_ FROM WOR_PROCESS WP ,WOR_USERS WU WHERE WP.FIRM = '121'
AND WU.APPTYPE=1 AND WP.USERID=WU.USERID
AND ( WP.DATE_ BETWEEN ? AND ? )
AND WU.USERNAME=M.P_KODU AND WP.CLCODE=M.CARI_KODU
ORDER BY WP.DATE_ DESC
) SON_GIDILEN_TARIH,
(SELECT W2.FNAME FROM WOR_USERS W2 WHERE W2.CODE=M.P_KODU) AS P_ADI
FROM E_WINGS_121_SCS3 M WHERE 1=1
Blog of Sem 