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
Talep: Logo object designer uyarlamada Logo satınalma faturasına, satır eğer malzeme ise ilgili malzemenin tablosundan özelkod4 (specode4) getirme talebi.
İşlem(Tiger 3 2.71) :
Önce InvoiceGrid grid componentine tıklayıp colattribs içinden MalzemeOzelKodu eklendi:
Formshow eventi:
Sub FormShow()
MGridName="InvoiceGrid"
MaxGridLine=MGridLines("InvoiceGrid")-1
for i=0 to MaxGridLine
GetGridRowHandle("InvoiceGrid",i,recH)
AppTables[9].GetIntFieldR(recH,"STOCKREF",intSTOCKREF)
AppTables[9].GetIntFieldR(recH,"LINETYPE",intLineType)
if intSTOCKREF>0 and intLineType=0 then
str(intSTOCKREF,STRSTOCKREF)
TableName = Application.GetTableName(1, Application.CompanyId, Application.FiscPerdId)
SPECODE4= G_GETTOP1TORESULT(TableName,false,4 ,"SPECODE4","LOGICALREF="+STRSTOCKREF)
SetCellText(MGridName,i,10071,SPECODE4)
end if
next i
End Sub
Controlexit eventi(malzeme kodu / malzeme açıklamasına girince):
Sub ControlExit(ctrl as String, index as Integer)
If ctrl="InvoiceGrid" and (Index=2 or Index=3) Then
MGridName="InvoiceGrid"
GridRow=MGridRow(MGridName)
GetCellText(MGridName,GridRow,16,TUR)
if TUR="Malzeme" Then
GetCellText(MGridName,GridRow,1,KODU)
TableName = Application.GetTableName(1, Application.CompanyId, Application.FiscPerdId)
SPECODE4= G_GETTOP1TORESULT(TableName,false,4 ,"SPECODE4","CODE='"+KODU+"'")
SetCellText(MGridName,GridRow,10071,SPECODE4)
end if
END if
End Sub
G_GETTOP1RESULT fonksiyonu(Ben Globalde kullanıyorum)
Function G_GETTOP1TORESULT(TableName as String,isCustTable as bool,RESULTFIELDTYPE as int ,SelectField as String,Where as String)
' Fielttype Alan türü. 1 Integer 2 Date(Logo formatı tarih ) 3 Float 4 Text
'TableName = Application.GetTableName(TableId, Application.CompanyId, Application.FiscPerdId)
'TableName = Application.GetCustTableName(1, Application.CompanyId, Application.FiscPerdId)
CreateQuery(MyQry1)
MyQryTable1=TableName
MySqlTxt1 = "Select top 1 "+SelectField+" From " + MyQryTable1 + " WITH(NOLOCK) WHERE "+Where
MyQry1.SetSQLText(MySqlTxt1)
If MyQry1.ExecuteDirect() Then
Res = MyQry1.First()
if Res = 0 then
if RESULTFIELDTYPE=4 then
FResult = ""
else
FResult = 0
end if
end if
Do While Res=1
MyQry1.GetFieldValue(1, RESULTFIELDTYPE, FResult)
Res=MyQry1.Next()
Loop
End if
MyQry1.Clear()
result=FResult
End Function
Not: Eğer tiger tarafında lodda eklenen kolon görünmez ise bu işlem yapılır (upgrade formsa tıklanır) test edilebilir(Her adımın yedekleme yaparak ilerlenmesini tavsiye ederim):
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
Collation sorunundan dolayı karşıma çıktı:
Ayrıca yine veritabanı altında Programmability->Store Procedures altından getParameters ve WOR_ ile başlayan prosedürleri, Functions->Scalar-Valued Functions altından GetWebOrderDateInt, Views altındadan VGetdate viewini siliniz. Sildikten sonra yeniden tabloların oluşturulmasını sağlayabilirsiniz?(Mobile Sales Genel Ayarlar açılarak veritabanı tablolarını ekleyebilirsiniz.)
bunlarıda sil:
spWebOrderParseBarkod
splitString
/*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 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
SENARYO
İŞLEYİŞ
CREATE VIEW CARIRISKSIPARISKAPAT_210 AS
SELECT * FROM (
SELECT
CLLOGICALREF= C.LOGICALREF,
[Cari Kodu]= C.CODE,
[Cari Ünvanı]= C.DEFINITION_,
[İşlem]= ISNULL((
SELECT TOP 1
CASE WHEN DATEDIFF(DAY,P.DATE_,GETDATE())>=50 THEN 'Siparişe Kapat' END ISLEM
FROM LG_210_01_PAYTRANS P WITH(NOLOCK)
WHERE
P.MODULENR=4 AND P.TRCODE IN (7,8) AND P.PAID=0 AND P.CARDREF=C.LOGICALREF AND P.CANCELLED=0 ORDER BY P.DATE_ ASC
),''),
[Siparişte]= ISNULL((SELECT
CASE CLR.ORDRISKOVER
WHEN 0 THEN 'İşleme Devam Edilecek'
WHEN 1 THEN 'Kullanıcı Uyarılacak'
WHEN 2 THEN 'İşlem Durdurulacak'
END FROM LG_210_01_CLRNUMS CLR WITH(NOLOCK) WHERE CLR.CLCARDREF=C.LOGICALREF),'')
FROM LG_210_CLCARD C WITH(NOLOCK) WHERE ACTIVE=0
) AS T WHERE T.İşlem<>'' and Siparişte not like '%Durdurulacak'
/*JOB SORGUSU UPDATE İŞLEMİ*/
SET QUOTED_IDENTIFIER ON
UPDATE LG_210_01_CLRNUMS SET ORDRISKOVER = 2
WHERE CLCARDREF IN (SELECT B.CLLOGICALREF FROM CARIRISKSIPARISKAPAT_210 B )
SELECT
T2.TUR,T2.BRANCH ISYERI_NR
,
(select CONVERT(VARCHAR,NR)+', '+NAME from L_CAPIDIV WITH(NOLOCK) WHERE FIRMNR=210 and NR=T2.BRANCH) ISYERI
,
(SELECT C.CODE FROM LG_210_CLCARD C WITH(NOLOCK) WHERE C.LOGICALREF=CARDREF) CARI_KOD
,
(SELECT C.DEFINITION_ FROM LG_210_CLCARD C WITH(NOLOCK) WHERE C.LOGICALREF=CARDREF) CARI_AD
,
CEK_NO,ISLEM_TARIHI,VADE_TARIHI,CEK_DOVIZ,TUTAR,KAPAMA_TUTAR,KARSI_ISLEM_BILGILERI
FROM (
SELECT DISTINCT
*,
CASE T.TRCURR WHEN 0 THEN 'TL' WHEN 160 THEN 'TL' WHEN 1 THEN 'USD' WHEN 17 THEN 'GBP' WHEN 20 THEN 'EUR' ELSE CONVERT(VARCHAR,T.TRCURR) END CEK_DOVIZ
,
(SELECT SUM(P.TOTAL) FROM BM_210_01_PAYTRANS P WITH(NOLOCK) WHERE P.BRANCH=T.BRANCH AND P.TRCURR=T.TRCURR AND
P.CARDREF=T.CARDREF AND P.FICHEREF=T.PAYTRANS_FICHEREF AND P.FICHELINEREF=T.PAYTRANS_FICHELINEREF ) TUTAR
,
ISNULL((SELECT SUM(P.PAID) FROM BM_210_01_PAYTRANS P WITH(NOLOCK) WHERE P.BRANCH=T.BRANCH AND P.TRCURR=T.TRCURR AND
P.CARDREF=T.CARDREF AND P.FICHEREF=T.PAYTRANS_FICHEREF AND P.FICHELINEREF=T.PAYTRANS_FICHELINEREF AND PAID<>0),0) KAPAMA_TUTAR
,
ISNULL((
STUFF(
(SELECT ','+dbo.PAYTRANS2ISLEMTURU(PP.TRCODE,PP.MODULENR)+'['+ CAST(DAY(PP.PROCDATE) AS varchar)+'.'+CAST(MONTH(PP.PROCDATE) AS varchar) +'.'+CAST(YEAR(PP.PROCDATE) AS varchar)+'] /'+format((ROUND(PP.PAID,2)),N'','tr-TR') FROM LG_210_01_PAYTRANS PP WITH(NOLOCK) WHERE PP.LOGICALREF IN ((SELECT P.CROSSREF FROM BM_210_01_PAYTRANS P WITH(NOLOCK) WHERE P.BRANCH=T.BRANCH AND P.TRCURR=T.TRCURR AND
P.CARDREF=T.CARDREF AND P.FICHEREF=T.PAYTRANS_FICHEREF AND P.FICHELINEREF=T.PAYTRANS_FICHELINEREF ))
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'),
1,
1,
''
)
),'') KARSI_ISLEM_BILGILERI
FROM (
SELECT
dbo.BM_PAYTRANS2ISLEMTURU(P.TRCODE,P.MODULENR) TUR,
P.BRANCH,
P.TRCURR,
P.CARDREF ,
P.FICHEREF PAYTRANS_FICHEREF, /*CSTRANS.ROLLREF*/
P.FICHELINEREF PAYTRANS_FICHELINEREF , /*CSTRANS.LOGICALREF*/
(SELECT CSCARD.NEWSERINO FROM LG_210_01_CSCARD CSCARD WITH(NOLOCK) WHERE CSCARD.LOGICALREF=
(SELECT CST.CSREF FROM LG_210_01_CSTRANS CST WITH(NOLOCK) WHERE CST.ROLLREF=P.FICHEREF AND CST.LOGICALREF=P.FICHELINEREF)) CEK_NO,
P.PROCDATE ISLEM_TARIHI,
P.DATE_ VADE_TARIHI
FROM BM_210_01_PAYTRANS P WITH(NOLOCK) WHERE P.MODULENR=6/*CEKLER*/ /* AND P.CARDREF=5571 */
/*AND YEAR(DATE_)=2021 AND MONTH(DATE_)=4 AND DAY(DATE_)=8*/
) AS T
) AS T2
Blog of Sem 