LOGO OBJECT İLE SORGU İŞLEMİ

On May 9, 2024 By semt20In LOGO ERP, LOGO OBJECTSLeave a comment

TIGER 3 ENTERPRISE

NEDEN İHTİYAÇ DUYULDU: LOGO SYSDE FİRMALARA FARKLI VERİTABANLARI ÜZERİNDEN İŞLEM YAPILABİLMEKTE. DOLAYISIYLA İLGİLİ VERİTABANINDAN OBJECT İLE SORGU KONTROLÜ YAPILIP ONA GÖRE DEVAM EDİLMESİ GEREKMEKTE(PROJE TABLOSU).

ÖNCE SORGUYU CEKMEK İSDEDİGİM TABLO İSMİ TESPİT EDİLİR-GetTableName(https://docs.logo.com.tr/public/wua/logo-objects/logo-objects-kuetuephanesi/unityapplication/gettablename)

string projectTable = AppUnity.GetTableName(229, firmNr, 0);
bool isProjectExists = ObjectProjectCodeControl(projectTable, L.PROJEKODU);
bool canContinue = true;
                        if (!isProjectExists)
                        {
                            canContinue = false;
                            WRITELOG(firmNr.ToString() + " PROJE KODU BULUNAMADI : " + L.PROJEKODU, null);
                        }
private static bool ObjectProjectCodeControl(string table, string code)
{
    bool isExists = false;
    string sql = "SELECT LOGICALREF FROM " + table + " WHERE CODE = '" + code + "'";
    string resultTxt = "";
    UnityObjects.Query Qry = AppUnity.NewQuery();
    Qry.Statement = sql;
    if (Qry.OpenDirect())
    {
        bool res = Qry.First();
        while (res)
        {
            resultTxt = Qry.QueryFields[0].Value.ToString();
            isExists = true;
            res = Qry.Next();
        }
    }
    else
    {
        MessageBox.Show(Qry.DBErrorDesc.ToString());
    }
    Qry.Close();
    return isExists;
}

Protected: SQL İLE TAHSİLATTAN BORÇ TAKİP KAPATMA(KISMİ DAHİL)

On May 2, 2024May 3, 2024 By semt20In LOGO ERP, Sql

Apache Tomcat servis durdur başlat (Logo Hr)

On November 16, 2023 By semt20In LOGO ERPLeave a comment

taskkill /IM “Tomcat8.exe” /T /F
ping -n 6 127.0.0.1 > nul
net start “Tomcat8”

LOGO Banka İşlem Fişi Kesilmiştir Detay

On October 18, 2023 By semt20In LOGO ERP, LOGO OBJECTS, SqlLeave a comment

Logoda kk fişi silerken banka işlem fişi kesilmiştir hatası veren fişlerin bağlarını bulmak için :

 SELECT * FROM (
 SELECT 
	DISTINCT
	Tarih,
	CariFisKKFisNo=(SELECT CLFICHE.FICHENO FROM LG_301_01_CLFICHE CLFICHE WHERE CLFICHE.LOGICALREF=CariFisKKFisRef),
	CariFisMuhasebeFisNo=(SELECT EMFICHE.FICHENO FROM LG_301_01_EMFICHE EMFICHE WHERE EMFICHE.LOGICALREF=CariFisMuhasebeFisRef),
	BankaFisNo,
	BankaFisMuhasebeFisNo=(SELECT EMFICHE.FICHENO FROM LG_301_01_EMFICHE EMFICHE WHERE EMFICHE.LOGICALREF=BankaFisMuhasebeFisRef)
 FROM (
	 SELECT 
	 Tarih=BNFICHE.DATE_,
	 CariFisKKFisRef=BNFLINE.CRCARDFCREF,
	 CariFisMuhasebeFisRef=(SELECT CLFICHE.ACCFICHEREF FROM LG_301_01_CLFICHE CLFICHE WHERE CLFICHE.LOGICALREF=(CASE WHEN ISNULL(BNFICHE.CRCARDFCREF,'')=0 THEN BNFLINE.CRCARDFCREF ELSE BNFICHE.CRCARDFCREF END)),
	 BankaFisNo=BNFICHE.FICHENO,
	 BankaFisMuhasebeFisRef=(SELECT EMFICHE.LOGICALREF FROM LG_301_01_EMFICHE EMFICHE WHERE EMFICHE.LOGICALREF=BNFICHE.ACCFICHEREF)
	 FROM 
		LG_301_01_BNFICHE BNFICHE LEFT JOIN LG_301_01_BNFLINE BNFLINE ON BNFLINE.SOURCEFREF=BNFICHE.LOGICALREF
	  WHERE BNFICHE.TRCODE=1 AND BNFICHE.MODULENR=7
  ) AS TT
  ) AS TF WHERE CariFisKKFisNo='BMS.0073921'

LOGO TAHSİL EDİLMEMİŞ ÇEK RAPORU

On September 22, 2023 By semt20In LOGO ERP, SqlLeave a comment

SELECT TOP (100) PERCENT LG_300_01_PAYTRANS_1.PROCDATE AS [MAK.T.],
	dbo.LG_300_CLCARD.CODE AS [CH KOD],
	LEFT(dbo.LG_300_CLCARD.DEFINITION_, 40) AS [CH ACIKLAMA],
	LEFT(dbo.LG_300_01_INVOICE.FICHENO, 11) AS [FAT.NO.],
	dbo.LG_300_01_PAYTRANS.PROCDATE AS [FAT.T.],
	LG_300_01_PAYTRANS_1.DATE_ AS [MAK.V.],
	LEFT(dbo.LG_300_01_INVOICE.NETTOTAL, 10) AS [FAT.(TL)],
	DATEDIFF(DAY, dbo.LG_300_01_PAYTRANS.PROCDATE, LG_300_01_PAYTRANS_1.DATE_) AS GUN,
	MONTH(dbo.LG_300_01_INVOICE.DATE_) AS AY,
	CASE LG_300_01_PAYTRANS.TRCURR
		WHEN 0
			THEN (
					CASE LG_300_01_PAYTRANS_1.CROSSTOTAL
						WHEN 0
							THEN (
									CASE LG_300_01_PAYTRANS.TRCURR
										WHEN 0
											THEN (dbo.LG_300_01_PAYTRANS.TOTAL)
										ELSE (dbo.LG_300_01_PAYTRANS.TOTAL * dbo.LG_300_01_PAYTRANS.TRRATE)
										END
									)
						ELSE (LG_300_01_PAYTRANS_1.CROSSTOTAL)
						END
					)
		ELSE (LG_300_01_PAYTRANS_1.CROSSTOTAL * dbo.LG_300_01_PAYTRANS.TRRATE)
		END AS [MAK. (TL)],
	LEFT(dbo.LG_SLSMAN.DEFINITION_, 15) AS [SATIS ELEMANI],
	CASE LG_300_01_PAYTRANS_1.MODULENR
		WHEN 5
			THEN (
					CASE LG_300_01_PAYTRANS_1.TRCODE
						WHEN 4
							THEN 'İNDİRİM'
						WHEN 70
							THEN 'KREDİ KARTI'
						WHEN 5
							THEN 'VİRMAN'
						WHEN 14
							THEN 'AÇILIŞ FİŞİ'
						END
					)
		WHEN 10
			THEN (
					CASE LG_300_01_PAYTRANS_1.TRCODE
						WHEN 1
							THEN 'NAKİT'
						END
					)
		WHEN 6
			THEN (
					CASE LG_300_01_PAYTRANS_1.TRCODE
						WHEN 1
							THEN 'ÇEK'
						END
					)
		WHEN 7
			THEN (
					CASE LG_300_01_PAYTRANS_1.TRCODE
						WHEN 3
							THEN 'HAVALE'
						END
					)
		WHEN 4
			THEN (
					CASE LG_300_01_PAYTRANS_1.TRCODE
						WHEN 3
							THEN 'İADE FATURASI'
						WHEN 4
							THEN 'HİZMET FATURASI'
						WHEN 1
							THEN 'MAL FATURASI'
						END
					)
		END AS [MAK.TİPİ],
	dbo.LG_300_01_INVOICE.TRADINGGRP
FROM dbo.LG_SLSMAN
RIGHT OUTER JOIN dbo.LG_300_01_INVOICE
	ON dbo.LG_SLSMAN.LOGICALREF = dbo.LG_300_01_INVOICE.SALESMANREF
LEFT OUTER JOIN dbo.LG_300_CLCARD
	ON dbo.LG_300_01_INVOICE.CLIENTREF = dbo.LG_300_CLCARD.LOGICALREF
RIGHT OUTER JOIN dbo.LG_300_01_PAYTRANS AS LG_300_01_PAYTRANS_1
LEFT OUTER JOIN dbo.LG_300_BANKACC
	ON LG_300_01_PAYTRANS_1.BANKACCREF = dbo.LG_300_BANKACC.LOGICALREF
RIGHT OUTER JOIN dbo.LG_300_01_PAYTRANS
	ON LG_300_01_PAYTRANS_1.LOGICALREF = dbo.LG_300_01_PAYTRANS.CROSSREF
		ON dbo.LG_300_01_INVOICE.TRCODE = dbo.LG_300_01_PAYTRANS.TRCODE
			AND dbo.LG_300_01_INVOICE.LOGICALREF = dbo.LG_300_01_PAYTRANS.FICHEREF WHERE (
			dbo.LG_300_01_PAYTRANS.TRCODE = 8
			OR dbo.LG_300_01_PAYTRANS.TRCODE = 14
			)
		AND (
			CASE LG_300_01_PAYTRANS_1.MODULENR
				WHEN 5
					THEN (
							CASE LG_300_01_PAYTRANS_1.TRCODE
								WHEN 4
									THEN 'İNDİRİM'
								WHEN 70
									THEN 'KREDİ KARTI'
								WHEN 5
									THEN 'VİRMAN'
								WHEN 14
									THEN 'AÇILIŞ FİŞİ'
								END
							)
				WHEN 10
					THEN (
							CASE LG_300_01_PAYTRANS_1.TRCODE
								WHEN 1
									THEN 'NAKİT'
								END
							)
				WHEN 6
					THEN (
							CASE LG_300_01_PAYTRANS_1.TRCODE
								WHEN 1
									THEN 'ÇEK'
								END
							)
				WHEN 7
					THEN (
							CASE LG_300_01_PAYTRANS_1.TRCODE
								WHEN 3
									THEN 'HAVALE'
								END
							)
				WHEN 4
					THEN (
							CASE LG_300_01_PAYTRANS_1.TRCODE
								WHEN 3
									THEN 'İADE FATURASI'
								WHEN 4
									THEN 'HİZMET FATURASI'
								WHEN 1
									THEN 'MAL FATURASI'
								END
							)
				END = 'ÇEK'
			)
		AND (GETDATE() - LG_300_01_PAYTRANS_1.PROCDATE <= 90)
		AND (
			LG_300_01_PAYTRANS_1.FICHEREF NOT IN (
				SELECT CST.ROLLREF
				FROM LG_300_01_CSTRANS CST
				WHERE CST.CSREF IN (
						SELECT CSC.LOGICALREF
						FROM LG_300_01_CSCARD CSC
						WHERE CSC.CURRSTAT = 8
						)
				)
			)
ORDER BY [CH ACIKLAMA]
GO

Logo e-fatura e-irsaliye e-arsiv vb. xml edit

On August 29, 2023 By semt20In LOGO ERPLeave a comment

not: tablo genişlikleri oran yerine px olabilir tam eşitlenmesi için :

genişleyince kayan elementleri aşağıya almak için <div style=”clear:both;”></div> kullanılabilir

SQL MUHASEBE FİŞİ SAĞ TIK YAZ SATIR PROJE ACIKLAMASI

On August 23, 2023 By semt20In LOGO ERPLeave a comment

_SQLINFO(“NAME”, “LG_123_PROJECT”, “LOGICALREF='”+STR(R3.projectRef)+”‘”)

LOGO REÇETE VE ALT MAMÜLLERİ SQL

On February 9, 2023February 9, 2023 By semt20In LOGO ERP, SqlLeave a comment

WITH ItemHierarcyh (STCREF, MAINCREF, CARDTYPE)
AS (
    SELECT STCREF, MAINCREF, CARDTYPE
    FROM LG_123_STCOMPLN
   WHERE MAINCREF = (SELECT I.LOGICALREF FROM LG_123_ITEMS I WHERE I.CODE='RECETE0016') 

    UNION ALL

    SELECT S.STCREF, S.MAINCREF, S.CARDTYPE
    FROM LG_123_STCOMPLN S
    JOIN ItemHierarcyh PH ON S.MAINCREF = PH.STCREF
)
SELECT 
STCREF, 
AltMalzeme=(select I.CODE+'-'+I.NAME from LG_123_ITEMS I WHERE I.LOGICALREF =STCREF),
MAINCREF,
AnaMalzeme=(select I.CODE+'-'+I.NAME from LG_123_ITEMS I WHERE I.LOGICALREF =MAINCREF),
CARDTYPE
FROM ItemHierarcyh  --where CARDTYPE    in (11,12)
OPTION (MAXRECURSION 0)

LOGO ERP MAMUL RECETE

On January 12, 2023January 12, 2023 By semt20In LOGO ERPLeave a comment

select 
Mamul_ItemRef_=				t.Mamul_ItemRef,
t.Mamul_Kodu,
t.Mamul_Aciklamasi,
t.Mamul_Birim,
AltMalzeme_ReceteRef_=		isnull(t.AltMalzeme2_ReceteRef,t.AltMalzeme_ReceteRef),
AltMalzeme_ItemRef_=		isnull(t.AltMalzeme2_ItemRef,t.AltMalzeme_ItemRef),
AltMalzeme_Kodu=			isnull(t.AltMalzeme2_Kodu,t.AltMalzeme_Kodu),
AltMalzeme_Aciklamasi=		isnull(t.AltMalzeme2_Aciklamasi,t.AltMalzeme_Aciklamasi),
AltMalzeme_Birim=			isnull(t.AltMalzeme2_Birim,t.AltMalzeme_Birim),
AltMalzeme_ReceteMiktar_=	isnull(t.AltMalzeme2_ReceteMiktar,t.AltMalzeme_ReceteMiktar),
AltMalzeme_CARDTYPE_=		isnull(t.AltMalzeme2_CARDTYPE,t.AltMalzeme_CARDTYPE),
HesaplananMiktar=			CASE 
								WHEN t.AltMalzeme2_ReceteMiktar IS NULL THEN t.AltMalzeme_ReceteMiktar 
							ELSE  
								ISNULL(CAST ((t.AltMalzeme_ReceteMiktar/NULLIF(AltMalzeme_Miktar,0.000))*t.AltMalzeme2_ReceteMiktar  AS NUMERIC(10,7)),0.000) 
							END
from (
	select 
		Mamul_ItemRef=				ITEMS.LOGICALREF,
		Mamul_Kodu=					ITEMS.CODE,
		Mamul_Aciklamasi=			ITEMS.NAME,
		Mamul_Birim=				(SELECT NAME FROM LG_123_UNITSETF WITH(NOLOCK) WHERE ITEMS.UNITSETREF = LG_123_UNITSETF.LOGICALREF),
		AltMalzeme_ReceteRef=		REC.LOGICALREF,
		AltMalzeme_ItemRef=			REC.STCREF,
		AltMalzeme_Kodu=			(SELECT SITEMS.CODE FROM LG_123_ITEMS SITEMS WHERE SITEMS.LOGICALREF=REC.STCREF),
		AltMalzeme_Aciklamasi=		(SELECT SITEMS.NAME FROM LG_123_ITEMS SITEMS WHERE SITEMS.LOGICALREF=REC.STCREF),
		AltMalzeme_Birim=			(SELECT NAME FROM LG_123_UNITSETL WITH(NOLOCK) WHERE REC.UOMREF = LG_123_UNITSETL.LOGICALREF),
		AltMalzeme_Miktar=			(SELECT SITEMS.QPRODAMNT FROM LG_123_ITEMS SITEMS WHERE SITEMS.LOGICALREF=REC.STCREF),
		AltMalzeme_ReceteMiktar=	REC.AMNT,
		AltMalzeme_CARDTYPE=		REC.CARDTYPE,
		AltMalzeme2_ReceteRef=		REC2.LOGICALREF,
		AltMalzeme2_ItemRef=		REC2.STCREF,
		AltMalzeme2_Kodu=			(SELECT SITEMS.CODE FROM LG_123_ITEMS SITEMS WHERE SITEMS.LOGICALREF=REC2.STCREF),
		AltMalzeme2_Aciklamasi=		(SELECT SITEMS.NAME FROM LG_123_ITEMS SITEMS WHERE SITEMS.LOGICALREF=REC2.STCREF),
		AltMalzeme2_Birim=			(SELECT NAME FROM LG_123_UNITSETL WITH(NOLOCK) WHERE REC2.UOMREF = LG_123_UNITSETL.LOGICALREF),
		AltMalzeme2_ReceteMiktar=	REC2.AMNT,
		AltMalzeme2_CARDTYPE=		REC2.CARDTYPE
	from 
		LG_123_ITEMS ITEMS LEFT JOIN LG_123_STCOMPLN REC ON REC.MAINCREF=ITEMS.LOGICALREF LEFT JOIN LG_123_STCOMPLN REC2 ON REC2.MAINCREF=REC.STCREF
	WHERE 
		ITEMS.CARDTYPE IN (12) AND ITEMS.LOGICALREF IN (SELECT REC.MAINCREF FROM LG_123_STCOMPLN REC) 
) as t

LOGO INVOICE TABLOSU VE URUN SATIRLARI(SQL STUFF)

On January 4, 2023 By semt20In LOGO ERP, SqlLeave a comment

Invoice tablosu baz alınarak sql stuff komutuyla malzeme/hizmetleri getirmek için:

SELECT 
FIS_MALZEME_HIZMET=	(
Stuff	(
					(
					SELECT DISTINCT
						N', ' + CASE 
									WHEN STL.LINETYPE=0 THEN (SELECT ITEMS.NAME FROM LG_121_ITEMS ITEMS WHERE ITEMS.LOGICALREF=STL.STOCKREF)
									WHEN STL.LINETYPE=4 THEN (SELECT SRVCARD.DEFINITION_ FROM LG_121_SRVCARD SRVCARD WHERE SRVCARD.LOGICALREF=STL.STOCKREF)
								END
					FROM 
						LG_121_01_STLINE STL
					WHERE
						STL.INVOICEREF=I.LOGICALREF AND STL.LINETYPE IN (0/*MALZEME*/,4/*HIZMET*/)
					/*ORDER BY 
						STL.LOGICALREF*/
					FOR XML PATH(''),TYPE
					)
.value('text()[1]','nvarchar(max)'),1,2,N''
				)
)
,
* FROM LG_121_01_INVOICE I

Blog of Sem

Category: LOGO ERP