Category: Sql

MS SQL TO C# CLASS

On By semt20In C SHARP, SqlLeave a comment 
declare @TableName sysname = 'CFGUSERSLIST'
declare @result varchar(max) = 'public class ' + @TableName + '
{'

select @result = @result + '
    public ' + ColumnType + ' ' + ColumnName + ' { get; set; }
'
from
(
    select 
        replace(col.name, ' ', '_') ColumnName,
        column_id,
        case typ.name 
            when 'bigint' then 'long'
            when 'binary' then 'byte[]'
            when 'bit' then 'bool'
            when 'char' then 'string'
            when 'date' then 'DateTime'
            when 'datetime' then 'DateTime'
            when 'datetime2' then 'DateTime'
            when 'datetimeoffset' then 'DateTimeOffset'
            when 'decimal' then 'decimal'
            when 'float' then 'float'
            when 'image' then 'byte[]'
            when 'int' then 'int'
            when 'money' then 'decimal'
            when 'nchar' then 'char'
            when 'ntext' then 'string'
            when 'numeric' then 'decimal'
            when 'nvarchar' then 'string'
            when 'real' then 'double'
            when 'smalldatetime' then 'DateTime'
            when 'smallint' then 'short'
            when 'smallmoney' then 'decimal'
            when 'text' then 'string'
            when 'time' then 'TimeSpan'
            when 'timestamp' then 'DateTime'
            when 'tinyint' then 'byte'
            when 'uniqueidentifier' then 'Guid'
            when 'varbinary' then 'byte[]'
            when 'varchar' then 'string'
            else 'UNKNOWN_' + typ.name
        end + 
        CASE
            WHEN col.is_nullable=1 AND
                 typ.name NOT IN (
                     'binary', 'varbinary', 'image',
                     'text', 'ntext',
                     'varchar', 'nvarchar', 'char', 'nchar')
            THEN '?'
            ELSE '' END AS [ColumnType]
    from sys.columns col
        join sys.types typ on
            col.system_type_id = typ.system_type_id AND col.user_type_id = typ.user_type_id 
    where object_id = object_id(@TableName)
) t
order by column_id

set @result = @result  + '
}'

print @result

PERKOTEK MYSQL LINK TO SQL SERVER

On By semt20In pdks, Sql2 Comments

Perkotek klasorunde mysql binde bu versionda test edildi;

MYSQL Ver 12.22 Distrib 4.0.20a, for Win95/Win98 (i32)

My sql connector mysql-connector-odbc-3.51.30-winx64.msi indirildi

Control panelde system dns yaratıldı;

Sql serverde linked servere sağ tıklayıp new deyip aşağıdaki ayarlar yapıldı

Bunların true olmasına dikkat et;

Tablolar geldi;

Link server üzerinden örnek sorgu çalıştır;

Spye çevir;

LOGO SQL MALZEME EKSTRE

On By semt20In LOGO ERP, Sql12 Comments 

/*TEST = 
select * from  dbo.SELECT_MALZEMEEXTRE_201('01/01/2017',getdate(),'01-YK-0001')*/
/****** Object:  UserDefinedFunction [dbo].[SELECT_MALZEMEEXTRE_201]    Script Date: 18.8.2020 09:09:02 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE FUNCTION [dbo].[SELECT_MALZEMEEXTRE_201]
(
    @BAT DATETIME,
	@BIT DATETIME,
	@MALZEMEKODU VARCHAR(50)
)
RETURNS TABLE
AS
RETURN 
SELECT
X.AMBAR,
x.[Stfıche_Referans] ,
x.[Stlıne_Referans] ,
x.[Fiş No],
x.[Fiş Türü],
x.[Belge No],
x.[Stfıche Tarıh],
x.[Stlıne Tarıh],
x.[Ftıme],
x.[Stok Kodu],
x.[Stok Adı],
x.[Bırım],
x.[Giriş Çıkış],
x.[Miktar],
  X.STFICHELNNO,
x.Kalan,
x.[Tutar]
FROM (
SELECT
(SELECT NAME FROM L_CAPIWHOUSE CAPIWHOUSE WHERE CAPIWHOUSE.NR = STLINE.SOURCEINDEX AND CAPIWHOUSE.FIRMNR = 201 ) AMBAR,

STFICHE.LOGICALREF [STFICHE_REFERANS],
STLINE.LOGICALREF [STLINE_REFERANS],
[Fiş No]=STFICHE.FICHENO,

[Fiş Türü]=CASE STFICHE.TRCODE 

WHEN  1 then 'Satınalma İrsaliyesi'

WHEN  2 then 'Perakende Satış İade İrsaliyesi'

WHEN  3 then 'Toptan Satış İade İrsaliyesi'

WHEN  4 then 'Konsinye Çıkış İade İrsaliyesi'

WHEN  5 then 'Konsinye Giriş İrsaliyesi'

WHEN  6 then 'Satınalma İade İrsaliyesi'

WHEN  7 then 'Perakende Satış İrsaliyesi'

WHEN  8 then 'Toptan Satış İrsaliyesi'

WHEN  9 then 'Konsinye Çıkış İrsaliyesi'

WHEN  10 then 'Konsinye Giriş İade İrsaliyesi'

WHEN  11 then 'Fire Fişi'

WHEN  12 then 'Sarf Fişi'

WHEN  13 then 'Üretimden Giriş Fişi'

WHEN  14 then 'Devir Fişi'

WHEN  25 then 'Ambar Fişi'

WHEN  26 then 'Muhtahsil İrsaliyesi'

WHEN  50 then 'Sayım Fazlası Fişi'

WHEN  51 then 'Sayım Eksiği Fişi' ELSE '' END,

[Belge No]=STFICHE.DOCODE,
 
[Stfıche Tarıh]=STFICHE.DATE_,

[Stlıne Tarıh]=STLINE.DATE_,

[Ftıme]=STLINE.FTIME,

[Stok Kodu]=ITEMS.CODE,

[Stok Adı]=ITEMS.NAME,

[Bırım]=BIRIM.CODE,

[Giriş Çıkış]= CASE WHEN STLINE.IOCODE IN(1,2) THEN 'Giriş' when STLINE.IOCODE IN(3,4) THEN 'Çıkış' else '' end,

[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),
STLINE.STFICHELNNO,

0.00 KALAN,

[Tutar]=(CASE WHEN STLINE.IOCODE IN(1,2) THEN 1 ELSE -1 end) * STLINE.VATMATRAH

FROM LG_201_01_STFICHE STFICHE WITH (NOLOCK)

LEFT OUTER JOIN LG_201_01_STLINE STLINE WITH (NOLOCK) ON STFICHE.LOGICALREF=STLINE.STFICHEREF

LEFT OUTER JOIN LG_201_ITEMS ITEMS      WITH (NOLOCK) ON STLINE.STOCKREF =ITEMS.LOGICALREF

LEFT OUTER JOIN LG_201_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 ITEMS.CODE=@MALZEMEKODU
AND STLINE.LPRODSTAT=0
AND (STLINE.DATE_>=@BAT AND STLINE.DATE_<=@BIT )
)
 AS  X 
GO

LOGO SQL MASRAF MERKEZİ MUHASEBE FİŞ TOPLAM KONTROL

On By semt20In LOGO ERP, SqlLeave a comment 
SELECT
        E.TRCODE "FIŞ TÜRÜ",
        E.FICHENO "FIŞ NO",
        L.DATE_ "FIŞ TARIHI",
        L.ACCOUNTCODE "MUH_KODU",
        CASE
                WHEN L.SIGN =0 THEN "BORÇ" ELSE "ALACAK"
        END        AS [BORÇ/ALACAK TÜRÜ] ,
        L.DEBIT "HAREKET_BORÇ",
        MASRAF_MERKEZI_TOPLAM.BORÇ "MASRAF MERK_BORÇ",
        L.CREDIT "HAREKET_ALACAK",
        MASRAF_MERKEZI_TOPLAM.ALACAK "MASRAF MERK_ALACAK"
FROM
        LG_101_01_EMFICHE E INNER JOIN
                      LG_101_01_EMFLINE  L ON E.LOGICALREF = L.ACCFICHEREF
LEFT OUTER JOIN
(
SELECT
        M.PREVLINEREF,
        M.TRCODE,
        M.TSIGN,
        SUM((2 - M.TSIGN - 1) * M.TRNET) AS BORÇ,
        SUM((M.TSIGN - 1 + 1) * M.TRNET) AS ALACAK
FROM
        LG_101_01_ACCDISTDETLN  M INNER JOIN
                         LG_101_01_EMFLINE  L ON M.PREVLINEREF = L.LOGICALREF
GROUP BY
        M.PREVLINEREF, M.TRCODE, M.TSIGN
)MASRAF_MERKEZI_TOPLAM ON L.LOGICALREF = MASRAF_MERKEZI_TOPLAM.PREVLINEREF

WHERE
        ((MASRAF_MERKEZI_TOPLAM.BORÇ <> L.DEBIT) OR (MASRAF_MERKEZI_TOPLAM.ALACAK <> L.CREDIT))

C# SQL GET LOGICALREF

On By semt20In C SHARP, LOGO ERP, LOGO OBJECTS, SqlLeave a comment 
        public string GET_LOGICALREF(string TABLENAME, string LOGICALREF, string WHEREFIELDNAME, string WHEREDATA) {
            SqlDatabase sqlSERVICEDB;
            sqlSERVICEDB = new SqlDatabase(M.CONSTR_BMS);
            DataTable DATA = sqlSERVICEDB.ExecuteDataSet(new SqlCommand("SELECT TOP 1 " + LOGICALREF + " FROM " + TABLENAME + " WHERE " + WHEREFIELDNAME + "='" + WHEREDATA + "' ORDER BY LOGICALREF DESC")).Tables[0];
            if (DATA.Rows.Count == 0) {
                return "";
            }
            return DATA.Rows[0][0].ToString();
        }

LOGO BORÇ TAKİP EKRANI TO LOGO

On By semt20In LOGO ERP, SqlLeave a comment 

SELECT TOP 100 PERCENT 
LGMAIN.TRCURR,LGMAIN.SIGN,LGMAIN.MODULENR, LGMAIN.FICHEREF, LGMAIN.LOGICALREF,
(CASE LGMAIN.SIGN WHEN 0 THEN CASE WHEN LGMAIN.TRCURR IN (0,160) THEN LGMAIN.TOTAL ELSE LGMAIN.TOTAL*LGMAIN.TRRATE END ELSE 0-(CASE WHEN LGMAIN.TRCURR IN (0,160) THEN LGMAIN.TOTAL ELSE LGMAIN.TOTAL*LGMAIN.TRRATE END)END) AS TUTAR,
LGMAIN.DATE_,
LGMAIN.PROCDATE,
LGMAIN.MATCHDATE,
CASE WHEN PAID=0 THEN 'Kapama Yapılmadı' else 'Kapalı' End KAPAMA_DURUMU,
dbo.BM_301_PAYTRANS2ISLEMTURU(LGMAIN.TRCODE,LGMAIN.MODULENR) TUR,
LGMAIN.BRANCH,
LGMAIN.PAID,
LGMAIN.CARDREF
FROM 
BM_301_01_PAYTRANS LGMAIN WITH(NOLOCK)
LEFT OUTER JOIN LG_301_01_INVOICE INVFC WITH(NOLOCK) ON (LGMAIN.FICHEREF  =  INVFC.LOGICALREF)
LEFT OUTER JOIN LG_301_01_CLFLINE CTRNS WITH(NOLOCK) ON (LGMAIN.FICHEREF  =  CTRNS.LOGICALREF)
LEFT OUTER JOIN LG_301_01_CSROLL RLFIC WITH(NOLOCK) ON (LGMAIN.FICHEREF  =  RLFIC.LOGICALREF)
LEFT OUTER JOIN LG_301_01_BNFLINE BTRNS WITH(NOLOCK) ON (LGMAIN.FICHEREF  =  BTRNS.LOGICALREF)
LEFT OUTER JOIN LG_301_01_KSLINES CASHTR WITH(NOLOCK) ON (LGMAIN.FICHEREF  =  CASHTR.LOGICALREF)
LEFT OUTER JOIN LG_301_01_ORFICHE ORFIC WITH(NOLOCK) ON (LGMAIN.FICHEREF  =  ORFIC.LOGICALREF)
 WHERE 
(LGMAIN.CARDREF = 10916) AND 
--LGMAIN.CARDREF IN (SELECT CL.LOGICALREF FROM LG_301_CLCARD CL WHERE CL.CODE LIKE '120%') AND
((((LGMAIN.MODULENR=3)
AND(ORFIC.CANCELLED=0))OR((LGMAIN.MODULENR=4)
AND(INVFC.CANCELLED=0))OR((LGMAIN.MODULENR IN (5,61,62))
AND(CTRNS.CANCELLED=0))OR((LGMAIN.MODULENR=6)
AND(RLFIC.CANCELLED=0))OR((LGMAIN.MODULENR=7)
AND(BTRNS.CANCELLED=0))OR((LGMAIN.MODULENR=10)
AND(CASHTR.CANCELLED=0))))
AND LGMAIN.PROCDATE<= '05/01/2020' ORDER BY CARDREF, TRCURR, DATE_, SIGN, MODULENR, FICHEREF, LOGICALREF /*MATCHDATE */

LOGO BORÇ TAKİP YAPILMAMIŞ (KAPATILMAMIŞ) MAKBUZLAR

On By semt20In LOGO ERP, SqlLeave a comment 
CREATE VIEW BM_301_AO_RAPOR_BORC_TAKIP_YAPILMAYANLAR AS
SELECT TOP 100 PERCENT * FROM (
SELECT  
P.DATE_,
BRANCH ISYERI_KODU,  
(select CONVERT(VARCHAR,NR)+', '+NAME from L_CAPIDIV  WITH(NOLOCK)  WHERE FIRMNR=301 and NR=P.BRANCH)  [ISYERI],
CASE P.MODULENR
               WHEN 7 THEN
                   CASE P.TRCODE
                       WHEN 3 THEN
                           'Gelen Havale'
                       WHEN 4 THEN
                           'Gönderilen Havale'
                       WHEN 16 THEN
                           'Banka - Alınan Hizmet Faturası'
                       WHEN 17 THEN
                           'Banka - Verilen Hizmet Faturası'
                   END
               WHEN 4 THEN
                   CASE P.TRCODE
                       WHEN 1 THEN
                           'Satınalama Faturası'
                       WHEN 4 THEN
                           'Alınan Hizmet Faturası'
                       WHEN 6 THEN
                           'Satınalma İade Faturası'
                       WHEN 13 THEN
                           'Satınalma Fiyat Farkı Faturası'
                       WHEN 2 THEN
                           'Perakende Satış İade Faturası'
                       WHEN 3 THEN
                           'Toptan Satış İade Faturası'
                       WHEN 7 THEN
                           'Perkande Satış Faturası'
                       WHEN 8 THEN
                           'Toptan Satış Faturası'
                       WHEN 9 THEN
                           'Verilen Hizmet Faturası'
                       WHEN 14 THEN
                           'Satış Fiyat Farkı Faturası'
                   END
               WHEN 5 THEN
                   CASE P.TRCODE
                       WHEN 70 THEN
                           'Kredi Kartı Fişi'
                       WHEN 2 THEN
                           'Nakit Ödeme'
                       WHEN 1 THEN
                           'Nakit Tahsilat'
                       WHEN 3 THEN
                           'Borç Dekontu'
                       WHEN 4 THEN
                           'Alacak Dekontu'
                       WHEN 5 THEN
                           'Virman Fişi'
                       WHEN 6 THEN
                           'Kurfarkı Fişi'
                       WHEN 14 THEN
                           'Açılış Fişi'
                       WHEN 41 THEN
                           'Verilen Vade Farkı Faturası'
                       WHEN 42 THEN
                           'Alınan Vade Farkı Faturası'
                       WHEN 71 THEN
                           'Kredi Kartı İade Fişi'
                       WHEN 72 THEN
                           'Firma Kredi Kartı Fişi'
                       WHEN 73 THEN
                           'Firma Kredi Kartı İade Fişi'
                   END
               WHEN 6 THEN
                   CASE P.TRCODE
                       WHEN 1 THEN
                           'Müşteri Çeki'
                       WHEN 3 THEN
                           'Kendi Çekimiz'
                   END
               WHEN 3 THEN
                   CASE P.TRCODE
                       WHEN 2 THEN
                           'Satınalama Siparişi'
                       WHEN 1 THEN
                           'Satış Siparişi'
                   END
               WHEN 10 THEN
                   CASE P.TRCODE
                       WHEN 1 THEN
                           'Nakit Tahsilat'
                       WHEN 2 THEN
                           'Nakit Ödeme'
                   END
               WHEN 61 THEN
                   CASE P.TRCODE
                       WHEN 3 THEN
                           'Borç Dekontu'
                       WHEN 4 THEN
                           'Alacak Dekontu'
                   END
               WHEN 62 THEN
                   CASE P.TRCODE
                       WHEN 3 THEN
                           'Borç Dekontu'
                       WHEN 4 THEN
                           'Alacak Dekontu'
                   END
           END AS ISLEM ,
(SELECT CL.CODE FROM LG_301_CLCARD CL WHERE CL.LOGICALREF=CARDREF) CARI_KOD,
(SELECT CL.DEFINITION_ FROM LG_301_CLCARD CL WHERE CL.LOGICALREF=CARDREF) CARI,
(SELECT ROUND(SUM(CASE WHEN C.SIGN=0 THEN C.AMOUNT ELSE 0-C.AMOUNT END),2) BAKIYE FROM LG_301_01_CLFLINE C  WITH(NOLOCK)  WHERE   C.CANCELLED=0 AND C.CLIENTREF=P.CARDREF) AS BAKIYE,
ABS(CASE SIGN WHEN 0 THEN CASE WHEN TRCURR IN (0,160) THEN TOTAL ELSE TOTAL*TRRATE END ELSE 0-(CASE WHEN TRCURR IN (0,160) THEN TOTAL ELSE TOTAL*TRRATE END)END) AS TUTAR
FROM BM_301_01_PAYTRANS P WHERE CANCELLED=0 AND PAID=0 AND SIGN=1 AND P.CARDREF IN (SELECT LOGICALREF FROM LG_301_CLCARD WHERE CODE LIKE '120.%')
) AS T
WHERE BAKIYE>0 ORDER BY CARI_KOD

LOGO SQL BELIRLI TARIH ARASI MALZEMELERIN SATISLARI

On By semt20In LOGO ERP, SqlLeave a comment 
SELECT 
ITMSC.CODE,
ITMSC.NAME,
ISNULL(SUM(AMOUNT),0)
FROM 
LG_118_01_STLINE STRNS WITH(NOLOCK) LEFT OUTER JOIN
LG_118_01_STFICHE STFIC WITH(NOLOCK) ON (STRNS.STFICHEREF  =  STFIC.LOGICALREF) LEFT OUTER JOIN
LG_118_CLCARD CLNTC WITH(NOLOCK) ON (STRNS.CLIENTREF  =  CLNTC.LOGICALREF) LEFT OUTER JOIN
LG_118_ITEMS ITMSC WITH(NOLOCK) ON (STRNS.STOCKREF  =  ITMSC.LOGICALREF) LEFT OUTER JOIN
LG_118_UNITSETL USLINE WITH(NOLOCK) ON (STRNS.UOMREF  =  USLINE.LOGICALREF) LEFT OUTER JOIN
LG_118_UNITSETF UNITSET WITH(NOLOCK) ON (ITMSC.UNITSETREF  =  UNITSET.LOGICALREF) LEFT OUTER JOIN
LG_118_VARIANT VARIANT WITH(NOLOCK) ON (STRNS.VARIANTREF = VARIANT.LOGICALREF)
 WHERE 
 /*(ITMSC.CODE LIKE 'COL00008') AND*/
 ((STFIC.DATE_ >= GETDATE()-1000) AND (STFIC.DATE_ <= GETDATE())) AND
 (STFIC.CANCELLED = 0) AND (STFIC.TRCODE IN (2,3,4,7,8,9)) AND
 (STFIC.STATUS IN (0,1)) AND  
 (STRNS.CPSTFLAG <> 1) AND
 (STRNS.LINETYPE IN (0,1,5,6,8,9,10)) AND
 (STFIC.DEVIR IN (0,1)) AND
 ((STFIC.TRCODE IN (2,3,4,7,8,9,35,36,37,38,39))OR(STFIC.TRCODE IN (1,5,6,10,26,30,31,32,33,34)))
  GROUP BY ITMSC.CODE,ITMSC.NAME

LOGO SQL PAYTRANSDAN CARİ BAKİYELERİNİ GÖRME

On By semt20In LOGO ERP, SqlLeave a comment 

(SELECT CODE FROM LG_301_CLCARD WHERE LOGICALREF=CARDREF) AS CARI_HESAP, CARDREF,
SUM(
CASE SIGN WHEN 0 THEN
CASE WHEN TRCURR IN (0,160) THEN TOTAL ELSE TOTAL*TRRATE END
ELSE 0-(CASE WHEN TRCURR IN (0,160) THEN TOTAL ELSE TOTAL*TRRATE END)
END) AS BAKIYE 

--, *
FROM LG_301_01_PAYTRANS 
WHERE CANCELLED=0 AND PAID=0 AND CARDREF IN (SELECT LOGICALREF FROM LG_301_CLCARD WHERE CODE LIKE '120%')
--AND CARDREF=30311
GROUP BY CARDREF