Category: TOOLS
YOUTUBE LISTE MP3 OLARAK İNDİR
ilk olarka yt-dlp (cmd ile çalışmakta) indir :
Sonrasında ffmpegi indir ve bir yere extract et (indirdikten sonra enviormant pathe ekle extract ettiğin yer)
cmd çalıştır ve yt-dlp exenin olduğu yere git ve örnek komut (youtube url tırnaklar önemli):
yt-dlp -x --audio-format mp3 --audio-quality 128K "https://www.youtube.com/watch?v=2zQEeDliNx0&list=PLRiHAPb9-vYqOgctytfZ-OPmiJD25nbNY"
VIDEO INDIRMEK ICINDE:
yt-dlp.exe --list-formats https://www.youtube.com/watch?v=JsxZGRWknWY
yt-dlp -f 95 https://www.youtube.com/watch?v=JsxZGRWknWY -o %%(title)s.%%(ext)s
c# dapper transaction with object
dapper version 2.0.123
public static string InsertOrionClcard(BMSV_CLIENT SD)
{
try
{
using (var connection = new SqlConnection(string.Format(@"Server={0}; Database={1}; User Id ={2};Password ={3};Application Name={4}", CFG.OTHERCARIMALZEMESERVER, CFG.OTHERCARIMALZEMEDATABASE, CFG.OTHERCARIMALZEMEUSERNAME, CFG.OTHERCARIMALZEMEPASSWORD, CFG.ORIONCARIMALZEMEUSERCODE)))
{
connection.Open();
using (var transaction = connection.BeginTransaction())
{
try
{
string TableName = "TBL_CARI_MAIN";
TBL_CARI_MAIN cari = new TBL_CARI_MAIN();
cari.AKTIF = true;
cari.CARIKODU = SD.Code;
cari.CARIADI = SD.Name;
cari.TIPID = 3;
cari.VERGIDAIRESI = SD.TaxLocation;
cari.VERGINO = SD.TaxNumber;
cari.KREDILIMITI = 0;
cari.RISKLIMITI = 0;
cari.INDIRIM = 0;
cari.BAKIYE = 0;
cari.GRUPID = 1;
cari.DOVIZID = 1;
cari.ALISFIYATID = 1;
cari.SATISFIYATID = 2;
cari.ODEMEGUNU = 0;
cari.TAKSITSAYISI = 0;
cari.RESIM = "";
cari.WEBADRESI = "";
cari.PASSWORD = "";
cari.ACIKLAMA = "";
cari.EMAIL = SD.Email;
cari.CREATEDATE = SD.CreatedDate;
cari.CREATEUSERID = 1;
string sql = $@"INSERT INTO {TableName} (AKTIF,CARIKODU,CARIADI,TIPID,VERGIDAIRESI,VERGINO,KREDILIMITI,RISKLIMITI,INDIRIM,BAKIYE,GRUPID,DOVIZID,ALISFIYATID,SATISFIYATID,ODEMEGUNU,TAKSITSAYISI,RESIM,WEBADRESI,PASSWORD,ACIKLAMA,EMAIL,CREATEDATE,CREATEUSERID) VALUES (@AKTIF,@CARIKODU,@CARIADI,@TIPID,@VERGIDAIRESI,@VERGINO,@KREDILIMITI,@RISKLIMITI,@INDIRIM,@BAKIYE,@GRUPID,@DOVIZID,@ALISFIYATID,@SATISFIYATID,@ODEMEGUNU,@TAKSITSAYISI,@RESIM,@WEBADRESI,@PASSWORD,@ACIKLAMA,@EMAIL,@CREATEDATE,@CREATEUSERID); SELECT CAST(SCOPE_IDENTITY() as int);";
int CARIID = connection.Query<int>(sql, cari, transaction).FirstOrDefault();
TBL_CARI_ADRES cari_adres = new TBL_CARI_ADRES();
cari_adres.CARIID = CARIID;
cari_adres.SUBEADI = "";
cari_adres.ADRES = SD.Address;
cari_adres.IL = "";
cari_adres.ILCE = "";
cari_adres.POSTAKODU = "";
cari_adres.ADRES = SD.Address;
cari_adres.TELEFON = SD.Phone1;
cari_adres.FAX = SD.Fax;
cari_adres.BOLGEID = 1;
cari_adres.ISMERKEZ = true;
cari_adres.CREATEDATE = (DateTime)SD.CreatedDate;
cari_adres.CREATEUSERID = 1;
cari_adres.ROTANO = 1;
connection.Execute($@"INSERT INTO TBL_CARI_ADRES (CARIID,SUBEADI,ADRES,IL,ILCE,POSTAKODU,TELEFON,FAX,BOLGEID,ISMERKEZ,CREATEDATE,CREATEUSERID,ROTANO) VALUES (@CARIID,@SUBEADI,@ADRES,@IL,@ILCE,@POSTAKODU,@TELEFON,@FAX,@BOLGEID,@ISMERKEZ,@CREATEDATE,@CREATEUSERID,@ROTANO)", cari_adres, transaction);
transaction.Commit();
return "ok";
}
catch (Exception E)
{
try { transaction.Rollback(); } catch { }
return E.Message;
}
finally
{
try { transaction.Dispose(); } catch { }
try { if (connection.State != ConnectionState.Closed) connection.Close(); }
catch
{
}
}
}
}
}
catch (Exception E)
{
LOGYAZ("InsertOrionClcard", E);
return E.Message;
}
}
C# DYNAMIC DATATABLE TO LIST
public static List<T> DataTableToList<T>(DataTable dataTable) where T : new()
{
List<T> list = new List<T>();
var props = typeof(T).GetProperties(System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.Instance);
foreach (DataRow row in dataTable.Rows)
{
T item = new T();
for (int i = 0; i < props.Length; i++)
{
if (dataTable.Columns.Contains(props[i].Name))
{
object value = row[props[i].Name];
try { props[i].SetValue(item, value); } catch { }
}
}
list.Add(item);
}
return list;
}
//USAGE: List<B2BLogoIntegration_FicheLine> fL= HELPER.DataTableToList<B2BLogoIntegration_FicheLine>(dt);
LOD CARİ İŞYERİ AMBAR FATURA
Amaç: Logoda cari hesap kartında ilgili carilere işyeri ambarlar set edilip faturada cari seçerken otomatik olarak set edilmiş bilgileri almak.
Tiger ent version 2.95
' ///////////////////////////////////////////
' LOD Source Code Text File
' Date :2.12.2024 09:19:21
' ///////////////////////////////////////////
' ///////////////////////////////////////////
' File Name : FRM_CariHesapKarti
' Name : FRM_CariHesapKarti
' Code Type : Event
' ///////////////////////////////////////////
Sub FormShow()
HideControl("_N_Isyeri_Nr")
HideControl("_N_Ambar_Nr")
HideControl("_T_Isyeri_Name")
HideControl("_T_Ambar_Name")
arrIsyeri = Application.GetFirmDivisions(Application.CompanyId) 'https://docs.logo.com.tr/public/wua/logo-object-designer/lod-kuetuephanesi/lod-application
for i = 0 to arrIsyeri.Size-1
_dNr = arrIsyeri[i].Nr
_dName = arrIsyeri[i].Name
AddItem("_C_Isyeri",_dName,_dNr) 'https://docs.logo.com.tr/public/wua/logo-object-designer/lod-kuetuephanesi/lod-form
next
getIsyeriName()
arrAmbar = Application.GetFirmWarehouses(Application.CompanyId, 0, 0) 'https://docs.logo.com.tr/public/wua/logo-object-designer/lod-kuetuephanesi/lod-application
for i = 0 to arrAmbar.Size-1
_wNr = arrAmbar[i].Nr
_wName = arrAmbar[i].Name
AddItem("_C_Ambar",_wName,_wNr) 'https://docs.logo.com.tr/public/wua/logo-object-designer/lod-kuetuephanesi/lod-form
next
getAmbarName()
End Sub
Sub ComboChange(ctrl as String, ID as Integer, index as Integer)
IF ctrl="_C_Ambar" THEN
GetTextVal("_C_Ambar",t_C_Ambar) 'https://docs.logo.com.tr/public/wua/logo-object-designer/lod-kuetuephanesi/lod-form
SetIntVal("_N_Ambar_Nr",index)
SetTextVal("_T_Ambar_Name",t_C_Ambar)
END IF
IF ctrl="_C_Isyeri" THEN
GetTextVal("_C_Isyeri",t_C_Isyeri) 'https://docs.logo.com.tr/public/wua/logo-object-designer/lod-kuetuephanesi/lod-form
SetIntVal("_N_Isyeri_Nr",index)
SetTextVal("_T_Isyeri_Name",t_C_Isyeri)
END IF
End Sub
' ///////////////////////////////////////////
' File Name : FRM_CariHesapKarti
' Name : FRM_CariHesapKarti
' Code Type : Module
' ///////////////////////////////////////////
Sub getIsyeriName()
'https://docs.logo.com.tr/public/wua/logo-object-designer/lod-baslangic
CreateQuery(QryDName)
GetIntVal("_N_Isyeri_Nr",v_N_Isyeri_Nr)
SetIntVal("_C_Isyeri",v_N_Isyeri_Nr)
STR(v_N_Isyeri_Nr,STR_v_N_Isyeri_Nr)
STR(Application.CompanyId,STR_ApplicationCompanyId)
SqlDName = "select TOP 1 NAME from L_CAPIDIV WHERE FIRMNR="+STR_ApplicationCompanyId+ " AND NR="+STR_v_N_Isyeri_Nr
QryDName.SetSQLText(SqlDName)
FResult = ""
If QryDName.ExecuteDirect() Then
Res = QryDName.First()
Do While Res=1
QryDName.GetFieldValue(1, 4, FResult)
Res=QryDName.Next()
Loop
end if
QryDName.Clear()
SetTextVal("_T_Isyeri_Name",FResult)
End Sub
Sub getAmbarName()
'https://docs.logo.com.tr/public/wua/logo-object-designer/lod-baslangic
CreateQuery(QryWHName)
GetIntVal("_N_Ambar_Nr",v_N_Ambar_Nr)
SetIntVal("_C_Ambar",v_N_Ambar_Nr)
STR(v_N_Ambar_Nr,STR_v_N_Ambar_Nr)
STR(Application.CompanyId,STR_ApplicationCompanyId)
SqlWHName = "select TOP 1 NAME from L_CAPIWHOUSE WHERE FIRMNR="+STR_ApplicationCompanyId+ " AND NR="+STR_v_N_Ambar_Nr
QryWHName.SetSQLText(SqlWHName)
FResult = ""
If QryWHName.ExecuteDirect() Then
Res = QryWHName.First()
Do While Res=1
QryWHName.GetFieldValue(1, 4, FResult)
Res=QryWHName.Next()
Loop
end if
QryWHName.Clear()
SetTextVal("_T_Ambar_Name",FResult)
End Sub
' ///////////////////////////////////////////
' File Name : FRM_SatisFaturasi
' Name : CUSTENTR2
' Code Type : Event
' ///////////////////////////////////////////
Sub FormShow()
AppTables[14].GetIntFieldR(RecHandle,"LOGICALREF",_invoiceRef)
isNewInvoiceFiche = true
IF _invoiceRef > 0 THEN
isNewInvoiceFiche = false
END IF
End Sub
Sub ControlExit(ctrl as String, index as Integer)
IF ctrl = "clCodeEdit" OR ctrl = "TitleEdit" THEN
setIsyeriByClient()
setAmbarByClient()
END IF
End Sub
' ///////////////////////////////////////////
' File Name : FRM_SatisFaturasi
' Name : CUSTENTR2
' Code Type : Module
' ///////////////////////////////////////////
Sub setIsyeriByClient()
AppTables[14].GetIntFieldR(RecHandle,"CLIENTREF",_clientRef)
IF _clientRef=0 THEN
EXIT SUB
END IF
STR(_clientRef,STR_clientRef)
ClCardIsyeriRelationTable = Application.GetCustTableName(1, Application.CompanyId, Application.FiscPerdId) 'https://docs.logo.com.tr/public/wua/logo-objects/logo-objects-kuetuephanesi/unityapplication/getcusttablename
'https://docs.logo.com.tr/public/wua/logo-object-designer/lod-baslangic
CreateQuery(QryClCardIsyeriRelationTable)
SqlClCardIsyeriRelationTable = "select TOP 1 _ISYERINR from "+ClCardIsyeriRelationTable+" WHERE PARLOGREF='"+STR_clientRef+"' "
QryClCardIsyeriRelationTable.SetSQLText(SqlClCardIsyeriRelationTable)
FResult = 0
If QryClCardIsyeriRelationTable.ExecuteDirect() Then
Res = QryClCardIsyeriRelationTable.First()
Do While Res=1
QryClCardIsyeriRelationTable.GetFieldValue(1, 1, FResult)
Res=QryClCardIsyeriRelationTable.Next()
Loop
end if
QryClCardIsyeriRelationTable.Clear()
if FResult > 0 then
Confirmed("İşyeri Cari Bağlantısından Gelsinmi ? ", _ask)
IF _ask = 1 THEN
SetIntVal("branchComb",FResult)
END IF
END IF
End Sub
Sub setAmbarByClient()
AppTables[14].GetIntFieldR(RecHandle,"CLIENTREF",_clientRef)
IF _clientRef=0 THEN
EXIT SUB
END IF
STR(_clientRef,STR_clientRef)
ClCardAmbarRelationTable = Application.GetCustTableName(1, Application.CompanyId, Application.FiscPerdId) 'https://docs.logo.com.tr/public/wua/logo-objects/logo-objects-kuetuephanesi/unityapplication/getcusttablename
'https://docs.logo.com.tr/public/wua/logo-object-designer/lod-baslangic
CreateQuery(QryClCardAmbarRelationTable)
SqlClCardAmbarRelationTable = "select TOP 1 _AMBARNR from "+ClCardAmbarRelationTable+" WHERE PARLOGREF='"+STR_clientRef+"' "
QryClCardAmbarRelationTable.SetSQLText(SqlClCardAmbarRelationTable)
FResult = 0
If QryClCardAmbarRelationTable.ExecuteDirect() Then
Res = QryClCardAmbarRelationTable.First()
Do While Res=1
QryClCardAmbarRelationTable.GetFieldValue(1, 1, FResult)
Res=QryClCardAmbarRelationTable.Next()
Loop
end if
QryClCardAmbarRelationTable.Clear()
if FResult > 0 then
Confirmed("Ambar Cari Bağlantısından Gelsinmi ? ", _ask)
IF _ask = 1 THEN
SetIntVal("invenComb",FResult)
MGridName = "InvoiceGrid"
_satirSayisi = MGridLines(MGridName)
For I=0 to _satirSayisi - 1
GetGridRowHandle(MGridName,i,_recH)
SetCellInt(MGridName,I,40,FResult)
InvalidateGrid(MGridName)
RefreshControl(MGridName,1)
Next I
END IF
END IF
End Sub
Malzeme,Birim Ambar stok(Pivot)
DECLARE @columns NVARCHAR(MAX)
DECLARE @sql NVARCHAR(MAX)
SELECT @columns = STRING_AGG(QUOTENAME(NR), ',')
FROM L_CAPIWHOUSE
WHERE FIRMNR = 123
select @columns='[-1],'+@columns
SET @sql = '
SELECT LOGICALREF,
CODE,
DESCRIPTION,
UNIT, ' + @columns + '
FROM (
SELECT TF.LOGICALREF,
TF.CODE,
TF.NAME AS DESCRIPTION,
US.NAME AS UNIT,
WH.NR AS WAREHOUSE,
ISNULL(SUM(ST.ONHAND), 0) AS STOCK
FROM (
SELECT DISTINCT LOGICALREF, CODE, NAME, UNITSETREF
FROM LG_123_ITEMS
) AS TF
--LEFT JOIN LG_123_ITMUNITA IUA ON IUA.ITEMREF = TF.LOGICALREF
--LEFT JOIN LG_123_UNITSETL US ON US.LOGICALREF = IUA.UNITLINEREF
LEFT JOIN LG_123_UNITSETL US ON US.UNITSETREF = TF.UNITSETREF AND US.MAINUNIT = 1
CROSS JOIN (SELECT W.NR,W.FIRMNR FROM L_CAPIWHOUSE W UNION ALL SELECT -1 AS NR,123 AS FIRMNR) WH
LEFT JOIN LV_123_01_STINVTOT ST ON ST.STOCKREF = TF.LOGICALREF AND ST.INVENNO = WH.NR
WHERE WH.FIRMNR = 123
GROUP BY TF.LOGICALREF, TF.CODE, TF.NAME, US.NAME, WH.NR
) AS SourceTable
PIVOT (
MAX(STOCK)
FOR WAREHOUSE IN (' + @columns + ')
) AS PivotTable
ORDER BY LOGICALREF;'
-- 3. Dinamik SQL'i çalıştırın.
EXEC sp_executesql @sql
LOGO C#DAN SQL TARIH INSERT ETME
int TIME_ = DateTime.Now.Hour * 65536 * 256 + DateTime.Now.Minute * 65536 + DateTime.Now.Second * 256;
Logo Borç Takip Ekranı
CREATE FUNCTION [dbo].[PAYTRANS2ISLEMTURU](@TRCODE INT,@MODULENR INT )
RETURNS VARCHAR (100)
AS
begin
declare @ISLEM VARCHAR (100)
select @ISLEM = ( SELECT
CASE @MODULENR
WHEN 7 THEN
CASE @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 @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 @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 @TRCODE
WHEN 1 THEN
'Müşteri Çeki'
WHEN 3 THEN
'Kendi Çekimiz'
END
WHEN 3 THEN
CASE @TRCODE
WHEN 2 THEN
'Satınalama Siparişi'
WHEN 1 THEN
'Satış Siparişi'
END
WHEN 10 THEN
CASE @TRCODE
WHEN 1 THEN
'Nakit Tahsilat'
WHEN 2 THEN
'Nakit Ödeme'
END
WHEN 61 THEN
CASE @TRCODE
WHEN 3 THEN
'Borç Dekontu'
WHEN 4 THEN
'Alacak Dekontu'
END
WHEN 62 THEN
CASE @TRCODE
WHEN 3 THEN
'Borç Dekontu'
WHEN 4 THEN
'Alacak Dekontu'
END
END AS ISLEM
)
return @ISLEM
end
GO
SELECT Date= PTR.PROCDATE
,DueDate= PTR.DATE_
,Code= (SELECT LG_940_CLCARD.CODE FROM LG_940_CLCARD WHERE LG_940_CLCARD.LOGICALREF = PTR.CARDREF)
,Name= (SELECT LG_940_CLCARD.DEFINITION_ FROM LG_940_CLCARD WHERE LG_940_CLCARD.LOGICALREF = PTR.CARDREF)
,FicheType= dbo.PAYTRANS2ISLEMTURU(PTR.TRCODE,PTR.MODULENR)
,FicheNo= CASE PTR.MODULENR
WHEN 3 THEN
(SELECT LG_940_01_CLFLINE.TRANNO FROM LG_940_01_CLFLINE WHERE LG_940_01_CLFLINE.SOURCEFREF=PTR.FICHEREF AND LG_940_01_CLFLINE.MODULENR=3)
WHEN 6 THEN
(SELECT LG_940_01_CLFLINE.TRANNO FROM LG_940_01_CLFLINE WHERE LG_940_01_CLFLINE.SOURCEFREF=PTR.FICHEREF AND LG_940_01_CLFLINE.MODULENR=6)
WHEN 5 THEN
(SELECT LG_940_01_CLFLINE.TRANNO FROM LG_940_01_CLFLINE WHERE LG_940_01_CLFLINE.LOGICALREF=PTR.FICHEREF AND LG_940_01_CLFLINE.MODULENR=5)
WHEN 10 THEN
(SELECT LG_940_01_CLFLINE.TRANNO FROM LG_940_01_CLFLINE WHERE LG_940_01_CLFLINE.SOURCEFREF=PTR.FICHEREF AND LG_940_01_CLFLINE.MODULENR=10)
WHEN 4 THEN
(SELECT LG_940_01_CLFLINE.TRANNO FROM LG_940_01_CLFLINE WHERE LG_940_01_CLFLINE.SOURCEFREF=PTR.FICHEREF AND LG_940_01_CLFLINE.MODULENR=4)
WHEN 7 THEN
(SELECT LG_940_01_CLFLINE.TRANNO FROM LG_940_01_CLFLINE WHERE LG_940_01_CLFLINE.SOURCEFREF=PTR.FICHEREF AND LG_940_01_CLFLINE.MODULENR=7)
WHEN 61 THEN
(SELECT LG_940_01_CLFLINE.TRANNO FROM LG_940_01_CLFLINE WHERE LG_940_01_CLFLINE.LOGICALREF=PTR.FICHEREF AND LG_940_01_CLFLINE.MODULENR=61)
WHEN 62 THEN
(SELECT LG_940_01_CLFLINE.TRANNO FROM LG_940_01_CLFLINE WHERE LG_940_01_CLFLINE.LOGICALREF=PTR.FICHEREF AND LG_940_01_CLFLINE.MODULENR=62)
END
,DocNo= CASE PTR.MODULENR
WHEN 3 THEN
(SELECT LG_940_01_CLFLINE.DOCODE FROM LG_940_01_CLFLINE WHERE LG_940_01_CLFLINE.SOURCEFREF=PTR.FICHEREF AND LG_940_01_CLFLINE.MODULENR=3)
WHEN 6 THEN
(SELECT LG_940_01_CLFLINE.DOCODE FROM LG_940_01_CLFLINE WHERE LG_940_01_CLFLINE.SOURCEFREF=PTR.FICHEREF AND LG_940_01_CLFLINE.MODULENR=6)
WHEN 5 THEN
(SELECT LG_940_01_CLFLINE.DOCODE FROM LG_940_01_CLFLINE WHERE LG_940_01_CLFLINE.LOGICALREF=PTR.FICHEREF AND LG_940_01_CLFLINE.MODULENR=5)
WHEN 10 THEN
(SELECT LG_940_01_CLFLINE.DOCODE FROM LG_940_01_CLFLINE WHERE LG_940_01_CLFLINE.SOURCEFREF=PTR.FICHEREF AND LG_940_01_CLFLINE.MODULENR=10)
WHEN 4 THEN
(SELECT LG_940_01_CLFLINE.DOCODE FROM LG_940_01_CLFLINE WHERE LG_940_01_CLFLINE.SOURCEFREF=PTR.FICHEREF AND LG_940_01_CLFLINE.MODULENR=4)
WHEN 7 THEN
(SELECT LG_940_01_CLFLINE.DOCODE FROM LG_940_01_CLFLINE WHERE LG_940_01_CLFLINE.SOURCEFREF=PTR.FICHEREF AND LG_940_01_CLFLINE.MODULENR=7)
WHEN 61 THEN
(SELECT LG_940_01_CLFLINE.DOCODE FROM LG_940_01_CLFLINE WHERE LG_940_01_CLFLINE.LOGICALREF=PTR.FICHEREF AND LG_940_01_CLFLINE.MODULENR=61)
WHEN 62 THEN
(SELECT LG_940_01_CLFLINE.DOCODE FROM LG_940_01_CLFLINE WHERE LG_940_01_CLFLINE.LOGICALREF=PTR.FICHEREF AND LG_940_01_CLFLINE.MODULENR=62)
END
,TLPrice= CASE
WHEN PTR.TRCURR IN (0,160) THEN
CASE WHEN PTR.SIGN=0 THEN PTR.TOTAL ELSE PTR.TOTAL*-1 END
ELSE
CASE WHEN PTR.SIGN=0 THEN PTR.TOTAL*PTR.TRRATE ELSE (PTR.TOTAL*PTR.TRRATE)*-1 END
END
,CurPrice= CASE WHEN PTR.SIGN=0 THEN PTR.TOTAL ELSE PTR.TOTAL*-1 END
,Currency= CASE PTR.TRCURR WHEN 0 THEN 'TL' WHEN 160 THEN 'TL' WHEN 1 THEN 'USD' WHEN 17 THEN 'GBP' WHEN 20 THEN 'EUR' ELSE '' END
,CurRate= PTR.TRRATE
,Explanataion= CASE PTR.MODULENR
WHEN 3 THEN
(SELECT LG_940_01_CLFLINE.LINEEXP FROM LG_940_01_CLFLINE WHERE LG_940_01_CLFLINE.SOURCEFREF=PTR.FICHEREF AND LG_940_01_CLFLINE.MODULENR=3)
WHEN 6 THEN
(SELECT LG_940_01_CLFLINE.LINEEXP FROM LG_940_01_CLFLINE WHERE LG_940_01_CLFLINE.SOURCEFREF=PTR.FICHEREF AND LG_940_01_CLFLINE.MODULENR=6)
WHEN 5 THEN
(SELECT LG_940_01_CLFLINE.LINEEXP FROM LG_940_01_CLFLINE WHERE LG_940_01_CLFLINE.LOGICALREF=PTR.FICHEREF AND LG_940_01_CLFLINE.MODULENR=5)
WHEN 10 THEN
(SELECT LG_940_01_CLFLINE.LINEEXP FROM LG_940_01_CLFLINE WHERE LG_940_01_CLFLINE.SOURCEFREF=PTR.FICHEREF AND LG_940_01_CLFLINE.MODULENR=10)
WHEN 4 THEN
(SELECT LG_940_01_CLFLINE.LINEEXP FROM LG_940_01_CLFLINE WHERE LG_940_01_CLFLINE.SOURCEFREF=PTR.FICHEREF AND LG_940_01_CLFLINE.MODULENR=4)
WHEN 7 THEN
(SELECT LG_940_01_CLFLINE.LINEEXP FROM LG_940_01_CLFLINE WHERE LG_940_01_CLFLINE.SOURCEFREF=PTR.FICHEREF AND LG_940_01_CLFLINE.MODULENR=7)
WHEN 61 THEN
(SELECT LG_940_01_CLFLINE.LINEEXP FROM LG_940_01_CLFLINE WHERE LG_940_01_CLFLINE.LOGICALREF=PTR.FICHEREF AND LG_940_01_CLFLINE.MODULENR=61)
WHEN 62 THEN
(SELECT LG_940_01_CLFLINE.LINEEXP FROM LG_940_01_CLFLINE WHERE LG_940_01_CLFLINE.LOGICALREF=PTR.FICHEREF AND LG_940_01_CLFLINE.MODULENR=62)
END
,Division= CASE PTR.MODULENR
WHEN 3 THEN
(SELECT L_CAPIDIV.NAME FROM L_CAPIDIV WHERE L_CAPIDIV.FIRMNR=940 AND L_CAPIDIV.NR=(SELECT LG_940_01_CLFLINE.BRANCH FROM LG_940_01_CLFLINE WHERE LG_940_01_CLFLINE.SOURCEFREF=PTR.FICHEREF AND LG_940_01_CLFLINE.MODULENR=3))
WHEN 6 THEN
(SELECT L_CAPIDIV.NAME FROM L_CAPIDIV WHERE L_CAPIDIV.FIRMNR=940 AND L_CAPIDIV.NR=(SELECT LG_940_01_CLFLINE.BRANCH FROM LG_940_01_CLFLINE WHERE LG_940_01_CLFLINE.SOURCEFREF=PTR.FICHEREF AND LG_940_01_CLFLINE.MODULENR=6))
WHEN 5 THEN
(SELECT L_CAPIDIV.NAME FROM L_CAPIDIV WHERE L_CAPIDIV.FIRMNR=940 AND L_CAPIDIV.NR=(SELECT LG_940_01_CLFLINE.BRANCH FROM LG_940_01_CLFLINE WHERE LG_940_01_CLFLINE.LOGICALREF=PTR.FICHEREF AND LG_940_01_CLFLINE.MODULENR=5))
WHEN 10 THEN
(SELECT L_CAPIDIV.NAME FROM L_CAPIDIV WHERE L_CAPIDIV.FIRMNR=940 AND L_CAPIDIV.NR=(SELECT LG_940_01_CLFLINE.BRANCH FROM LG_940_01_CLFLINE WHERE LG_940_01_CLFLINE.SOURCEFREF=PTR.FICHEREF AND LG_940_01_CLFLINE.MODULENR=10))
WHEN 4 THEN
(SELECT L_CAPIDIV.NAME FROM L_CAPIDIV WHERE L_CAPIDIV.FIRMNR=940 AND L_CAPIDIV.NR=(SELECT LG_940_01_CLFLINE.BRANCH FROM LG_940_01_CLFLINE WHERE LG_940_01_CLFLINE.SOURCEFREF=PTR.FICHEREF AND LG_940_01_CLFLINE.MODULENR=4))
WHEN 7 THEN
(SELECT L_CAPIDIV.NAME FROM L_CAPIDIV WHERE L_CAPIDIV.FIRMNR=940 AND L_CAPIDIV.NR=(SELECT LG_940_01_CLFLINE.BRANCH FROM LG_940_01_CLFLINE WHERE LG_940_01_CLFLINE.SOURCEFREF=PTR.FICHEREF AND LG_940_01_CLFLINE.MODULENR=7))
WHEN 61 THEN
(SELECT L_CAPIDIV.NAME FROM L_CAPIDIV WHERE L_CAPIDIV.FIRMNR=940 AND L_CAPIDIV.NR=(SELECT LG_940_01_CLFLINE.BRANCH FROM LG_940_01_CLFLINE WHERE LG_940_01_CLFLINE.LOGICALREF=PTR.FICHEREF AND LG_940_01_CLFLINE.MODULENR=61))
WHEN 62 THEN
(SELECT L_CAPIDIV.NAME FROM L_CAPIDIV WHERE L_CAPIDIV.FIRMNR=940 AND L_CAPIDIV.NR=(SELECT LG_940_01_CLFLINE.BRANCH FROM LG_940_01_CLFLINE WHERE LG_940_01_CLFLINE.LOGICALREF=PTR.FICHEREF AND LG_940_01_CLFLINE.MODULENR=62))
END
,DivisionNo= CONVERT(smallint, CASE PTR.MODULENR
WHEN 3 THEN
(SELECT LG_940_01_CLFLINE.BRANCH FROM LG_940_01_CLFLINE WHERE LG_940_01_CLFLINE.SOURCEFREF=PTR.FICHEREF AND LG_940_01_CLFLINE.MODULENR=3)
WHEN 6 THEN
(SELECT LG_940_01_CLFLINE.BRANCH FROM LG_940_01_CLFLINE WHERE LG_940_01_CLFLINE.SOURCEFREF=PTR.FICHEREF AND LG_940_01_CLFLINE.MODULENR=6)
WHEN 5 THEN
(SELECT LG_940_01_CLFLINE.BRANCH FROM LG_940_01_CLFLINE WHERE LG_940_01_CLFLINE.LOGICALREF=PTR.FICHEREF AND LG_940_01_CLFLINE.MODULENR=5)
WHEN 10 THEN
(SELECT LG_940_01_CLFLINE.BRANCH FROM LG_940_01_CLFLINE WHERE LG_940_01_CLFLINE.SOURCEFREF=PTR.FICHEREF AND LG_940_01_CLFLINE.MODULENR=10)
WHEN 4 THEN
(SELECT LG_940_01_CLFLINE.BRANCH FROM LG_940_01_CLFLINE WHERE LG_940_01_CLFLINE.SOURCEFREF=PTR.FICHEREF AND LG_940_01_CLFLINE.MODULENR=4)
WHEN 7 THEN
(SELECT LG_940_01_CLFLINE.BRANCH FROM LG_940_01_CLFLINE WHERE LG_940_01_CLFLINE.SOURCEFREF=PTR.FICHEREF AND LG_940_01_CLFLINE.MODULENR=7)
WHEN 61 THEN
(SELECT LG_940_01_CLFLINE.BRANCH FROM LG_940_01_CLFLINE WHERE LG_940_01_CLFLINE.LOGICALREF=PTR.FICHEREF AND LG_940_01_CLFLINE.MODULENR=61)
WHEN 62 THEN
(SELECT LG_940_01_CLFLINE.BRANCH FROM LG_940_01_CLFLINE WHERE LG_940_01_CLFLINE.LOGICALREF=PTR.FICHEREF AND LG_940_01_CLFLINE.MODULENR=62)
END)
,CloseStatus= CASE WHEN PAID=0 THEN 'NotPaid' else 'Paid' end
--,PTR.TRCODE,PTR.MODULENR
FROM
LG_940_01_PAYTRANS AS PTR
WHERE
PTR.CARDREF IN (SELECT CL.LOGICALREF FROM LG_940_CLCARD CL WHERE CL.CODE LIKE 'P000229%')
Blog of Sem 