select
count(distinct program_name)
from master.dbo.sysprocesses
where program_name like 'LOGO_ERPOBJECT%'
Logo Erp / Sql Malzeme sınıfı bağlantıları çalışması
kırılım yapısı(3 alt kırılım-FERRARI) : Tüm Malzemeler -> Kırılım1-> Kırılım2-> Kırılım3 -> Kırılım3 Malzemeleri
Kampanya yapısı(2 alt kırılım-KMP) : Tüm Malzemeler -> Kırılım1-> Kırılım2 -> Kırılım2 Malzemeleri
create view [RM_FERRARI_MALZEME_SINIFLARI]
as
select top 100 percent [Type], [Level1Ref] = case when [Level1Ref] = 1 then Level2Ref else Level1Ref end, [Level1] = case when [Level1Ref] = 1 then Level2 else Level1 end, [Level2Ref] = case when [Level1Ref] = 1 then Level3Ref else Level2Ref end, [Level2] = case when [Level1Ref] = 1 then Level3 else Level2 end, [Level3Ref] = case when [Level1Ref] = 1 then 0 else [Level3Ref] end, [Level3] = case when [Level1Ref] = 1 then '' else [Level3] end, [ItemRef], [Code], [Name], [Active]
from (
select [Type] = case when ITMSC.CARDTYPE = 20 then 'Genel Malzeme Sınıfı' else 'Malzeme' end, [Level1Ref] = (
select top 1 LGMAIN2.PARENTREF
from LG_101_ITMCLSAS LGMAIN2
where LGMAIN2.CHILDREF = (
select top 1 LGMAIN2.PARENTREF
from LG_101_ITMCLSAS LGMAIN2
where LGMAIN2.CHILDREF = LGMAIN.PARENTREF
)
), [Level1] = (
select I2.CODE + '-' + I2.name
from LG_101_ITEMS I2
where I2.LOGICALREF = (
select top 1 LGMAIN2.PARENTREF
from LG_101_ITMCLSAS LGMAIN2
where LGMAIN2.CHILDREF = (
select top 1 LGMAIN2.PARENTREF
from LG_101_ITMCLSAS LGMAIN2
where LGMAIN2.CHILDREF = LGMAIN.PARENTREF
)
)
), [Level2Ref] = (
select top 1 LGMAIN2.PARENTREF
from LG_101_ITMCLSAS LGMAIN2
where LGMAIN2.CHILDREF = LGMAIN.PARENTREF
), [Level2] = (
select I2.CODE + '-' + I2.name
from LG_101_ITEMS I2
where I2.LOGICALREF = (
select top 1 LGMAIN2.PARENTREF
from LG_101_ITMCLSAS LGMAIN2
where LGMAIN2.CHILDREF = LGMAIN.PARENTREF
)
), [Level3Ref] = LGMAIN.PARENTREF, [Level3] = (
select I2.CODE + '-' + I2.name
from LG_101_ITEMS I2
where I2.LOGICALREF = LGMAIN.PARENTREF
), [ItemRef] = ITMSC.LOGICALREF, [Code] = ITMSC.CODE, [Name] = ITMSC.name, [Active] = ITMSC.ACTIVE
from LG_101_ITMCLSAS LGMAIN with (nolock)
left outer join LG_101_ITEMS ITMSC with (nolock) on (LGMAIN.CHILDREF = ITMSC.LOGICALREF)
) as T
where [Level1] = 'MS01-FERRARI' or Level3 = 'MS01.16-KMP'
order by Level1, Level2, Level3
LOGO RAPOR ÜRETİCİ SELECTSİZ TARİH FİLTRESİ
NOT:
SELECTE EKLENDİ: CONVERT(SMALLDATETIME,’01.01.2000′,103) TARIH
WHERE KISMINA EKLENDİ: BETWEEN {FLTDATEBEG(1)} AND {FLTDATEEND(1)}
--RAPOR SORGUSU:
SELECT TOP 200
CONVERT(SMALLDATETIME,'01.01.2000',103) TARIH,
CARI_KODU,CARI_ADI,MALZEME_GRUP,MALZEME_ALTGRUP,MALZEME_KODU,MALZEME_AÇIKLAMASI, SUM(MIKTAR) AS MIKTAR,
SUM(TUTAR) AS TUTAR,
'' KARLILIK,
'' SATIS_FIYATI
FROM
(
SELECT DATE_ AS TARIH,
(SELECT SPECODE2 FROM LG_001_ITEMS WHERE LOGICALREF=STOCKREF) AS CARI_KODU,
ISNULL((SELECT DEFINITION_ FROM LG_001_CLCARD WHERE CODE=(SELECT SPECODE2 FROM LG_001_ITEMS WHERE LOGICALREF=STOCKREF)),'') AS CARI_ADI,
ISNULL((SELECT SPECODE+' - '+DEFINITION_ FROM LG_001_SPECODES WHERE CODETYPE=4 AND SPECODE=(SELECT STGRPCODE FROM LG_001_ITEMS WHERE LOGICALREF=STOCKREF)),'') AS MALZEME_GRUP,
ISNULL((SELECT SPECODE+' - '+DEFINITION_ FROM LG_001_SPECODES WHERE CODETYPE=1 AND SPECODETYPE=1 AND SPETYP1=1 AND SPECODE=(SELECT SPECODE FROM LG_001_ITEMS WHERE LOGICALREF=STOCKREF)),'') AS MALZEME_ALTGRUP,
(SELECT CODE FROM LG_001_ITEMS WHERE LOGICALREF=STOCKREF) AS MALZEME_KODU,
(SELECT NAME FROM LG_001_ITEMS WHERE LOGICALREF=STOCKREF) AS MALZEME_AÇIKLAMASI,
AMOUNT AS MIKTAR,
VATMATRAH AS TUTAR
FROM LG_001_01_STLINE WHERE CANCELLED=0 AND LINETYPE=0 AND TRCODE=7 AND DATE_ BETWEEN {FLTDATEBEG(1)} AND {FLTDATEEND(1)}
) AS RPR
GROUP BY CARI_KODU,CARI_ADI,MALZEME_GRUP,MALZEME_ALTGRUP,MALZEME_KODU,MALZEME_AÇIKLAMASI
ORDER BY (SUM(MIKTAR)) DESC
C# DEVEXPRESS PIVOTGRIDCONTROL
pivotGridControl1.DataSource = DLL.SQL.SELECT(string.Format("SELECT * FROM MIZANMALZEME WHERE (Cast(Tarih as smalldatetime) between '" + TE_bASLANGICTARIHI.DateTime.ToString("yyyy-MM-dd") + "' and '" + TE_BITISTARIHI.DateTime.ToString("yyyy-MM-dd") + "') AND Isyeri_No IN (" + ISYERI_NRLERI + ")"));
pivotGridControl1.RetrieveFields();
pivotGridControl1.RestoreLayoutFromRegistry(@"Software\RAPOR\FRM_FINANSAL_RAPOR_212+" + pivotGridControl1.Name);
PIVOTBESTFIT();
private void PIVOTBESTFIT()
{
try
{
pivotGridControl1.BestFit();
int i = 0;
foreach (PivotGridField item in pivotGridControl1.Fields)
{
item.BestFit();
}
}
catch { }
}
Windows Mobile Başlat menusune kısayol ekleme
Kopyalanmak isdenilen program fileexplorerda bulunup File Explorer -> Windows -> Startup -> Paste Shortcut ve File Explorer -> Windows -> Startup -> Programs -> Paste Shortcut yapılır
Logo Malzeme Grup Açıklamaları Sql
select
CODE,
NAME,
STGRPCODE,
(
select
TOP 1 S.DEFINITION_
from
LG_301_SPECODES S
WHERE
(
(
(S.CODETYPE = 4)
AND (S.SPECODETYPE = 0)
)
OR (
(S.CODETYPE = 4)
AND (S.SPECODETYPE > 0)
)
OR (
(S.CODETYPE > 4)
)
)
AND (
(
(S.CODETYPE = 4)
AND (S.SPECODETYPE <= 0)
)
OR (
(S.CODETYPE < 4)
)
)
AND S.SPECODE = I.STGRPCODE
) STGRPCODE_A,
SPECODE,
(
select
TOP 1 S.DEFINITION_
from
LG_301_SPECODES S
WHERE
S.SPECODE = I.SPECODE
AND S.SPETYP1 = 1
AND S.CODETYPE = 1
AND S.SPECODETYPE = 1
) SPECODE_A,
SPECODE2,
(
select
TOP 1 S.DEFINITION_
from
LG_301_SPECODES S
WHERE
S.SPECODE = I.SPECODE2
AND S.SPETYP2 = 1
AND S.CODETYPE = 1
AND S.SPECODETYPE = 1
) SPECODE2_A,
SPECODE3,
(
select
TOP 1 S.DEFINITION_
from
LG_301_SPECODES S
WHERE
S.SPECODE = I.SPECODE3
AND S.SPETYP3 = 1
AND S.CODETYPE = 1
AND S.SPECODETYPE = 1
) SPECODE3_A,
SPECODE4,
(
select
TOP 1 S.DEFINITION_
from
LG_301_SPECODES S
WHERE
S.SPECODE = I.SPECODE4
AND S.SPETYP4 = 1
AND S.CODETYPE = 1
AND S.SPECODETYPE = 1
) SPECODE4_A
from
LG_301_ITEMS I
Dosya/Klasör sağ tık Vs Code ile aç
aşağıdaki içeriği xxx.reg isminde bir dosya yaratıp içine at kaydet ve çalıştır.
Windows Registry Editor Version 5.00
; Open files
[HKEY_CLASSES_ROOT*\shell\Open with VS Code]
@=”Edit with VS Code”
“Icon”=”C:\Program Files\Microsoft VS Code\Code.exe,0”
[HKEY_CLASSES_ROOT*\shell\Open with VS Code\command]
@=”\”C:\Program Files\Microsoft VS Code\Code.exe\” \”%1\””
; This will make it appear when you right click ON a folder
; The “Icon” line can be removed if you don’t want the icon to appear
[HKEY_CLASSES_ROOT\Directory\shell\vscode]
@=”Open Folder as VS Code Project”
“Icon”=”\”C:\Program Files\Microsoft VS Code\Code.exe\”,0″
[HKEY_CLASSES_ROOT\Directory\shell\vscode\command]
@=”\”C:\Program Files\Microsoft VS Code\Code.exe\” \”%1\””
; This will make it appear when you right click INSIDE a folder
; The “Icon” line can be removed if you don’t want the icon to appear
[HKEY_CLASSES_ROOT\Directory\Background\shell\vscode]
@=”Open Folder as VS Code Project”
“Icon”=”\”C:\Program Files\Microsoft VS Code\Code.exe\”,0″
[HKEY_CLASSES_ROOT\Directory\Background\shell\vscode\command]
@=”\”C:\Program Files\Microsoft VS Code\Code.exe\” \”%V\””
LOGO SQL Promosyon Satırı Olan Ürünler
SELECT
(
select
I.CODE
FROM
LG_120_ITEMS I
WHERE
I.LOGICALREF = S.STOCKREF
) [Malzeme Kodu],
(
select
I.NAME
FROM
LG_120_ITEMS I
WHERE
I.LOGICALREF = S.STOCKREF
) [Malzeme Adı]
FROM
LG_120_01_STLINE S
WHERE
LINETYPE = 1
AND STOCKREF <> 0
AND CANCELLED = 0
AND TRCODE = 1
GROUP BY
S.STOCKREF
LOGO SQL Devir Maliyet Raporu
SELECT
(SELECT I.CODE FROM LG_121_ITEMS I WHERE I.LOGICALREF=SS3.SREF) URUNKODU ,
(SELECT I.NAME FROM LG_121_ITEMS I WHERE I.LOGICALREF=SS3.SREF) URUNKODU ,
SS3.MLYT, ST4.AMOUNT
FROM LG_121_01_STLINE AS ST4 LEFT OUTER JOIN
(SELECT ST3.VATMATRAH / ST3.AMOUNT AS MLYT, SS2.SREF FROM
(SELECT MAX(DISTINCT ST2.LOGICALREF) AS LREF, ST2.STOCKREF AS SREF FROM
(SELECT ST1.STOCKREF, MAX(ST1.DATE_) AS DATE_ FROM LG_117_01_STLINE AS ST1 WHERE
(ST1.IOCODE =1) AND (ST1.LINETYPE = 0) AND (ST1.CANCELLED = 0) AND (ST1.PRICE <> 0) GROUP BY ST1.STOCKREF)
AS SS1 LEFT OUTER JOIN LG_117_01_STLINE AS ST2 ON SS1.DATE_ = ST2.DATE_ AND SS1.STOCKREF = ST2.STOCKREF
WHERE (ST2.LINETYPE = 0) AND (ST2.IOCODE =1) AND (ST2.CANCELLED = 0) AND (ST2.PRICE <> 0) GROUP BY ST2.STOCKREF) AS SS2
LEFT OUTER JOIN LG_117_01_STLINE AS ST3 ON SS2.SREF = ST3.STOCKREF AND SS2.LREF = ST3.LOGICALREF) AS SS3
ON ST4.STOCKREF = SS3.SREF
WHERE (ST4.TRCODE = 14) AND (ST4.CANCELLED = 0) AND (ST4.LINETYPE = 0) AND (SS3.MLYT <> 0) AND (ST4.LINENET = 0)
Logo Satış Siparişi Belirli Saate Göre Sql Rapor
Talep: 1/1/2021‘den itibaren cumartesileri saat 14:00dan sonraki satış siparişleri net tutarını gösteren rapor.
SELECT DISTINCT * FROM (
SELECT
TOP (100) PERCENT CAST(
LG_101_01_ORFICHE.BRANCH AS VARCHAR(3)
)+ ' - ' +(
SELECT
NAME
FROM
L_CAPIDIV
WHERE
NR = LG_101_01_ORFICHE.BRANCH
AND FIRMNR = 101
) AS İşyeri,
/*ORFLINE.DUEDATE AS [Sevk Tarihi], */
LG_101_01_ORFICHE.DATE_ AS Sipariş_Tarihi,
/*LG_101_01_ORFICHE.TIME_, */
dbo.fn_LogoTimetoSystemTime(LG_101_01_ORFICHE.TIME_) Sipariş_Saat,
LG_101_01_ORFICHE.FICHENO Sipariş_No,
CLCARD.CODE AS Müşteri_No,
dbo.Get_Satis_Eleman_adi(LG_101_01_ORFICHE.SALESMANREF) Satis_Elemani,
CLCARD.DEFINITION_ AS Müşteri_Adı,
CASE LG_101_01_ORFICHE.TRCURR WHEN 0 THEN 'TL' WHEN 1 THEN 'USD' WHEN 17 THEN 'GBP' WHEN 20 THEN 'EUR' ELSE '' END Islem_Dovizi,
LG_101_01_ORFICHE.NETTOTAL Net_Tutar
/*ISNULL(
(
SELECT
LEFT(S.CODE, 7)+ ' - ' +(
SELECT
NAME
FROM
LG_101_ITEMS
WHERE
CODE = LEFT(S.CODE, 7)
)
FROM
LG_101_ITEMS I (NOLOCK)
LEFT OUTER JOIN LG_101_ITMCLSAS C (NOLOCK) ON I.LOGICALREF = C.CHILDREF
AND C.UPLEVEL = 0
LEFT OUTER JOIN LG_101_ITEMS S (NOLOCK) ON C.PARENTREF = S.LOGICALREF
WHERE
S.CODE LIKE 'MS01.%'
AND I.CODE NOT LIKE 'MS01.%'
AND I.LOGICALREF = ITEMS.LOGICALREF
),
''
) AS Üst_Sınıf,
ISNULL(
(
SELECT
S.CODE + ' - ' + S.NAME
FROM
LG_101_ITEMS I (NOLOCK)
LEFT OUTER JOIN LG_101_ITMCLSAS C (NOLOCK) ON I.LOGICALREF = C.CHILDREF
AND C.UPLEVEL = 0
LEFT OUTER JOIN LG_101_ITEMS S (NOLOCK) ON C.PARENTREF = S.LOGICALREF
WHERE
S.CODE LIKE 'MS01.%'
AND I.CODE NOT LIKE 'MS01.%'
AND I.LOGICALREF = ITEMS.LOGICALREF
),
''
) AS Mlz_Sınıfı,
ITEMS.PRODUCERCODE Üretici_Kodu,
ITEMS.CODE AS Mlz_Kodu,
ITEMS.NAME AS Mlz_Açıklaması,
'Adet' as Birim,
ORFLINE.AMOUNT AS Sipariş_Miktarı,
ORFLINE.SHIPPEDAMOUNT AS Sevk_Edilen_Miktar,
ROUND(VATMATRAH, 2) AS Net_Tutar,
ROUND(VATAMNT, 2) AS KDV_Tutarı,
ROUND(VATMATRAH + VATAMNT, 2) AS Brüt_Tutar,
CASE (TOTAL - VATMATRAH - VATAMNT) WHEN 0 THEN 0 ELSE ROUND(
100 *(TOTAL - VATMATRAH - VATAMNT)/ TOTAL,
2
) END AS İndirim,
ROUND(TOTAL - VATMATRAH - VATAMNT, 2) AS İndirim_Tutarı,
dbo.Get_Siparis_Masraf_ORFICHE_101(ORDFICHEREF) Masraf,
dbo.Get_Satis_Eleman_adi(ORFLINE.SALESMANREF) Satis_Elemani,
ORFLINE.CLOSED AS [0(Açık) ] */
FROM
LG_101_SPECODES AS SPECODES
RIGHT OUTER JOIN LG_101_01_ORFLINE AS ORFLINE ON SPECODES.SPECODE = ORFLINE.SPECODE
LEFT OUTER JOIN LG_101_ITEMS AS ITEMS ON ORFLINE.STOCKREF = ITEMS.LOGICALREF FULL
OUTER JOIN LG_101_CLCARD AS CLCARD
RIGHT OUTER JOIN LG_101_SHIPINFO
RIGHT OUTER JOIN LG_101_01_ORFICHE ON LG_101_SHIPINFO.LOGICALREF = LG_101_01_ORFICHE.SHIPINFOREF ON CLCARD.LOGICALREF = LG_101_01_ORFICHE.CLIENTREF ON ORFLINE.ORDFICHEREF = LG_101_01_ORFICHE.LOGICALREF
WHERE
(LG_101_01_ORFICHE.TRCODE = 1)
AND LG_101_01_ORFICHE.DATE_ IN (
select
dateadd(dd, number, '20210101')
from
master..spt_values
where
type = 'p'
and year(
dateadd(dd, number, '20210101')
)= year('20210101')
and DATEPART(
dw,
dateadd(dd, number, '20210101')
) = 7
UNION ALL
select
dateadd(dd, number, '20220101')
from
master..spt_values
where
type = 'p'
and year(
dateadd(dd, number, '20220101')
)= year('20220101')
and DATEPART(
dw,
dateadd(dd, number, '20220101')
) = 7
)
AND LG_101_01_ORFICHE.TIME_ > 230000000 /*SAAT 14:00DAN SONRA*/
) AS T5 ORDER BY Sipariş_No DESC
Blog of Sem 