Malzeme,Birim Ambar stok(Pivot)

On November 27, 2024 By semt20In TOOLSLeave a comment

DECLARE @columns NVARCHAR(MAX)
DECLARE @sql NVARCHAR(MAX)

SELECT @columns = STRING_AGG(QUOTENAME(NR), ',') 
FROM L_CAPIWHOUSE 
WHERE FIRMNR = 123
select @columns='[-1],'+@columns

SET @sql = '
SELECT LOGICALREF,
       CODE,
       DESCRIPTION,
       UNIT, ' + @columns + '
FROM (
    SELECT TF.LOGICALREF,
           TF.CODE,
           TF.NAME AS DESCRIPTION,
           US.NAME AS UNIT,
           WH.NR AS WAREHOUSE,
           ISNULL(SUM(ST.ONHAND), 0) AS STOCK
    FROM (
        SELECT DISTINCT LOGICALREF, CODE, NAME, UNITSETREF
        FROM LG_123_ITEMS
    ) AS TF
    --LEFT JOIN LG_123_ITMUNITA IUA ON IUA.ITEMREF = TF.LOGICALREF
    --LEFT JOIN LG_123_UNITSETL US ON US.LOGICALREF = IUA.UNITLINEREF
    LEFT JOIN LG_123_UNITSETL US ON US.UNITSETREF = TF.UNITSETREF AND US.MAINUNIT = 1
    CROSS JOIN (SELECT W.NR,W.FIRMNR FROM L_CAPIWHOUSE W UNION ALL SELECT -1 AS NR,123 AS FIRMNR) WH
    LEFT JOIN LV_123_01_STINVTOT ST ON ST.STOCKREF = TF.LOGICALREF AND ST.INVENNO = WH.NR
    WHERE WH.FIRMNR = 123
    GROUP BY TF.LOGICALREF, TF.CODE, TF.NAME, US.NAME, WH.NR
) AS SourceTable
PIVOT (
    MAX(STOCK)
    FOR WAREHOUSE IN (' + @columns + ')
) AS PivotTable
ORDER BY LOGICALREF;'

-- 3. Dinamik SQL'i çalıştırın.
EXEC sp_executesql @sql

LOGO C#DAN SQL TARIH INSERT ETME

On October 1, 2024 By semt20In TOOLS2 Comments

 int TIME_ = DateTime.Now.Hour * 65536 * 256 + DateTime.Now.Minute * 65536 + DateTime.Now.Second * 256;

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 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

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 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

MOBILSALES SQL PENETRASYON RAPORU

On September 27, 2022 By semt20In LOGO ERP, MOBILESALES, SqlLeave a comment

 select 
		(CASE WHEN P.PTYPE='0' THEN 'MIKTAR' ELSE 'VAR/YOK' END) PENETRATIONTYPE,
		U.CODE+'-'+U.FNAME SALESMAN,
		PNTHEADER.DATE, 
		CLCARD.CODE+'-'+CLCARD.DEFINITION_ CLIENT,
		ISNULL(P.PCODE,'') PCODE,
        ISNULL(P.PDEFINITION,'') PDEFINITION,
		ISNULL(WD.ITEMNAME,'') ITEMNAME	,
		WU.PRICE, 
		WU.AMOUNT,
		WU.NOTE
	 from WOR_PNTHEADER  PNTHEADER 
	 LEFT JOIN WOR_PNTTRANS WU ON WU.PNTHEADER_REF=PNTHEADER.PNT_GUID
	 LEFT JOIN WOR_PENETRATIONDETAIL WD ON WU.PNTDTL_ID=WD.LOGICALREF
	 LEFT JOIN WOR_USERS U ON U.USERID=PNTHEADER.SALESMANREF
	 LEFT JOIN WOR_PENETRATION P ON P.LOGICALREF=PNTHEADER.PNT_ID
	 LEFT JOIN LG_219_CLCARD CLCARD ON CLCARD.LOGICALREF=PNTHEADER.CLIENTREF
	 /*where PNTHEADER.LOGICALREF=9*/
	 ORDER BY PNTHEADER.DATE, WD.SEQUENCE

LOGO CARİ HESAP HAREKET DÖKÜMÜ EKLEME TARİHİ

On August 29, 2022August 29, 2022 By semt20In LOGO ERP, SqlLeave a comment

Talep: Cari Hesap Hareket Dökümü -> Kullanıcı tanımlı rapor kullanarak rapor filtresine created date(clfiche) tarih aralığı filtresi eklemek

Not: Logonun tanımlı alanlar listesinde clfline createddate yok dolayısıyla sql infodan created date getirildi.

S_GUN =
VAL(_SQLINFO(“DAY(CAPIBLOCK_CREADEDDATE)”,”LG_001_01_CLFLINE”,”LOGICALREF='”+STR(R1.logicalRef)+”‘”))

S_AY =
VAL(_SQLINFO(“MONTH(CAPIBLOCK_CREADEDDATE)”,”LG_001_01_CLFLINE”,”LOGICALREF='”+STR(R1.logicalRef)+”‘”))

S_YIL =

VAL(_SQLINFO(“YEAR(CAPIBLOCK_CREADEDDATE)”,”LG_001_01_CLFLINE”,”LOGICALREF='”+STR(R1.logicalRef)+”‘”))

S_TARIH=

DATE([S_GUN],[S_AY],[S_YIL])

LOGO OBJECTS TSQL KAÇ ADET ÇALIŞMAKTA

On August 10, 2022 By semt20In LOGO OBJECTS, SqlLeave a comment

select 
         count(distinct program_name) 
from master.dbo.sysprocesses 
where program_name like 'LOGO_ERPOBJECT%'

Logo Erp / Sql Malzeme sınıfı bağlantıları çalışması

On August 10, 2022August 10, 2022 By semt20In LOGO ERP, SqlLeave a comment

kırılım yapısı(3 alt kırılım-FERRARI) : Tüm Malzemeler -> Kırılım1-> Kırılım2-> Kırılım3 -> Kırılım3 Malzemeleri

Kampanya yapısı(2 alt kırılım-KMP) : Tüm Malzemeler -> Kırılım1-> Kırılım2 -> Kırılım2 Malzemeleri

create view [RM_FERRARI_MALZEME_SINIFLARI]
as
select top 100 percent [Type], [Level1Ref] = case when [Level1Ref] = 1 then Level2Ref else Level1Ref end, [Level1] = case when [Level1Ref] = 1 then Level2 else Level1 end, [Level2Ref] = case when [Level1Ref] = 1 then Level3Ref else Level2Ref end, [Level2] = case when [Level1Ref] = 1 then Level3 else Level2 end, [Level3Ref] = case when [Level1Ref] = 1 then 0 else [Level3Ref] end, [Level3] = case when [Level1Ref] = 1 then '' else [Level3] end, [ItemRef], [Code], [Name], [Active]
from (
	select [Type] = case when ITMSC.CARDTYPE = 20 then 'Genel Malzeme Sınıfı' else 'Malzeme' end, [Level1Ref] = (
			select top 1 LGMAIN2.PARENTREF
			from LG_101_ITMCLSAS LGMAIN2
			where LGMAIN2.CHILDREF = (
					select top 1 LGMAIN2.PARENTREF
					from LG_101_ITMCLSAS LGMAIN2
					where LGMAIN2.CHILDREF = LGMAIN.PARENTREF
					)
			), [Level1] = (
			select I2.CODE + '-' + I2.name
			from LG_101_ITEMS I2
			where I2.LOGICALREF = (
					select top 1 LGMAIN2.PARENTREF
					from LG_101_ITMCLSAS LGMAIN2
					where LGMAIN2.CHILDREF = (
							select top 1 LGMAIN2.PARENTREF
							from LG_101_ITMCLSAS LGMAIN2
							where LGMAIN2.CHILDREF = LGMAIN.PARENTREF
							)
					)
			), [Level2Ref] = (
			select top 1 LGMAIN2.PARENTREF
			from LG_101_ITMCLSAS LGMAIN2
			where LGMAIN2.CHILDREF = LGMAIN.PARENTREF
			), [Level2] = (
			select I2.CODE + '-' + I2.name
			from LG_101_ITEMS I2
			where I2.LOGICALREF = (
					select top 1 LGMAIN2.PARENTREF
					from LG_101_ITMCLSAS LGMAIN2
					where LGMAIN2.CHILDREF = LGMAIN.PARENTREF
					)
			), [Level3Ref] = LGMAIN.PARENTREF, [Level3] = (
			select I2.CODE + '-' + I2.name
			from LG_101_ITEMS I2
			where I2.LOGICALREF = LGMAIN.PARENTREF
			), [ItemRef] = ITMSC.LOGICALREF, [Code] = ITMSC.CODE, [Name] = ITMSC.name, [Active] = ITMSC.ACTIVE
	from LG_101_ITMCLSAS LGMAIN with (nolock)
	left outer join LG_101_ITEMS ITMSC with (nolock) on (LGMAIN.CHILDREF = ITMSC.LOGICALREF)
	) as T
where [Level1] = 'MS01-FERRARI' or Level3 = 'MS01.16-KMP'
order by Level1, Level2, Level3

Blog of Sem

Tag: LOGO