--BORÇ TAKİPTE OLUP DİĞER MODULLERDE OLMAYAN
SELECT *
FROM LG_001_01_PAYTRANS
WHERE
(
(MODULENR=4 AND TRCODE IN (6,7,8,9,41) AND SIGN=1) OR
(MODULENR=5 AND TRCODE IN (2,3) AND SIGN=1) OR
(MODULENR=6 AND TRCODE IN (3,4) AND SIGN=1)OR
(MODULENR=7 AND TRCODE IN (4) AND SIGN=1)OR
(MODULENR=10 AND TRCODE IN (2) AND SIGN=1)OR
(MODULENR=4 AND TRCODE IN (1,2,3,4,26,42) AND SIGN=0)OR
(MODULENR=5 AND TRCODE IN (1,4) AND SIGN=0)OR
(MODULENR=6 AND TRCODE IN (1,2) AND SIGN=0)OR
(MODULENR=7 AND TRCODE IN (3) AND SIGN=0)OR
(MODULENR=10 AND TRCODE IN (1) AND SIGN=0)
)
OR
(
((MODULENR = 0) AND (TRCODE=0) AND (FICHEREF >= 0))OR
((MODULENR>=0) AND (TRCODE=0)AND(FICHEREF=0)) OR
((MODULENR=0) AND (TRCODE>=0)AND(FICHEREF=0))
)
OR
(
(MODULENR=4 AND FICHEREF NOT IN
(SELECT LOGICALREF FROM LG_001_01_INVOICE)) OR
(MODULENR=5 AND FICHEREF NOT IN
(SELECT LOGICALREF FROM LG_001_01_CLFLINE)) OR
(MODULENR=6 AND FICHEREF NOT IN
(SELECT LOGICALREF FROM LG_001_01_CSROLL)) OR
(MODULENR=7 AND FICHEREF NOT IN
(SELECT LOGICALREF FROM LG_001_01_BNFLINE)) OR
(MODULENR=10 AND FICHEREF NOT IN
(SELECT LOGICALREF FROM LG_001_01_KSLINES))
)
--FİŞİ OLMAYAN CARİ HESAP FİŞLERİ
SELECT * FROM LG_001_01_CLFLINE
WHERE MODULENR=5 AND SOURCEFREF NOT IN(SELECT LOGICALREF FROM LG_001_01_CLFICHE)
--CARI KARTI OLMAYAN HAREKETLER
SELECT CLIENTREF FROM LG_001_01_CLFLINE
WHERE CLIENTREF NOT IN(SELECT LOGICALREF FROM LG_001_CLCARD;
--CARİ HESAP HAREKET HATALI SATIRLAR
SELECT * FROM LG_001_01_CLFLINE WHERE MODULENR=4 AND TRCODE=38 AND
SOURCEFREF NOT IN (SELECT LOGICALREF FROM LG_001_01_INVOICE)
Merhaba,
Yedek aldıktan sonra işlemleri yapınız.
---------FİŞİ OLMAYAN CARİ HESAP HAREKETLERİ
SELECT * FROM LG_001_01_CLFLINE
WHERE MODULENR=5 AND SOURCEFREF NOT IN (SELECT LOGICALREF FROM LG_001_01_CLFICHE)
---------CARI KARTI OLMAYAN HAREKETLER
SELECT CLIENTREF FROM LG_001_01_CLFLINE
WHERE CLIENTREF NOT IN (SELECT LOGICALREF FROM LG_001_CLCARD)
---------CARİ HESAP HAREKET HATALI SATIRLAR
SELECT * FROM LG_001_01_CLFLINE WHERE MODULENR=4 AND TRCODE=38 AND
SOURCEFREF NOT IN (SELECT LOGICALREF FROM LG_001_01_INVOICE)
***********************
BORÇ TAKİP İLE CARİ HESAP HAREKETLERİNİ KARŞILAŞTIRIYOR
SELECT * FROM LG_208_01_PAYTRANS P WITH(NOLOCK) LEFT OUTER JOIN LG_208_01_CLFLINE L WITH(NOLOCK) ON P.FICHEREF=L.SOURCEFREF AND P.MODULENR=L.MODULENR WHERE P.MODULENR NOT IN (5,61,62,63,64) AND L.LOGICALREF IS NULL UNION
SELECT * FROM LG_208_01_PAYTRANS P WITH(NOLOCK) LEFT OUTER JOIN LG_208_01_CLFLINE L WITH(NOLOCK) ON P.FICHEREF=L.LOGICALREF AND P.MODULENR=L.MODULENR WHERE P.MODULENR IN (5,61,62,63,64) AND L.LOGICALREF IS NULL
*****************
CARİ HESAP HAREKETLERİ İLE BORÇ TAKİP KARŞILAŞTIRIYOR
SELECT * FROM LG_208_01_CLFLINE L WITH(NOLOCK) LEFT OUTER JOIN LG_208_01_PAYTRANS P WITH(NOLOCK) ON L.SOURCEFREF=P.FICHEREF AND L.MODULENR=P.MODULENR WHERE L.MODULENR NOT IN (5,61,62,63,64) AND L.AMOUNT>0
-- AND L.CLIENTREF=140
AND P.LOGICALREF IS NULL
UNION
SELECT
*
FROM
LG_208_01_CLFLINE L WITH(NOLOCK)
LEFT OUTER JOIN LG_208_01_PAYTRANS P WITH(NOLOCK) ON L.LOGICALREF=P.FICHEREF AND L.MODULENR=P.MODULENR
WHERE
L.MODULENR IN (5,61,62,63,64)
AND L.AMOUNT>0
-- AND L.CLIENTREF=140
AND P.LOGICALREF IS NULL
SELECT * FROM LG_208_01_PAYTRANS WHERE MODULENR=10 AND FICHEREF=19372
SELECT * FROM LG_208_01_CLFLINE WHERE MODULENR=10 AND SOURCEFREF=19372
------CARI KARTI OLMAYAN HAREKETLER
SELECT CLIENTREF FROM LG_001_01_CLFLINE
WHERE CLIENTREF NOT IN (SELECT LOGICALREF FROM LG_001_CLCARD)
------CARİ HESAP HAREKET HATALI SATIRLAR
SELECT * FROM LG_001_01_CLFLINE WHERE MODULENR=4 AND TRCODE=38 AND
SOURCEFREF NOT IN (SELECT LOGICALREF FROM LG_001_01_INVOICE)
-------FATURASI OLUP CARI HESAP HAREKETİ OLMAYANLAR
SELECT * FROM LG_001_01_INVOICE WHERE TRCODE=8 AND LOGICALREF NOT IN (
SELECT SOURCEFREF FROM LG_001_01_CLFLINE WHERE MODULENR=4 AND TRCODE=38 )
------FİŞİ OLMAYAN CARİ HESAP HAREKETLERİ
SELECT * FROM LG_001_01_CLFLINE
WHERE MODULENR=5 AND SOURCEFREF NOT IN (SELECT LOGICALREF FROM LG_001_01_CLFICHE)
-----CARI KARTI OLMAYAN HAREKETLER
SELECT CLIENTREF FROM LG_001_01_CLFLINE
WHERE CLIENTREF NOT IN (SELECT LOGICALREF FROM LG_001_CLCARD)
----CARİ HESAP HAREKETLERİNDE OLUP CARİ HESAP FİŞİ BULUNMAYAN KAYITLAR İÇİN
SELECT * FROM LG_001_01_CLFLINE WHERE MODULENR=5 AND SOURCEFREF NOT IN
(SELECT LOGICALREF FROM LG_001_01_CLFICHE)
----CARİ HESAP FİŞİ OLAN ANCAK CARİ HAREKETLERDE GÖZÜKMEYEN KAYITLAR İÇİN
SELECT * FROM LG_001_01_CLFICHE WHERE LOGICALREF NOT IN
(SELECT SOURCEFREF FROM LG_001_01_CLFLINE WHERE MODULENR=5)
Category: Sql
LOGO SQL DETAYLI FATURA RAPORU
SELECT ROW_NUMBER() OVER( ORDER BY INTLOGICALREF ASC) AS INTROWID, * FROM (
SELECT
LINETYPE,
'520' TXTFIRMA,
INVOICE.LOGICALREF INTLOGICALREF,
INVOICE.TRCODE INTFATURATURU,
(CASE
WHEN INVOICE.TRCODE=1 THEN 'Satınalma Faturası'
WHEN INVOICE.TRCODE=2 THEN 'Perakende Satış İade Faturası'
WHEN INVOICE.TRCODE=3 THEN 'Toptan Satış İade Faturası'
WHEN INVOICE.TRCODE=4 THEN 'Alınan Hizmet Faturası'
WHEN INVOICE.TRCODE=5 THEN 'Alınan Proforma Faturası'
WHEN INVOICE.TRCODE=6 THEN 'Satınalma İade Faturası'
WHEN INVOICE.TRCODE=7 THEN 'Perakende Satış Faturası'
WHEN INVOICE.TRCODE=8 THEN 'Toptan Satış Faturası'
WHEN INVOICE.TRCODE=9 THEN 'Verilen Hizmet Faturası'
WHEN INVOICE.TRCODE=10 THEN 'Verilen Proforma Faturası'
WHEN INVOICE.TRCODE=12 THEN 'Alınan Vade Farkı Faturası'
WHEN INVOICE.TRCODE=13 THEN 'Satınalma Fiyat Farkı Faturası'
WHEN INVOICE.TRCODE=14 THEN 'Satış Fiyat Farkı Faturası'
WHEN INVOICE.TRCODE=26 THEN 'Müstahsil Makbuzu'
WHEN INVOICE.TRCODE=1 THEN 'Satınalma Faturası'
WHEN INVOICE.TRCODE=2 THEN 'Perakende Satış İade Faturası'
WHEN INVOICE.TRCODE=3 THEN 'Toptan Satış İade Faturası'
WHEN INVOICE.TRCODE=4 THEN 'Alınan Hizmet Faturası'
WHEN INVOICE.TRCODE=5 THEN 'Alınan Proforma Faturası'
WHEN INVOICE.TRCODE=6 THEN 'Satınalma İade Faturası'
WHEN INVOICE.TRCODE=7 THEN 'Perakende Satış Faturası'
WHEN INVOICE.TRCODE=8 THEN 'Toptan Satış Faturası'
WHEN INVOICE.TRCODE=9 THEN 'Verilen Hizmet Faturası'
WHEN INVOICE.TRCODE=10 THEN 'Verilen Proforma Faturası'
WHEN INVOICE.TRCODE=12 THEN 'Alınan Vade Farkı Faturası'
WHEN INVOICE.TRCODE=13 THEN 'Satınalma Fiyat Farkı Faturası'
WHEN INVOICE.TRCODE=14 THEN 'Satış Fiyat Farkı Faturası'
WHEN INVOICE.TRCODE=26 THEN 'Müstahsil Makbuzu'
END
) as TXTFATURATIPI,
INVOICE.FICHENO TXTFATURANO,
INVOICE.DATE_ as TRHFATURATARIHI,
INVOICE.DOCODE AS TXTBELGENO,
INVOICE.SPECODE AS TXTOZELKOD,
CLCARD.CODE as LNGMUSTERIKOD,
CLCARD.DEFINITION_ as LNGMUSTERIAD,
(SELECT P.CODE FROM LG_520_PAYPLANS P WHERE P.LOGICALREF=INVOICE.PAYDEFREF) TXTODEMELER,
INVOICE.TRADINGGRP TXTTICARIISLEMGRUBU,
(SELECT S.CODE+'-'+S.DEFINITION_ FROM LG_SLSMAN S WHERE S.FIRMNR=520 AND S.LOGICALREF= INVOICE.SALESMANREF) TXTSATISELEMANI,
INVOICE.CYPHCODE TXTYETKIKODU,
(SELECT CAST(D.NR AS VARCHAR)+'-'+ D.NAME FROM L_CAPIDIV D WHERE D.FIRMNR=520 AND D.NR=INVOICE.BRANCH) TXTISYERI,
(SELECT CAST(D.NR AS VARCHAR)+'-'+ D.NAME FROM L_CAPIDEPT D WHERE D.FIRMNR=520 AND D.NR=INVOICE.DEPARTMENT) TXTBOLUM,
(SELECT CAST(D.NR AS VARCHAR)+'-'+ D.NAME FROM L_CAPIFACTORY D WHERE D.FIRMNR=520 AND D.NR=INVOICE.FACTORYNR) TXTFABRIKA,
(SELECT CAST(D.NR AS VARCHAR)+'-'+ D.NAME FROM L_CAPIWHOUSE D WHERE D.FIRMNR=520 AND D.NR=INVOICE.SOURCEINDEX) TXTAMBAR,
(SELECT D.CODE+'-'+ D.NAME FROM LG_520_PROJECT D WHERE D.LOGICALREF=INVOICE.PROJECTREF) TXTPROJEKODU,
RTRIM(INVOICE.GENEXP1+' '+INVOICE.GENEXP2+' '+INVOICE.GENEXP3+' '+INVOICE.GENEXP4) AS TXTACIKLAMAFIS,
INVOICE.TOTALEXPENSES DBLYPBTOPLAMMASRAF,
INVOICE.TOTALDISCOUNTS DBLYPBTOPLAMINDIRIM,
INVOICE.NETTOTAL-INVOICE.TOTALVAT DBLYPBTOPLAM,
INVOICE.TOTALEXADDTAX DBLYPBTOPLAMEKVERGI,
INVOICE.TOTALVAT DBLYPBTOPLAMKDV,
INVOICE.NETTOTAL DBLYPBTOPLAMNET,
ROUND(INVOICE.TOTALEXPENSES/NULLIF(INVOICE.TRRATE,0),2) DBLIDTOPLAMMASRAF,
ROUND(INVOICE.TOTALDISCOUNTS/NULLIF(INVOICE.TRRATE,0),2) DBLIDTOPLAMINDIRIM,
ROUND((INVOICE.NETTOTAL-INVOICE.TOTALVAT)/NULLIF(INVOICE.TRRATE,0),2) DBLIDTOPLAM,
ROUND(INVOICE.TOTALEXADDTAX/NULLIF(INVOICE.TRRATE,0),2) DBLIDTOPLAMEKVERGI,
ROUND(INVOICE.TOTALVAT/NULLIF(INVOICE.TRRATE,0),2) DBLIDTOPLAMKDV,
ROUND(INVOICE.NETTOTAL/NULLIF(INVOICE.TRRATE,0),2) DBLIDTOPLAMNET,
CASE CLCARD.CCURRENCY WHEN 0 THEN 'TL' WHEN 1 THEN 'USD' WHEN 17 THEN 'GBP' WHEN 20 THEN 'EUR' ELSE '' END TXTCARIDOVIZ,
CASE INVOICE.TRCURR WHEN 0 THEN 'TL' WHEN 1 THEN 'USD' WHEN 17 THEN 'GBP' WHEN 20 THEN 'EUR' ELSE '' END TXTISLEMDOVIZ,
CASE LINETYPE WHEN 0 THEN 'MALZEME' WHEN 1 THEN 'PROMOSYON' WHEN 2 THEN 'INDIRIM' WHEN 3 THEN 'MASRAF' WHEN 4 THEN 'HIZMET' ELSE '' END TXTTUR,
CASE WHEN STLINE.LINETYPE=4 THEN SRV.CODE ELSE INV.CODE END AS TXTKOD,
CASE WHEN STLINE.LINETYPE=4 THEN SRV.DEFINITION_ ELSE INV.NAME END TXTACIKLAMA,
COALESCE(
CASE STLINE.UINFO1
WHEN 0 THEN STLINE.AMOUNT
ELSE STLINE.AMOUNT*STLINE.UINFO2/STLINE.UINFO1
END, STLINE.AMOUNT/1000) DBLMIKTAR,
CASE WHEN LINETYPE=4 THEN UNTS.CODE ELSE UNT.CODE END TXTBIRIM,
ROUND(STLINE.PRICE,2) DBLBIRIMFIYAT,
CASE STLINE.PRCURR WHEN 0 THEN 'TL' WHEN 1 THEN 'USD' WHEN 17 THEN 'GBP' WHEN 20 THEN 'EUR' ELSE '' END TXTDOVIZTURU,
ROUND(STLINE.DISCPER,2) DBLYUZDE,
ROUND(STLINE.VAT,2) DBLKDV,
ROUND(STLINE.TOTAL,2) DBLTUTAR,
ROUND(STLINE.VATAMNT,2) DBLKDVTUTARI,
ROUND(STLINE.VATMATRAH,2) DBLNETTUTAR
FROM LG_520_01_INVOICE AS INVOICE WITH (NOLOCK)
LEFT JOIN LG_520_01_STLINE AS STLINE ON INVOICE.LOGICALREF = STLINE.INVOICEREF
LEFT JOIN LG_520_ITEMS INV ON INV.LOGICALREF=STLINE.STOCKREF
LEFT JOIN LG_520_SRVCARD SRV ON SRV.LOGICALREF=STLINE.STOCKREF
LEFT JOIN LG_520_CLCARD CLCARD ON CLCARD.LOGICALREF=INVOICE.CLIENTREF
LEFT JOIN LG_520_EMUHACC EMUH ON EMUH.LOGICALREF=STLINE.ACCOUNTREF
LEFT JOIN LG_520_UNITSETL UNT ON UNT.UNITSETREF=INV.UNITSETREF AND LINENR=1
LEFT JOIN LG_520_UNITSETL UNTS ON UNTS.UNITSETREF=SRV.UNITSETREF AND UNTS.LINENR=1 WHERE(INVOICE.TRCODE IN(1/*,4,5,6,12,13,26,2,3,7,8,9,10,14*/))) AS T
LOGO YIL AY BAZLI MALZEME SATIŞLARI
SELECT * FROM (
SELECT
ITMSM.YEAR_ YIL,
ITMSM.MONTH_ AY,
I.CODE URUNKODU,
I.NAME URUNADI,
SUM(ITMSM.SALES_AMOUNT) SATIS_MIKTAR
FROM
LV_119_01_STINVENS ITMSM
WITH(NOLOCK)
LEFT JOIN LG_119_ITEMS I WITH(NOLOCK) ON I.LOGICALREF=ITMSM.STOCKREF
WHERE
(ITMSM.INVENNO = -1) AND (( ITMSM.MONTH_ >= 1 ) AND ( ITMSM.MONTH_ <= 12 ) ) AND (ITMSM.MTRLINC = 0) AND (ITMSM.VIRTUALINVEN = 0)
GROUP BY ITMSM.YEAR_,
ITMSM.MONTH_,I.CODE,I.NAME ) AS T ORDER BY YIL,AY,URUNADI
SQL SERVER COLUMNLARI DATATABLEA DİNAMİK OLARAK AKTARMA
public static DataTable getReadingTableFromSchema()
{
string TARIH = DateTime.Now.Month.ToString() + "-" + DateTime.Now.Day.ToString() + "-" + DateTime.Now.Year.ToString();
using (SqlConnection conn = new SqlConnection(CS))
{
string sql = string.Format("EXEC[LG_118_ONAY] '','{0}'", TARIH);
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
DbDataAdapter da = new SqlDataAdapter(cmd);
DataTable dtbl = new DataTable();
da.FillSchema(dtbl, SchemaType.Source);
return dtbl;
}
}
private void INITIALIZEGV()
{
DataTable DTNEW = getReadingTableFromSchema();
DataTable DT = _M.SELECT_LISTE();
string TARIH = DateTime.Now.Month.ToString() + "-" + DateTime.Now.Day.ToString() + "-" + DateTime.Now.Year.ToString();
foreach (DataRow item in DT.Rows)
{
string STUDENTCODE = item[0].ToString();
DataRow s = _M.SELECT_ONAY(CODE, TARIH).Rows[0];
DTNEW.ImportRow(s);
}
gc_CONTROL1.DataSource = DTNEW;
}
SQL SERVER DB TABLO YAPILARI ve TAVSİYELERİ
sql server db tablo yapı tavsiyeleri
full kucuk harf ingilizce karakter kullan,Ornek İplik Adı = iplik_adi gibi
referanslı int alanların başında id_malzeme gibi şeklinde kullan(böyle olmasının sebebi id_ görürse otomatik olarak int varsaysın table script generator)
primarykey ismi = id_ olsun
varchar yerine nvarchar kullan
LOGO SQL SERVER
TRIGGERLE İŞLEMLERDE ÖZELLİKLE UPDATE TRIGGERINDE SET NOCOUNT ON YAP
Entegrasyon yazılımı yaparken dikkat edilmesi gereken hususlar
entegrasyon yapacak arkadaşlar için notlar:
*Entegrasyon yapılırken önemli olan entegrasyon yapılacak tarafta olması gereken verilerin dışında inserted_datetime, modified_datetime ve uniuqeid yoksa eklettirilmesi çok büyük avantajdır(dublicate kayıtları önler).
*synctable = Entegrasyon nereye olacaksa tablolara entegrsayon başladığında sync=1 olacak bir tablo yaratılırsa ve bitince tekrar 0 yaparsa çakışmayı onler.
*syncdatetimetable = Entegrasyon yapılan her modul icin bir tablo daha yaratılıp modul ismi ve son entegrasyon tarihi alanları olursa başın ağrımaz(dublicate kayıtları önler).
LOGO ÜRÜN BARKOD VE SATIŞ FİYATI LİSTESİ SQL
select
ISNULL(B.BARCODE,'') ProductCode,
ISNULL(I.NAME,'') ProductName,
ISNULL((SELECT TOP 1 ROUND(PRICE,2) FROM LG_320_PRCLIST P WITH(NOLOCK) WHERE CARDREF = I.LOGICALREF AND ACTIVE = 0 AND PTYPE = 2 AND CLIENTCODE = '' order by P.LOGICALREF DESC ),0) Price
FROM dbo.LG_320_ITEMS I WITH(NOLOCK) LEFT JOIN LG_320_UNITBARCODE B ON B.ITEMREF=I.LOGICALREF WHERE I.CODE<>'ÿ'AND ISNULL(B.BARCODE,'') <> ''
LOGO BANKA TAHSİLE VERİLEN ÇEKLER SQL
SELECT
CSROLL.BRANCH ISYERIKOD,
(SELECT D.NAME FROM L_CAPIDIV D WHERE D.FIRMNR={0} AND D.NR=CSROLL.BRANCH) ISYERI,
CSROLL.DEPARTMENT BOLUMKOD,
(SELECT D.NAME FROM L_CAPIDEPT D WHERE D.FIRMNR={0} AND D.NR=CSROLL.DEPARTMENT) BOLUM,
CSROLL.CYPHCODE YETKIKODU,
CSROLL.ROLLNO BORDRONO,
CSCARD.PORTFOYNO,
CSCARD.NEWSERINO CEKNO,
CSCARD.DUEDATE VADE,
(SELECT B.CODE FROM LG_{0}_BANKACC B WHERE B.LOGICALREF=CSROLL.CARDREF) BANKAKOD,
(SELECT B.DEFINITION_ FROM LG_{0}_BANKACC B WHERE B.LOGICALREF=CSROLL.CARDREF) BANKA,
(SELECT C.CODE FROM LG_{0}_CLCARD C WHERE C.LOGICALREF= (SELECT CSROLL2.CARDREF FROM LG_{0}_{1}_CSROLL CSROLL2 WHERE CSROLL2.TRCODE=1 AND
CSROLL2.LOGICALREF IN (SELECT CSTRANS2.ROLLREF FROM LG_{0}_{1}_CSTRANS CSTRANS2 WHERE CSTRANS2.CSREF =CSCARD.LOGICALREF)) ) CARIKOD,
(SELECT C.DEFINITION_ FROM LG_{0}_CLCARD C WHERE C.LOGICALREF= (SELECT CSROLL2.CARDREF FROM LG_{0}_{1}_CSROLL CSROLL2 WHERE CSROLL2.TRCODE=1 AND
CSROLL2.LOGICALREF IN (SELECT CSTRANS2.ROLLREF FROM LG_{0}_{1}_CSTRANS CSTRANS2 WHERE CSTRANS2.CSREF =CSCARD.LOGICALREF)) ) CARI,
CASE WHEN CSTRANS.TRCODE=5 THEN CSROLL.TOTAL ELSE 0 END TUTAR
FROM LG_{0}_{1}_CSCARD CSCARD LEFT JOIN LG_{0}_{1}_CSTRANS CSTRANS ON CSTRANS.CSREF=CSCARD.LOGICALREF
LEFT JOIN LG_{0}_{1}_CSROLL CSROLL ON CSROLL.LOGICALREF = CSTRANS.ROLLREF
WHERE CSCARD.PORTFOYNO='PORTFOYD' AND CSTRANS.TRCODE IN (5)
Blog of Sem 