LOGO TIGER 3 SQL AY , AMBAR VE MALZEME BAZLI STOK DURUM RAPORU

On October 14, 2019April 29, 2021 By semt20In LOGO ERP, LOGO OBJECTSLeave a comment

CREATE view [dbo].[BM_211_STOKAYLIK] as
SELECT TOP 100 PERCENT  tarih, ambar,'0' [isyeri],
  CASE ITEMS2.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 AS mal_tur,
ITEMS2.CODE mal_kod, ITEMS2.NAME mal_ad ,BIRIM.CODE birim ,

       ISNULL(
       (
           SELECT SUM(ONHAND)
           FROM LV_211_01_STINVTOT STINVTOT
           WHERE STINVTOT.STOCKREF = ITEMS2.LOGICALREF
                 AND STINVTOT.INVENNO = ambarnr
               --  AND STINVTOT.DATE_ <= DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(m, 0, tarih)), 0)
     AND  YEAR(STINVTOT.DATE_) * 100 + MONTH(STINVTOT.DATE_)<=tarih
       ),
       0
             ) stok

FROM (
SELECT DISTINCT tarih ,whouse.NR ambarnr, WHOUSE.NAME ambar,MALZEMELER.STOCKREF  FROM (
select DISTINCT  YEAR(STLINE.DATE_) * 100 + MONTH(STLINE.DATE_) tarih from LG_211_01_STLINE STLINE
WHERE YEAR(STLINE.DATE_) * 100 + MONTH(STLINE.DATE_) <=YEAR(GETDATE())*100+MONTH(GETDATE())
 ) AS T
  CROSS JOIN  L_CAPIWHOUSE WHOUSE
  CROSS JOIN  LG_211_ITEMS ITEMS
  LEFT JOIN
  (
     SELECT STLINE.STOCKREF ,   STLINE.SOURCEINDEX

    FROM LG_211_01_STLINE STLINE WITH (NOLOCK)
        LEFT JOIN LG_211_01_STFICHE STFIC
            ON STLINE.STFICHEREF = STFIC.LOGICALREF
    WHERE (
              (STFIC.TRCODE IN ( 2, 3, 4, 7, 8, 9, 35, 36, 37, 38, 39 ))
              OR (STFIC.CLIENTREF = 0)
              OR (STFIC.TRCODE IN ( 1, 5, 6, 10, 26, 30, 31, 32, 33, 34 ))
              OR (STFIC.CLIENTREF = 0)
              OR (STFIC.TRCODE IN ( 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 50, 51 ))
          )
          AND (STFIC.CANCELLED = 0)
          AND STLINE.LINETYPE IN ( 0, 1, 5, 6, 8, 9, 10 )
          AND
          (
              (STFIC.STATUS IN ( 0, 1 ))
              OR (STFIC.TRCODE IN ( 11, 12, 13, 14, 25, 26, 50, 51 ))
          )
          AND
          (
              (STFIC.STATUS IN ( 0, 1 ))
              OR (STFIC.TRCODE = 25)
          )
          AND
          (
              (STFIC.PRODSTAT = 0)
              OR (STFIC.TRCODE IN ( 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 ))
          )
          AND (STFIC.DEVIR IN ( 0, 1 ))
  ) as MALZEMELER ON MALZEMELER.STOCKREF=ITEMS.LOGICALREF AND MALZEMELER.SOURCEINDEX=NR
  WHERE WHOUSE.FIRMNR=211
  --ORDER BY tarih
  ) AS T2 LEFT JOIN LG_211_ITEMS ITEMS2 ON STOCKREF=ITEMS2.LOGICALREF
    LEFT OUTER JOIN LG_211_UNITSETL BIRIM WITH (NOLOCK)
            ON BIRIM.UNITSETREF = ITEMS2.UNITSETREF
               AND BIRIM.MAINUNIT = 1 
   order by tarih
GO

LOGO OBJECTS REGISTERLEME TÜRLERİ

On October 9, 2019December 19, 2019 By semt20In LOGO ERP, LOGO OBJECTSLeave a comment

c:\Windows\SysWOW64>REGSVR32 C:\Users\Public\Documents\TIGER3ENTERPRISE\LOBJECTS.DLL

LOGO SQL AYRINTILI MALIYET ANALIZ RAPORU

On September 17, 2019April 1, 2021 By semt20In LOGO ERP, LOGO OBJECTS6 Comments

SELECT   distinct
(SELECT
case ITEMS.CARDTYPE when 1 then 'Ticari Mal'
when 2 then 'Karma Koli'
when 3 then 'Depozitolu Mal'
when 4 then 'Sabit Kıymet'
when 10 then 'Hammadde'
when 11 then 'Yarı Mamul'
when 12 then 'Mamul'
when 13 then 'Tükletim Malı' else CONVERT(VARCHAR,ITEMS.CARDTYPE) END
  FROM LG_211_ITEMS AS ITEMS WITH (NOLOCK) WHERE ITEMS.LOGICALREF=STLINE.STOCKREF) AS [Malzeme Turu],
(SELECT ITEMS.CODE FROM LG_211_ITEMS AS ITEMS WITH (NOLOCK) WHERE ITEMS.LOGICALREF=STLINE.STOCKREF) AS [Malzeme Kodu],
(SELECT ITEMS.NAME FROM LG_211_ITEMS AS ITEMS WITH (NOLOCK) WHERE ITEMS.LOGICALREF=STLINE.STOCKREF) AS [Malzeme Adı],
(SELECT ITEMS.SPECODE FROM LG_211_ITEMS AS ITEMS WITH (NOLOCK) WHERE ITEMS.LOGICALREF=STLINE.STOCKREF) AS [Malzeme Özelkodu],
STLINE.DATE_ AS [Tarih],
100*(YEAR(STLINE.DATE_))+MONTH(STLINE.DATE_) AS [Ay],
YEAR(STLINE.DATE_) AS [Yıl],
CASE
WHEN STLINE.TRCODE IN(11,12,13,14,25,50,51) THEN (SELECT STFICHE.FICHENO FROM LG_211_01_STFICHE AS STFICHE WITH (NOLOCK) WHERE STFICHE.LOGICALREF=STLINE.STFICHEREF)
WHEN STLINE.TRCODE IN(1,2,3,6,7,8) THEN (SELECT INVOICE.FICHENO FROM LG_211_01_INVOICE AS INVOICE WITH (NOLOCK) WHERE INVOICE.LOGICALREF=STLINE.INVOICEREF)
END AS [Fiş No],
CASE
WHEN STLINE.TRCODE IN(11,12,13,14,25,50,51) THEN (SELECT STFICHE.DOCODE FROM LG_211_01_STFICHE AS STFICHE  WITH (NOLOCK) WHERE STFICHE.LOGICALREF=STLINE.STFICHEREF)
WHEN STLINE.TRCODE IN(1,2,3,6,7,8) THEN (SELECT INVOICE.DOCODE FROM LG_211_01_INVOICE AS INVOICE WITH (NOLOCK) WHERE INVOICE.LOGICALREF=STLINE.INVOICEREF)
END AS [Fiş Belge No],
CASE
STLINE.TRCODE
WHEN 14 THEN 'Devir Fişi'
WHEN 11 THEN 'Fire Fişi'
WHEN 12 THEN 'Sarf Fişi'
WHEN 13 THEN 'Üretimden Giriş Fişi'
WHEN 25 THEN 'Ambar Fişi'
WHEN 50 THEN 'Sayım Fazlası'
WHEN 51 THEN 'Sayım Eksiği'
WHEN 1 THEN 'Satınalma Faturası'
WHEN 6 THEN 'Satınalma İade Faturası'
WHEN 2 THEN 'Perkende Satış İade Faturası'
WHEN 3 THEN 'Toptan Satış İade Faturası'
WHEN 7 THEN 'Perakende Satış Faturası'
WHEN 8 THEN 'Toptan Satış Faturası'
END AS [Fiş Türü],
CASE
WHEN STLINE.IOCODE IN (0,1,2) THEN 'G'
WHEN STLINE.IOCODE IN (3,4) THEN 'Ç'
ELSE '?'
END AS [Hareket Yönü],
CASE
WHEN TRCODE IN (1,2,3,13,14,25,50) AND IOCODE IN (0,1,2) THEN
(SELECT L_CAPIDIV.NAME FROM L_CAPIDIV WITH (NOLOCK) WHERE L_CAPIDIV.FIRMNR=211 AND L_CAPIDIV.NR=(SELECT TOP 1 L_CAPIWHOUSE.DIVISNR FROM L_CAPIWHOUSE WITH (NOLOCK) WHERE L_CAPIWHOUSE.FIRMNR=211 AND L_CAPIWHOUSE.NR=STLINE.SOURCEINDEX))
ELSE
''
END  AS [Giriş İşyeri],
CASE
WHEN TRCODE IN (1,2,3,13,14,25,50) AND IOCODE IN (0,1,2) THEN
(SELECT L_CAPIWHOUSE.NAME FROM L_CAPIWHOUSE WITH (NOLOCK) WHERE L_CAPIWHOUSE.FIRMNR=211 AND L_CAPIWHOUSE.NR=STLINE.SOURCEINDEX)
ELSE
''
END  AS [Giriş Ambarı],




CASE
WHEN TRCODE IN (25) AND IOCODE IN (2) THEN
ISNULL((SELECT EMCENTER.CODE FROM LG_211_EMCENTER AS EMCENTER WITH (NOLOCK)  WHERE STLINE.VATCENTERREF=EMCENTER.LOGICALREF),'')
WHEN TRCODE IN (1,2,3,13,14,50) THEN --AND IOCODE IN (0,1,2) THEN
ISNULL((SELECT EMCENTER.CODE FROM LG_211_EMCENTER AS EMCENTER WITH (NOLOCK)  WHERE STLINE.CENTERREF=EMCENTER.LOGICALREF),'')
ELSE ''
END AS [Giriş Masraf Merkezi],

CASE
WHEN TRCODE IN (1,2,3,13,14,25,50) AND IOCODE IN (0,1,2) THEN
STLINE.AMOUNT
ELSE
0
END  AS [Giren Miktar],
(SELECT CODE FROM LG_211_UNITSETF WITH (NOLOCK) WHERE LOGICALREF=(SELECT LG_211_ITEMS.UNITSETREF FROM  LG_211_ITEMS WITH (NOLOCK) WHERE LG_211_ITEMS.LOGICALREF=STLINE.STOCKREF)) AS [Birim],
CASE
WHEN TRCODE IN (1,2,3,13,14,25,50) AND IOCODE IN (0,1,2) THEN
ROUND(STLINE.VATMATRAH/STLINE.AMOUNT,4)
ELSE
0
END  AS [Birim Fiyat (G)],
CASE
WHEN TRCODE IN (1,2,3,13,14,25,50) AND IOCODE IN (0,1,2) THEN
ROUND(STLINE.VATMATRAH/STLINE.AMOUNT,4)*STLINE.AMOUNT
ELSE
0
END AS [Giren Mal Tutarı],
CASE
WHEN TRCODE IN (1,2,3,13,14,25,50) AND IOCODE IN (0,1,2) THEN
ROUND(STLINE.VATMATRAH/STLINE.AMOUNT,4)*STLINE.AMOUNT
ELSE
0
END AS [Giriş Maliyeti],
CASE
WHEN TRCODE IN (6,7,8,11,12,25,51) AND IOCODE IN (3,4) THEN
(SELECT L_CAPIDIV.NAME FROM L_CAPIDIV WITH (NOLOCK) WHERE L_CAPIDIV.FIRMNR=211 AND L_CAPIDIV.NR=(SELECT TOP 1 L_CAPIWHOUSE.DIVISNR FROM L_CAPIWHOUSE WITH (NOLOCK) WHERE L_CAPIWHOUSE.FIRMNR=211 AND L_CAPIWHOUSE.NR=STLINE.SOURCEINDEX))
ELSE
''
END  AS [Çıkış İşyeri],
CASE
WHEN TRCODE IN (6,7,8,11,12,25,51) AND IOCODE IN (3,4) THEN
(SELECT L_CAPIWHOUSE.NAME FROM  L_CAPIWHOUSE WITH (NOLOCK) WHERE L_CAPIWHOUSE.FIRMNR=211 AND L_CAPIWHOUSE.NR=STLINE.SOURCEINDEX)
ELSE
''
END  AS [Çıkış Ambarı],

CASE
WHEN TRCODE IN (25) AND IOCODE IN (3) THEN
ISNULL((SELECT EMCENTER.CODE FROM LG_211_EMCENTER AS EMCENTER WITH (NOLOCK)  WHERE STLINE.CENTERREF=EMCENTER.LOGICALREF),'')
WHEN TRCODE IN (7,8,6,11,12) THEN--AND IOCODE IN (0,1,2) THEN
ISNULL((SELECT EMCENTER.CODE FROM LG_211_EMCENTER AS EMCENTER WITH (NOLOCK)  WHERE STLINE.CENTERREF=EMCENTER.LOGICALREF),'')
ELSE ''
END AS [Çıkış Masraf Merkezi],

CASE
WHEN TRCODE IN (6,7,8,11,12,25,51) AND IOCODE IN (3,4) THEN
STLINE.AMOUNT
ELSE
0
END  AS [Çıkış Miktar],
CASE
WHEN TRCODE IN (6,7,8,11,12,25,51) AND IOCODE IN (3,4) THEN
ROUND(STLINE.VATMATRAH/STLINE.AMOUNT,4)
ELSE
0
END  AS [Birim Fiyat (Ç)],
CASE
WHEN TRCODE IN (6,7,8,11,12,25,51) AND IOCODE IN (3,4) THEN
--ROUND(STLINE.VATMATRAH/STLINE.AMOUNT,4)*STLINE.AMOUNT
(STLINE.VATMATRAH/STLINE.AMOUNT)*STLINE.AMOUNT
ELSE
0
END AS [Çıkış Mal Tutarı],
CASE
WHEN TRCODE IN (6,7,8,11,12,25,51) AND IOCODE IN (3,4) THEN
--STLINE.AMOUNT*ROUND(STLINE.OUTCOST,4)
STLINE.AMOUNT*STLINE.OUTCOST
ELSE
0
END AS [Çıkış Maliyet Tutarı],
--ROUND(STLINE.OUTCOST,4) AS [Birim Maliyeti COST],
STLINE.OUTCOST AS [Birim Maliyeti COST],
ROUND((SELECT SUM(LV_211_01_STINVTOT.ONHAND) FROM LV_211_01_STINVTOT WITH (NOLOCK) WHERE LV_211_01_STINVTOT.INVENNO=-1 AND LV_211_01_STINVTOT.STOCKREF=STLINE.STOCKREF),4) AS [Eldeki Miktar Genel],

ROUND((SELECT SUM(LV_211_01_STINVTOT.ONHAND) FROM LV_211_01_STINVTOT WITH (NOLOCK) WHERE LV_211_01_STINVTOT.INVENNO=-1 AND LV_211_01_STINVTOT.STOCKREF=STLINE.STOCKREF),4)
*
ROUND((SELECT TOP 1 LG_211_01_STLINE.OUTCOST FROM LG_211_01_STLINE WITH (NOLOCK) WHERE LG_211_01_STLINE.OUTCOST 0 AND LG_211_01_STLINE.STOCKREF=STLINE.STOCKREF AND LG_211_01_STLINE.TRCODE IN (6,7,8,11,12,25,51) AND LG_211_01_STLINE.IOCODE IN (3,4) ORDER BY LG_211_01_STLINE.DATE_ DESC),4) AS [Envanter Tutarı]
,CASE
WHEN STLINE.BILLED='0' THEN (SELECT L_CAPIDIV.NAME FROM L_CAPIDIV WHERE L_CAPIDIV.FIRMNR=211 AND CONVERT(VARCHAR,NR)=(SELECT STFICHE.BRANCH FROM LG_211_01_STFICHE STFICHE WHERE STFICHE.LOGICALREF=STLINE.STFICHEREF))
ELSE (SELECT L_CAPIDIV.NAME FROM L_CAPIDIV WHERE L_CAPIDIV.FIRMNR=211 AND NR=(SELECT INVOICE.BRANCH FROM LG_211_01_INVOICE AS INVOICE WHERE STLINE.INVOICEREF=INVOICE.LOGICALREF))
END AS [Fiş İşyeri]

FROM LG_211_01_STLINE AS STLINE WITH (NOLOCK)
WHERE STLINE.CANCELLED=0 AND
STLINE.LINETYPE=0 AND
--STLINE.TRCODE IN (8) AND
STLINE.TRCODE IN (11,12,13,14,25,50,51,1,2,3,6,7,8) AND
STLINE.IOCODE IN(0,1,2,3,4) AND
STLINE.LPRODSTAT=0
AND YEAR(STLINE.DATE_)=2019
--AND STLINE.STOCKREF IN (SELECT LG_211_ITEMS.LOGICALREF FROM LG_211_ITEMS WHERE LG_211_ITEMS.CODE='1743')

/*
TRCODE IN (1,2,3,13,14,25,50) AND IOCODE IN (0,1,2)-- G İ R İ Ş !!!AMBAR FİŞİ 2
TRCODE IN (6,7,8,11,12,25,51) AND IOCODE IN (3,4)  -- Ç I K I Ş !!!AMBAR FİŞİ IOCODE=3
*/

C# ile SQLE Resim Kaydetmek

On September 13, 2019December 19, 2019 By semt20In C SHARPLeave a comment

resim kaydetmek için
SQL TURU VARBINARY(MAX)

c#
BM_PERSON.IMAGEDATA = DATA TURU BYTE[]
pe_GB1_IMAGE = PICTUREEDIT DEVEXPRESS COMPONENT
set = PI.BM_PERSON.IMAGEDATA = (byte[])pe_GB1_IMAGE.EditValue;
get = pe_GB1_IMAGE.EditValue = PI.BM_PERSON.IMAGEDATA;

SQL KAYDEDERKEN DIKKAT
if (B.IMAGEDATA != null)
com.Parameters.AddWithValue(“@IMAGEDATA”, B.IMAGEDATA);
else
com.Parameters.AddWithValue(“@IMAGEDATA”, System.Data.SqlTypes.SqlBinary.Null);

FAVORILERIM

On September 12, 2019December 19, 2019 By semt20In TOOLSLeave a comment

https://y2mate.com : YOUTUBE INDIRME
https://chrome.google.com/webstore/detail/adblock-for-youtube/cmedhionkhpnakcndndgjdbohmhepckk?hl=en : CHROME EXTENSION YOUTUBE VIDEO AD BLOCKER

C# SQL SERVER IN ADAPTASYONU

On September 12, 2019August 20, 2021 By semt20In C SHARPLeave a comment

                    string PERSONREFS = string.Empty;
                    if (F.PERSONREFS != null && F.PERSONREFS.Length > 0)
                        for (int i = 0; i < F.PERSONREFS.Length; i++)
                        {
                            PERSONREFS += "'" + F.PERSONREFS[i] + "'";
                            if (i != F.PERSONREFS.Length - 1)
                                PERSONREFS += ", ";
                        }
                    else
                        PERSONREFS = "-1";

LOGO HAREKET GOREN MALZEME BIRIM DEISTIRME

On September 11, 2019August 20, 2021 By semt20In LOGO ERP, LOGO OBJECTSLeave a comment

SELECT LOGICALREF,CODE,NAME FROM LG_211_UNITSETF BIRIMLER WITH(NOLOCK)




UPDATE LG_211_ITEMS SET UNITSETREF=6 WHERE CODE='yem-069' AND UNITSETREF=35
UPDATE LG_211_ITEMS SET UNITSETREF=6 WHERE CODE='yem-070' AND UNITSETREF=35

SELECT *
FROM LG_211_ITMUNITA WITH(NOLOCK) WHERE ITEMREF=MALZEMENIN LOGICALREFI

UPDATE LG_211_ITMUNITA  SET UNITLINEREF=UOMREF WHERE ITEMREF=MALZEMENIN LOGICALREFI

LOGO KAMPANYALARI GUNLUK DOVIZ KURUNA GORE GUNCELLEME

On September 5, 2019August 20, 2021 By semt20In LOGO ERP, LOGO OBJECTSLeave a comment

UPDATE LG_119_CAMPAIGN SET VARIABLEDEFS1=(
SELECT  CASE WHEN RATES4>7.0 THEN 7.0 ELSE RATES4 END FROM LG_EXCHANGE_119 WITH(NOLOCK)
WHERE convert(varchar, EDATE, 104)= convert(varchar, getdate(), 104) AND CRTYPE=20
)

SQL SERVER YIL GÜN AY 0 OLSUN BAŞINDA

On September 5, 2019August 20, 2021 By semt20In SqlLeave a comment

SELECT  YEAR(GETDATE()), FORMAT(GETDATE(),'MM'),  FORMAT(GetDate(), 'dd')

LOGO SQLINFO MALZEME SATIN ALMA VE SATIS FIYATI DOVIZ KURLARIDA DIKKATE ALINIR

On September 5, 2019December 19, 2019 By semt20In LOGO ERP, LOGO OBJECTS1 Comment

Ayrıntılı Satış raporunda yapıldı…
3 adet METIN TANIMLI ALAN TANIMLANIR

1-SAFDOVIZCINSINR,
2-SAFDOVIZCINSIMETIN,
3-SAF ÇIKTI = (ÖR 111 USD)
SATIN ALMA FIYATI(SATIS FIYATI ICIN PTYPE 2 YAPILIR)
—————–
SAFDOVIZCINSINR=_SQLINFO(“CURRENCY”,”LG_116_PRCLIST”,”PTYPE=1 AND CARDREF='”+STR(R2.logicalRef)+”‘”)

SAFDOVIZCINSIMETIN=_SQLINFO(“CURCODE”,”L_CURRENCYLIST”,”CURTYPE='”+[SAFDOVIZCINSINR]+”‘”)

SAF=_SQLINFO(“PRICE”,”LG_116_PRCLIST”,”PTYPE=1 AND CARDREF='”+STR(R2.logicalRef)+”‘”) +” ” + [SAFDOVIZCINSIMETIN])

DOVIZ KURU TEK CINSSE :
SATIN ALMA FIYATI
VAL(_SQLINFO(“PRICE”,”LG_116_PRCLIST”,”PTYPE=1 AND ACTIVE=0 AND CARDREF='”+STR(R2.logicalRef)+”‘”))

SATIS FIYATI
VAL(_SQLINFO(“PRICE”,”LG_116_PRCLIST”,”PTYPE=2 AND ACTIVE=0 AND CARDREF='”+STR(R2.logicalRef)+”‘”))

———

SQLINFO GUNLUK DOVIZ KURU
SAYI :
VAL(_SQLINFO(“RATES3″,”LG_EXCHANGE_116″,”CONVERT(DATE,EDATE)= CONVERT(DATE,GETDATE()) AND CRTYPE='”+[SAFDOVIZCINSINR]+”‘”))
VERIDE ALANA CIFT TIKLA VE
1-TABLO KOLONLARINA EKLE
2-PARAMETRELER KISMINDAN PARASAL DEGER
3-FORMAT 12345.6789