Wednesday, February 17, 2010

LVF dosyası nasıl açılır?

LVF dosyası Logo tarafından üretilen programlarda oluşturulmuş rapor çıktısı formatıdır. Bu dosyayı açmak için Logo tarafından verilen kurulum CD'sinde Report Viewer programını bulabilirsiniz.

Elinizde CD yoksa aşağıdaki linkten gerekli Report Viewer programını indirip LVF dosyasını açabilirsiniz.

Programlar:
Report Viewer
LVF Linki:
ftp://download.logo.com.tr/Windows/TOOLS/tools/ReportViewer.rar

Örnek Program Görünümü:

Monday, February 15, 2010

Tiger/Unity'deki tarih bilgisinin çevrim işlemleri

Tiger/Unity veritabanında integer olarak tutulan tarih bilgisininin datetime'a çeviren fonksiyon.

CREATE FUNCTION fn_LogoDatetoSystemDate (@DEGER INT)
RETURNS datetime

AS
BEGIN
DECLARE @GUN VARCHAR(2), @AY VARCHAR(2), @YIL VARCHAR(4)

DECLARE @SONUC datetime
SELECT
@GUN=CAST((CONVERT(INT,CONVERT(BINARY,@DEGER,2),0)-(CONVERT(INT,CONVERT(BINARY,@DEGER,2),0)/256*256)) AS VARCHAR(3))
SELECT
@AY=CAST(((CONVERT(INT,CONVERT(BINARY,@DEGER,2),0)-(65536*(CONVERT(INT,CONVERT(BINARY,@DEGER,2),0)/65536)))-(CONVERT(INT,CONVERT(BINARY,@DEGER,2),0)-(CONVERT(INT,CONVERT(BINARY,@DEGER,2),0)/256*256)))/256 AS VARCHAR(3))
SELECT
@YIL=CAST((CONVERT(INT,CONVERT(BINARY,@DEGER,2),0)/65536) AS VARCHAR(6))
SET @GUN=CASE WHEN LEN(@GUN)<2 THEN '0'+@GUN ELSE @GUN END
SET @AY=CASE WHEN LEN(@AY)<2 THEN '0'+@AY ELSE @AY END
SONUC:
IF @DEGER<>0
BEGIN
SET @SONUC=CONVERT(DATETIME, @YIL + '-' + @AY + '-' + @GUN + ' 00:00:00', 102)
END
IF @DEGER=0
SET @SONUC= NULL
RETURN @SONUC
END


Tiger/Unity için tarih bilgisinini datetime'a integer olarak çeviren fonksiyon.

CREATE FUNCTION fn_LogoTimetoSystemTime (@GELENDEGER INT)
RETURNS VARCHAR(8)
AS

BEGIN
DECLARE @SAAT VARCHAR(2), @DAKIKA VARCHAR(2), @SANIYE VARCHAR(2), @SONUC VARCHAR(8)
SELECT
@SAAT=RTRIM(CONVERT(char(20), ROUND(@GELENDEGER / 16777216, 2))),
@DAKIKA=RTRIM(CONVERT(char(20), ROUND((@GELENDEGER - ROUND(@GELENDEGER / 16777216, 2) * 16777216)/ 65536, 2))),
@SANIYE=RTRIM(CONVERT(char(20), ROUND((@GELENDEGER - ROUND(@GELENDEGER / 16777216, 2) * 16777216 - ROUND((@GELENDEGER - ROUND(@GELENDEGER / 16777216, 2) * 16777216) / 65536, 2) * 65536) / 256, 2)))
SET @SAAT=CASE WHEN LEN(@SAAT)=1 THEN '0'+@SAAT ELSE @SAAT END
SET @DAKIKA=CASE WHEN LEN(@DAKIKA)=1 THEN '0'+@DAKIKA ELSE @DAKIKA END
SET @SANIYE=CASE WHEN LEN(@SANIYE)=1 THEN '0'+@SANIYE ELSE @SANIYE END
SET @SONUC= @SAAT + ':' +@DAKIKA + ':' + @SANIYE
RETURN @SONUC
END

Wednesday, February 10, 2010

Logo Tiger'da Muhasebe Kodu olmayan cari kartların kontrolü

SELECT LOGICALREF,CODE, DEFINITION_, CITY FROM LG_106_CLCARD WHERE
(LOGICALREF NOT IN (SELECT CARDREF FROM LG_106_CRDACREF WHERE (LG_106_CRDACREF.TRCODE = 5)))
ORDER BY DEFINITION_

Logo Tiger'da firma dönem bilgilerini listelemek

SELECT FRM.NR AS FIRMA_NO,FRM.NAME AS FIRMA_ADI
DONEM.NR AS DONEM_NO FROM L_CAPIFIRM AS FRM
INNER JOIN L_CAPIPERIOD AS DONEM ON FRM.NR=DONEM.FIRMNR
order by FRM.NR,DONEM.NR

Left Join with Distinct Statement in T-SQL

Problem:

SQL Left Join, it return multiple rows of the same items. I tried DISTINCT but that didnt work because its on Left Join. So my question how to add DISTINCT to Left Join?

Solution:
SELECT *
FROM dbo.LG_106_10_STLINE (NOLOCK) INNER JOIN
dbo.LG_106_ITEMS (NOLOCK) ON dbo.LG_106_10_STLINE.STOCKREF = dbo.LG_106_ITEMS.LOGICALREF LEFT OUTER JOIN
dbo.LG_106_10_STFICHE (NOLOCK) ON dbo.LG_106_10_STLINE.STFICHEREF = dbo.LG_106_10_STFICHE.LOGICALREF LEFT OUTER JOIN
dbo.LG_106_CLCARD (NOLOCK) ON dbo.LG_106_10_STFICHE.CLIENTREF = dbo.LG_106_CLCARD.LOGICALREF LEFT OUTER JOIN
dbo.L_CAPIDIV (NOLOCK) ON dbo.LG_106_10_STFICHE.BRANCH = dbo.L_CAPIDIV.NR LEFT OUTER JOIN
dbo.LG_106_SPECODES (nolock) LG_106_SPECODES_1
--This is I Changed
(select DISTINCT dbo.LG_106_SPECODES.SPECODE,dbo.LG_106_SPECODES.DEFINITION_ FROM dbo.LG_106_SPECODES (NOLOCK)) LG_106_SPECODES_1 ON dbo.LG_106_ITEMS.STGRPCODE = LG_106_SPECODES_1.SPECODE LEFT OUTER JOIN
dbo.L_TRADGRP (NOLOCK) ON dbo.LG_106_10_STFICHE.TRADINGGRP = dbo.L_TRADGRP.GCODE LEFT OUTER JOIN
dbo.LG_106_SPECODES LG_106_SPECODES_2 (NOLOCK) ON dbo.LG_106_10_STLINE.DELVRYCODE = LG_106_SPECODES_2.SPECODE LEFT OUTER JOIN
LG_106_PROJECT (NOLOCK) ON LG_106_10_STLINE.PROJECTREF = LG_106_PROJECT.LOGICALREF LEFT OUTER JOIN
dbo.LG_106_10_INVOICE (NOLOCK) ON dbo.LG_106_10_STFICHE.INVOICEREF = dbo.LG_106_10_INVOICE.LOGICALREF
WHERE (dbo.LG_106_10_STFICHE.TRCODE IN (12)) AND
(LG_106_SPECODES_2.SPECODETYPE IS NULL OR LG_106_SPECODES_2.SPECODETYPE = 0)
AND (dbo.L_CAPIDIV.FIRMNR = 106)