/*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
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))
LOGO LOD- SATIŞ FATURA SATIRINA İKİNCİ MİKTAR GETİRME
Events:
Modules
Table:
Events:
Sub FormShow()
Call GetData()
End Sub
Sub BeforeSave(proceed as Integer)
Call SetLineArray()
End Sub
Sub AfterSave()
AppTables[14].GetIntFieldR(RecHandle,”LOGICALREF”,gSETLOGREF)
Call SetData(gSETLOGREF)
End Sub
Modules:
Dim LineDataArr1(200) as string
Sub GetData()
AppTables[14].GetIntFieldR(RecHandle,”LOGICALREF”,iMasrefRef)
MaxGridLine=MGridLines("InvoiceGrid")
for i=0 to MaxGridLine-1
GetGridRowHandle("InvoiceGrid",i,recH)
AppTables[9].GetIntFieldR(recH,"LOGICALREF",intLineRef)
CustTables[62].Search(2,2,intLineRef)
if not (DBErr <>0) then
CustTables[62].GetFltField("MIKTAR2",fMIKTAR2)
SetCellFloat("InvoiceGrid",i,10072,fMIKTAR2)
end if
next i
End Sub
Sub SetData(intLogref as Long)
i=0
CreateQuery(Qry)
tblName = Application.GetTableName(9, Application.CompanyId, Application.FiscPerdId)
Qry.AddTable(“A”, tblName)
Qry.AddSelField(“A”, “LOGICALREF,”)
Qry.AddSelField(“A”, “INVOICELNNO”)
Str(intLogref, LogRefTxt)
MyWhereCond = “INVOICEREF = ” + LogRefTxt
Qry.AddWhereCond(MyWhereCond,1)
Qry.Execute()
if Qry.Ready = 1 then
res = Qry.First()
Do while res = 1
Qry.GetFieldValue(1, 1, MyLineRef)
Qry.GetFieldValue(2, 1, MyLineNo)
if MyLineRef<>0 then
CustTables[62].Search(2,2,MyLineRef)
if (DBErr <>0) then
CustTables[62].NewRecord()
CustTables[62].SetIntField("PARLOGREF",MyLineRef)
CustTables[62].SetIntField("INVFICHEREF",intLogref)
CustTables[62].SetFltField("MIKTAR2",LineDataArr1[MyLineNo - 1])
else
CustTables[62].SetFltField("MIKTAR2",LineDataArr1[MyLineNo - 1])
end if
CustTables[62].UpdateRecord()
end if
res = Qry.Next()
Loop
end if
End Sub
Sub SetLineArray()
MaxGridLine=MGridLines(“InvoiceGrid”)
i=0
for i=0 to MaxGridLine-1
GetCellFloat(“InvoiceGrid”,i,10072,LineDataArr1[i])
next i
End Sub
C# SQL GET LOGICALREF
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
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
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 paytrans to branch
USE [YAR2016]
GO
/****** Object: View [dbo].[BM_301_PAYTRANSTOBRANCH] Script Date: 06.07.2020 13:11:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BM_301_PAYTRANSTOBRANCH] AS
SELECT TOP 100 PERCENT
LGMAIN.LOGICALREF,
CASE LGMAIN.MODULENR WHEN 7 THEN BTRNS.BRANCH WHEN 4 THEN INVFC.BRANCH WHEN 5 THEN CTRNS.BRANCH
WHEN 6 THEN RLFIC.BRANCH WHEN 3 THEN ORFIC.BRANCH WHEN 10 THEN CASHTR.BRANCH
WHEN 61 THEN CTRNS.BRANCH WHEN 62 THEN CTRNS.BRANCH ELSE '' END [İşyeri Kodu]
FROM
LG_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.MODULENR=3 AND
((ORFIC.BRANCH NOT IN (-2))))AND
(ORFIC.CANCELLED=0))OR((LGMAIN.MODULENR=4 AND
((INVFC.BRANCH NOT IN (-2))))AND
(INVFC.CANCELLED=0))OR((LGMAIN.MODULENR IN (5,61,62) AND
((CTRNS.BRANCH NOT IN (-2))))AND
(CTRNS.CANCELLED=0))OR((LGMAIN.MODULENR=6 AND
((RLFIC.BRANCH NOT IN (-2))))AND
(RLFIC.CANCELLED=0))OR((LGMAIN.MODULENR=7 AND
((BTRNS.BRANCH NOT IN (-2))))AND
(BTRNS.CANCELLED=0))OR((LGMAIN.MODULENR=10 AND
((CASHTR.BRANCH NOT IN (-2))))AND(CASHTR.CANCELLED=0)))) AND (LGMAIN.TOTAL - LGMAIN.PAID > 0) AND (LGMAIN.PAIDINCASH = 0)
GO
LOGO SQL BELIRLI TARIH ARASI MALZEMELERIN SATISLARI
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
(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
Blog of Sem 