Blog Of Sem

Object Realtime Aktiflik

Kullanımda olan Logo Objects'lere aşağıdaki sorgu ile erişebilirsiniz.

" select count(distinct program_name) from master.dbo.sysprocesses where program_name like 'LOGO_ERPOBJECT%' "

DEVEXPRESS RUNTIME HIDDEN EXPORT TO XLS

private void simpleButton1_Click_1(object sender, EventArgs e)
{
//XLWorkbook wb = new XLWorkbook();
//DataTable dt = BMS_DLL.SQL.SELECT("select * from BMS_MS_RECORDS");
//wb.Worksheets.Add(dt, "WorksheetName");
//wb.SaveAs(@"HelloWorld.xls");
DataTableToExcel(BMS_DLL.SQL.SELECT("select * from BMS_MS_RECORDS"), "HelloWorld.xls");

}
public void DataTableToExcel(DataTable dt, string PATH)
{
GridControl GC = new GridControl();
GridView GV = new GridView();
GC.ViewCollection.Add(GV);
GC.MainView = GV;
GC.BindingContext = new BindingContext();
GC.DataSource = dt;
GV.PopulateColumns();
GC.ForceInitialize();
GV.ExportToXls(PATH);
}

SQL SERVER KURULDUKTAN SONRA COLLATION DEĞİŞİMİ

Sql Serverin setupunun bulunduğu klasor
>Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER2014 /SQLSYSADMINACCOUNTS=Administrator /SAPWD= M@nVendr4 /SQLCOLLATION=SQL_Latin1_General_CP1_CI_AS

BAT UZERINDEN DOSYA + DATABASE YEDEKLEME (EXPRESS ICIN) VE RAR OLARAK TARIH ISMINDE SIKISTIRMA

YEDEK.BAT
---------
@ECHO OFF
set CUR_YYYY=%date:~10,4%
set CUR_MM=%date:~4,2%
set CUR_DD=%date:~7,2%
set CUR_HH=%time:~0,2%
if %CUR_HH% lss 10 (set CUR_HH=0%time:~1,1%)

set CUR_NN=%time:~3,2%
set CUR_SS=%time:~6,2%
set CUR_MS=%time:~9,2%

set SUBFILENAME=%CUR_YYYY%%CUR_MM%%CUR_DD%-%CUR_HH%%CUR_NN%%CUR_SS%

xcopy C:\Users\ek3g\Desktop\*.* D:\veritabanyedekler\yedekleme /s/d/y/i/h/c/r
sqlcmd -S TURKCELL -i "D:\veritabanyedekler\yedekscript.sql"
"c:\program files\winrar\rar.exe" a -r D:\veritabanyedekler\yedegi_al\%SUBFILENAME% D:\veritabanyedekler\yedekleme\*.*
rd D:\veritabanyedekler\yedekleme\ /s /q

yedekscript.sql
---------------
BACKUP DATABASE [TIGER] TO DISK = N'D:\veritabanyedekler\yedekleme\TIGER.bak' WITH NOFORMAT, NOINIT, NAME = N'TIGER-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

BAT DOSYASINDAN VERITABANI VE ONEMLI KLASOR YEDEKLEME

@ECHO OFF
set CUR_YYYY=%date:~10,4%
set CUR_MM=%date:~4,2%
set CUR_DD=%date:~7,2%
set CUR_HH=%time:~0,2%
if %CUR_HH% lss 10 (set CUR_HH=0%time:~1,1%)

set CUR_NN=%time:~3,2%
set CUR_SS=%time:~6,2%
set CUR_MS=%time:~9,2%

set SUBFILENAME=%CUR_YYYY%%CUR_MM%%CUR_DD%-%CUR_HH%%CUR_NN%%CUR_SS%

xcopy C:\Users\ek3g\Desktop\*.* D:\veritabanyedekler\yedekleme /s/d/y/i/h/c/r
sqlcmd -S MYPC -i "D:\veritabanyedekler\yedekscript.sql"
"c:\program files\winrar\rar.exe" a -r D:\veritabanyedekler\yedegi_al\%SUBFILENAME% D:\veritabanyedekler\yedekleme\*.*
rd D:\veritabanyedekler\yedekleme\ /s /q

wordpress plugin woof woocommerce filter price

\woocommerce-products-filter\views\woof.php

Find bellow

<button style="float: left;" class="button woof_submit_search_form"><?php echo $woof_filter_btn_txt ?></button>

change to

<button style="float: left;" class="button woof_submit_search_form"><?php echo "Filtrele" ?></button>

LOGO KAPANMAMIŞ FATURALAR

ALTER VIEW BM_MOBILSALES_KAPANMAMISFATURALAR AS

SELECT
INVFC.DATE_ TARIH,
CLCARD.CODE CARI_KOD,
CLCARD.DEFINITION_ CARI_AD,
INVFC.FICHENO,
INVFC.DOCODE,
SUM(CASE LGMAIN.TRRATE WHEN 0 THEN LGMAIN.TOTAL ELSE LGMAIN.TOTAL*LGMAIN.TRRATE END) AS TUTAR
-- , SUM(CASE LGMAIN.TRRATE WHEN 0 THEN LGMAIN.TOTAL ELSE LGMAIN.TOTAL*LGMAIN.TRRATE END * DATEDIFF(day,GETDATE(),LGMAIN.DATE_)) AS TOTALGDEB
FROM
LG_119_01_PAYTRANS LGMAIN WITH(NOLOCK) LEFT OUTER JOIN LG_119_01_INVOICE INVFC WITH(NOLOCK) ON (LGMAIN.FICHEREF = INVFC.LOGICALREF) LEFT OUTER JOIN LG_119_01_CLFLINE CTRNS WITH(NOLOCK) ON (LGMAIN.FICHEREF = CTRNS.LOGICALREF) LEFT OUTER JOIN LG_119_01_CSROLL RLFIC WITH(NOLOCK) ON (LGMAIN.FICHEREF = RLFIC.LOGICALREF) LEFT OUTER JOIN LG_119_01_BNFLINE BTRNS WITH(NOLOCK) ON (LGMAIN.FICHEREF = BTRNS.LOGICALREF) LEFT OUTER JOIN LG_119_01_KSLINES CASHTR WITH(NOLOCK) ON (LGMAIN.FICHEREF = CASHTR.LOGICALREF) LEFT OUTER JOIN LG_119_01_ORFICHE ORFIC WITH(NOLOCK) ON (LGMAIN.FICHEREF = ORFIC.LOGICALREF)
LEFT JOIN LG_119_CLCARD CLCARD ON CLCARD.LOGICALREF = LGMAIN.CARDREF
WHERE
((LGMAIN.DATE_ >= CONVERT(dateTime, '1-1-2000', 101)) AND (LGMAIN.DATE_ <= CONVERT(dateTime, '12-31-2051', 101))) AND ((((LGMAIN.MODULENR=3 AND ((ORFIC.BRANCH BETWEEN 0 AND 13)))AND((ORFIC.DATE_ >= CONVERT(dateTime, '1-1-2000', 101)) AND (ORFIC.DATE_ <= CONVERT(dateTime, '12-31-2050', 101)))AND(ORFIC.CANCELLED=0))OR((LGMAIN.MODULENR=4 AND ((INVFC.BRANCH BETWEEN 0 AND 13)))AND((INVFC.DATE_ >= CONVERT(dateTime, '1-1-2000', 101)) AND (INVFC.DATE_ <= CONVERT(dateTime, '12-31-2050', 101)))AND(INVFC.CANCELLED=0))OR((LGMAIN.MODULENR IN (5,61,62) AND ((CTRNS.BRANCH BETWEEN 0 AND 13)))AND((CTRNS.DATE_ >= CONVERT(dateTime, '1-1-2000', 101)) AND (CTRNS.DATE_ <= CONVERT(dateTime, '12-31-2050', 101)))AND(CTRNS.CANCELLED=0))OR((LGMAIN.MODULENR=6 AND ((RLFIC.BRANCH BETWEEN 0 AND 13)))AND((RLFIC.DATE_ >= CONVERT(dateTime, '1-1-2000', 101)) AND (RLFIC.DATE_ <= CONVERT(dateTime, '12-31-2050', 101)))AND(RLFIC.CANCELLED=0))OR((LGMAIN.MODULENR=7 AND ((BTRNS.BRANCH BETWEEN 0 AND 13)))AND((BTRNS.DATE_ >= CONVERT(dateTime, '1-1-2000', 101)) AND (BTRNS.DATE_ <= CONVERT(dateTime, '12-31-2050', 101)))AND(BTRNS.CANCELLED=0))OR((LGMAIN.MODULENR=10 AND ((CASHTR.BRANCH BETWEEN 0 AND 13)))AND((CASHTR.DATE_ >= CONVERT(dateTime, '1-1-2000', 101)) AND (CASHTR.DATE_ <= CONVERT(dateTime, '12-31-2050', 101)))AND(CASHTR.CANCELLED=0)))) AND (LGMAIN.TOTAL-PAID > 0) AND (LGMAIN.TOTAL - LGMAIN.PAID > 0.00000001) AND (LGMAIN.SIGN = 0) AND (LGMAIN.CANCELLED = 0) AND (LGMAIN.PAIDINCASH <> 1)

AND CLCARD.CODE LIKE 'M%'

GROUP BY INVFC.DATE_, CODE,CLCARD.DEFINITION_, INVFC.FICHENO , INVFC.DOCODE

KLASORDE DOSYALARIN ICINDEKI YAZIYA GORE ARAMA YAPMA SEARCH EVERYTHING UYGULAMASI

KLASORDE DOSYALARIN ICINDEKI YAZIYA GORE ARAMA YAPMA SEARCH EVERYTHING UYGULAMASI

ARAMA YERİNE =
C:\Users\PC\Documents\sadasdasdsaddsa content:"dosyaiçindekiyazi"

LOGO OBJECTS EXPORT TO XML

UnityObjects.Data OF = M.NewObjectData(UnityObjects.DataObjectType.doSalesOrderSlip);
....

string FISNO = DateTime.Now.ToString("yyyyMMddHHmmss").ToString();
EMF.ExportToXML("GL_VOUCHERS", "C:/Users/PC/Desktop/devr/" + FISNO + ".xml");
if (!OF.Post())
....

"SALES_ORDER" = XML OLARAK LOGODAN KAYDEDİNCE EN ÜSTTTE XMLİN ALTINDAKİDİR

LOGO J-PLATFORM İŞLEMLERİ

C:\LOGO\J-GUAR\SELFSERVIS\UniServer\www\HrSelfService\app\View\Themed\MainTheme\Users\login.ctp

şifremi unutummu sil :
----------------------
<div class="row">
<a id="forgotPasswordLink" href="#forgotSection"><?php echo $resLib::getLocalStrRes($formResDomain, 7, "Şifremi Unuttum")?></a>
</div>

sosyal medyaları sil :
----------------------
<div id="socialWidgets">
<?php if (!empty($configParam)){?>
<a target="_blank" href="<?php echo $configParam->facebookAddress?>">
<i class="lbs-icon lbs-icon-facebook lbs-icon-bg lbs-icon-2x"></i>
</a>
<a target="_blank" href="<?php echo $configParam->twitterAddress?>">
<i class="lbs-icon lbs-icon-twitter lbs-icon-bg lbs-icon-2x"></i>
</a>
<a target="_blank" href="<?php echo $configParam->linkedinAddress?>">
<i class="lbs-icon lbs-icon-linkedin lbs-icon-bg lbs-icon-2x"></i>
</a>
<?php
}
else {?>
<a target="_blank" href="http://www.facebook.com/logoyazilim">
<i class="lbs-icon lbs-icon-facebook lbs-icon-bg lbs-icon-2x"></i>
</a>
<a target="_blank" href="http://twitter.com/logo_bs">
<i class="lbs-icon lbs-icon-twitter lbs-icon-bg lbs-icon-2x"></i>
</a>
<a target="_blank" href="http://www.linkedin.com/company/13830">
<i class="lbs-icon lbs-icon-linkedin lbs-icon-bg lbs-icon-2x"></i>
</a>
<?php } ?>
</div>

logoyu sil (değiştirebilirsinde ama <img src="data:image/png;base64,resimkodu?> olmalı:
------------
<?=$this->element('firmInfo', array('classes' => 'row'))?>

LOGO YANSITMA ŞABLONU QUERYISI

DECLARE @GIDER_BASL_HESAP_KODU VARCHAR(50), @GIDER_BITIS_HESAP_KODU VARCHAR(50), @GIDER_HESAP_KODU VARCHAR(50), @GIDER_YANSITMA_HESAP_KODU VARCHAR(50), @GELIR_YANSITMA_HESAP_KODU VARCHAR(50), @TARIH_BASL VARCHAR(50), @TARIH_BITIS VARCHAR(50), @FIRMNR VARCHAR(3),
@ACIKLAMA_YANSITMA1 VARCHAR(150),@ACIKLAMA_YANSITMA2 VARCHAR(150),@ACIKLAMA_GELIRLERIKAPAT VARCHAR(150),@ACIKLAMA_GIDERLERIKAPAT VARCHAR(150)
SET @FIRMNR='212';
SET @GIDER_HESAP_KODU = '690.01.01.000'
SET @GIDER_BASL_HESAP_KODU='720.01.01.001'
SET @GIDER_BITIS_HESAP_KODU='720.01.01.002'
SET @GIDER_YANSITMA_HESAP_KODU = '721.01.01.001'
SET @GELIR_YANSITMA_HESAP_KODU = '620.01.01.001'
SET @TARIH_BASL= '1-1-2018'
SET @TARIH_BITIS='12-31-2018'
SET @ACIKLAMA_YANSITMA1='24.10.2019 tarihli dönem içi yansıtma kaydı';
SET @ACIKLAMA_YANSITMA2='24.10.2019 tarihli dönem sonu yansıtma kaydı';
SET @ACIKLAMA_GELIRLERIKAPAT='24.10.2019 Tarihli Gelir Kapatma Kaydı';
SET @ACIKLAMA_GIDERLERIKAPAT='24.10.2019 Tarihli Gider Kapatma Kaydı';
--YANSITMA1
SELECT * FROM (
SELECT *
FROM
(
SELECT '1-YANSITMA1' TUR,
@GELIR_YANSITMA_HESAP_KODU BORC_HESAP,
@GIDER_YANSITMA_HESAP_KODU ALACAK_HESAP,@ACIKLAMA_YANSITMA1 ACIKLAMA,
BRANCH ISYERINR,
(
SELECT NAME FROM L_CAPIDIV D WHERE D.NR = GLTRN.BRANCH AND D.FIRMNR = @FIRMNR
) ISYERI,
GLTRN.TRCURR,
CONVERT(DECIMAL(10, 2), SUM(GLTRN.DEBIT)) DEBIT,
CONVERT(DECIMAL(10, 2), SUM(GLTRN.CREDIT)) CREDIT,
CONVERT(DECIMAL(10, 2), SUM(GLTRN.REPORTNET * (1 - 2 * GLTRN.SIGN))) REPORTNET,
CONVERT(DECIMAL(10, 2), SUM(GLTRN.TRNET * (1 - 2 * GLTRN.SIGN))) TRNET_TUTAR, CONVERT(DECIMAL(10, 2), SUM(GLTRN.TRNET * (1 - 2 * GLTRN.SIGN))) SON
FROM LG_212_01_EMFLINE GLTRN WITH (NOLOCK)
WHERE (GLTRN.DATE_ >= CONVERT(DATETIME, @TARIH_BASL, 101))
AND (GLTRN.DATE_ <= CONVERT(DATETIME, @TARIH_BITIS, 101))
AND (GLTRN.CANCELLED = 0)
AND (GLTRN.TRCODE NOT IN ( 5 ))
AND --
ACCOUNTCODE
BETWEEN @GIDER_BASL_HESAP_KODU AND @GIDER_BITIS_HESAP_KODU
GROUP BY GLTRN.TRCURR,
BRANCH
) AS T
WHERE T.TRNET_TUTAR <> 0
--YANSITMA2
UNION ALL
SELECT *
FROM
(
SELECT '2-YANSITMA2' TUR,
@GIDER_YANSITMA_HESAP_KODU BORC,
ACCOUNTCODE ALACAK,@ACIKLAMA_YANSITMA2 ACIKLAMA,
BRANCH ISYERINR,
(
SELECT NAME FROM L_CAPIDIV D WHERE D.NR = GLTRN.BRANCH AND D.FIRMNR = @FIRMNR
) ISYERI,
GLTRN.TRCURR,
CONVERT(DECIMAL(10, 2), SUM(GLTRN.DEBIT)) DEBIT,
CONVERT(DECIMAL(10, 2), SUM(GLTRN.CREDIT)) CREDIT,
CONVERT(DECIMAL(10, 2), SUM(GLTRN.REPORTNET * (1 - 2 * GLTRN.SIGN))) REPORTNET,
CONVERT(DECIMAL(10, 2), SUM(GLTRN.TRNET * (1 - 2 * GLTRN.SIGN))) TRNET_TUTAR, CONVERT(DECIMAL(10, 2), SUM(GLTRN.TRNET * (1 - 2 * GLTRN.SIGN))) SON
FROM LG_212_01_EMFLINE GLTRN WITH (NOLOCK)
WHERE (GLTRN.DATE_ >= CONVERT(DATETIME,@TARIH_BASL, 101))
AND (GLTRN.DATE_ <= CONVERT(DATETIME,@TARIH_BITIS, 101))
AND (GLTRN.CANCELLED = 0)
AND (GLTRN.TRCODE NOT IN ( 5 ))
AND --
ACCOUNTCODE
BETWEEN @GIDER_BASL_HESAP_KODU AND @GIDER_BITIS_HESAP_KODU
GROUP BY GLTRN.TRCURR,ACCOUNTCODE,BRANCH
) AS T
WHERE T.TRNET_TUTAR <> 0
--GELİRLERİ KAPAT
UNION ALL
SELECT * from (
SELECT *
FROM
(
SELECT '3-GELİRLERİ KAPAT' TUR,
@GIDER_HESAP_KODU BORC,
ACCOUNTCODE ALACAK,@ACIKLAMA_GELIRLERIKAPAT ACIKLAMA,
BRANCH ISYERINR,
(
SELECT NAME FROM L_CAPIDIV D WHERE D.NR = GLTRN.BRANCH AND D.FIRMNR = @FIRMNR
) ISYERI,
GLTRN.TRCURR,
CONVERT(DECIMAL(10, 2), SUM(GLTRN.DEBIT)) DEBIT,
CONVERT(DECIMAL(10, 2), SUM(GLTRN.CREDIT)) CREDIT,
CONVERT(DECIMAL(10, 2), SUM(GLTRN.REPORTNET * (1 - 2 * GLTRN.SIGN))) REPORTNET,
CONVERT(DECIMAL(10, 2), SUM(GLTRN.TRNET * (1 - 2 * GLTRN.SIGN))) TRNET_TUTAR,
CONVERT(DECIMAL(10, 2), SUM(GLTRN.DEBIT))- CONVERT(DECIMAL(10, 2), SUM(GLTRN.CREDIT)) SON
FROM LG_212_01_EMFLINE GLTRN WITH (NOLOCK)
WHERE (GLTRN.DATE_ >= CONVERT(DATETIME,@TARIH_BASL, 101))
AND (GLTRN.DATE_ <= CONVERT(DATETIME, @TARIH_BITIS, 101))
AND (GLTRN.CANCELLED = 0)
AND (GLTRN.TRCODE NOT IN ( 5 ))
AND
(ACCOUNTCODE LIKE LEFT(@GIDER_HESAP_KODU,1) +'%' AND ACCOUNTCODE<@GIDER_HESAP_KODU)
GROUP BY ACCOUNTCODE,GLTRN.TRCURR,BRANCH
) AS T
WHERE
(DEBIT-CREDIT>0) ) AS T2 WHERE SON>0
--GIDERLERİ KAPAT
UNION ALL
SELECT * from (
SELECT *
FROM
(
SELECT '4-GIDERLERİ KAPAT' TUR,
ACCOUNTCODE BORC,
@GIDER_HESAP_KODU ALACAK,@ACIKLAMA_GIDERLERIKAPAT ACIKLAMA,
BRANCH ISYERINR,
(
SELECT NAME FROM L_CAPIDIV D WHERE D.NR = GLTRN.BRANCH AND D.FIRMNR = @FIRMNR
) ISYERI,
GLTRN.TRCURR,
CONVERT(DECIMAL(10, 2), SUM(GLTRN.DEBIT)) DEBIT,
CONVERT(DECIMAL(10, 2), SUM(GLTRN.CREDIT)) CREDIT,
ABS(CONVERT(DECIMAL(10, 2), SUM(GLTRN.REPORTNET * (1 - 2 * GLTRN.SIGN)))) REPORTNET,
ABS(CONVERT(DECIMAL(10, 2), SUM(GLTRN.TRNET * (1 - 2 * GLTRN.SIGN)))) TRNET_TUTAR,
CONVERT(DECIMAL(10, 2), SUM(GLTRN.CREDIT))- CONVERT(DECIMAL(10, 2), SUM(GLTRN.DEBIT)) SON
FROM LG_212_01_EMFLINE GLTRN WITH (NOLOCK)
WHERE (GLTRN.DATE_ >= CONVERT(DATETIME, @TARIH_BASL, 101))
AND (GLTRN.DATE_ <= CONVERT(DATETIME,@TARIH_BITIS, 101))
AND (GLTRN.CANCELLED = 0)
AND (GLTRN.TRCODE NOT IN ( 5 ))
AND --
(ACCOUNTCODE LIKE LEFT(@GIDER_HESAP_KODU,1) +'%' AND ACCOUNTCODE<@GIDER_HESAP_KODU)
GROUP BY ACCOUNTCODE,GLTRN.TRCURR,BRANCH
) AS T
WHERE
T.CREDIT-T.DEBIT>0 ) AS T2
) AS YANSITMASABLONU ORDER BY TUR,YANSITMASABLONU.ISYERINR,YANSITMASABLONU.TRCURR

C# BIR FORMDAN AÇIK BAŞKA BIR FORMA BILGI AKTARIMI

ÖNCELİKLE AKTARILACAK OLAN FORMDAKI CONTROLLERIN MODIFY PRIVATEDEN PUBLICE DEGISTIRILMELI.

public partial class MAINFORM : Form
{
SECONDFORM SF;
}

public MAINFORM()
{

}

private void simpleButton1_Click(object sender, EventArgs e)
{

gb_UC_CONTAINER.Controls.Clear();
UCP = new SICILLERHAREKET(M, CFG, C);
gb_UC_CONTAINER.Controls.Add(UCP);
UCP.Dock = DockStyle.Fill;
}

private void sle_PROJELER_EditValueChanged(object sender, EventArgs e)
{
SF.te_PROJE.Text = (string)sle_PROJELER.EditValue;
}

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

USE [LEVENT]
GO

/****** Object: View [dbo].[BM_211_STOKAYLIK] Script Date: 14.10.2019 17:13:13 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER 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
-- where ITEMS2.CODE='ESARF-001'
order by tarih
GO

LOGO OBJECTS REGISTERLEME TÜRLERİ

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

LOGO SQL AYRINTILI MALIYET ANALIZ RAPORU

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

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

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

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

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

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

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

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

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

LOGO SQL FATURAYA BAGLI IRSALIYELERIN ESITLIK KONTROLLERI

SELECT *
,CASE WHEN T.FTARIH=T.ITARIH THEN 'ESIT' ELSE 'ESIT DEGIL' END AS TARIHESITMI
,CASE WHEN T.FTUTAR=T.ITUTAR THEN 'ESIT' ELSE 'ESIT DEGIL' END AS TUTARESITMI
,CASE WHEN T.FISYERI=T.IISYERI THEN 'ESIT' ELSE 'ESIT DEGIL' END AS ISYERIESITMI
,CASE WHEN T.FAMBAR=T.IAMBAR THEN 'ESIT' ELSE 'ESIT DEGIL' END AS AMBARESITMI
FROM(
SELECT I.FICHENO FFISNO,I.DATE_ FTARIH,I.NETTOTAL FTUTAR,I.BRANCH FISYERI,I.SOURCEINDEX FAMBAR,
S.FICHENO IFISNO,S.DATE_ ITARIH,S.NETTOTAL ITUTAR,S.BRANCH IISYERI,S.SOURCEINDEX IAMBAR
FROM LG_104_01_INVOICE I WITH(NOLOCK)
LEFT JOIN LG_104_01_STFICHE S ON S.INVOICEREF=I.LOGICALREF WHERE S.TRCODE=1 AND I.TRCODE=1
) AS T

LOGO İRSALİYELERİN TARİHİNİ FATURA TARİHLERİNE EŞİTLEME

--STLINE TARİHİ EŞİTLE
UPDATE INVDATEUPDT SET STLDATE=INVDATE FROM
(
SELECT STL.DATE_ AS STLDATE,
INV.DATE_ AS INVDATE
FROM LG_201_01_STLINE AS STL
LEFT OUTER JOIN
LG_201_01_INVOICE AS INV ON INV.LOGICALREF=STL.INVOICEREF
WHERE STL.INVOICEREF<>0 AND STL.DATE_<>INV.DATE_
)AS INVDATEUPDT
----------------------------------------------
--IRSALYE TARİHİ EŞİTLE
UPDATE STFICHEDATEUPD SET STFDATE=INVDATE FROM
(
SELECT INV.DATE_ AS INVDATE,
STF.DATE_ AS STFDATE
FROM LG_201_01_INVOICE AS INV
LEFT OUTER JOIN
LG_201_01_STFICHE AS STF ON INV.LOGICALREF=STF.INVOICEREF
WHERE STF.INVOICEREF<>0 AND STF.DATE_<>INV.DATE_
) AS STFICHEDATEUPD
*/

LOGO AMORTİSMAN HESAPLAMA İŞLEMLERİ

önce dbden ilgili yıl ve aydaki hesaplamalar silinir
delete from LG_101_FAYEAR WHERE YEAR_ in (2019) AND CALCMON in (3)

duran varlık yönetimi-işlemler-toplu amortisman değerleme/hesaplama
2019 ve 3üncü ay

daha sonra dosya öndeğerleme duran varlık hareketlerinden ilgili yıl ve ayı seç muhasebe masraf değerlerini bağlantıdan okuya bas ve tamama bas.

Daha sonra genel muhasebe - işlemler - muhasebeleştirme - sabit kıymet amortisman ve değerleme işlemleri ilgili yıl ve tamam bas

SQL SERVER TUNING TARAFINDAN OLUSTURULAN INDEXLERI BULMA

SELECT * FROM (
SELECT i.[name] as index_name,
substring(column_names, 1, len(column_names)-1) as [columns],
case when i.[type] = 1 then 'Clustered index'
when i.[type] = 2 then 'Nonclustered unique index'
when i.[type] = 3 then 'XML index'
when i.[type] = 4 then 'Spatial index'
when i.[type] = 5 then 'Clustered columnstore index'
when i.[type] = 6 then 'Nonclustered columnstore index'
when i.[type] = 7 then 'Nonclustered hash index'
end as index_type,
case when i.is_unique = 1 then 'Unique'
else 'Not unique' end as [unique],
schema_name(t.schema_id) + '.' + t.[name] as table_view,
case when t.[type] = 'U' then 'Table'
when t.[type] = 'V' then 'View'
end as [object_type]
from sys.objects t
inner join sys.indexes i
on t.object_id = i.object_id
cross apply (select col.[name] + ', '
from sys.index_columns ic
inner join sys.columns col
on ic.object_id = col.object_id
and ic.column_id = col.column_id
where ic.object_id = t.object_id
and ic.index_id = i.index_id
order by col.column_id
for xml path ('') ) D (column_names)
where t.is_ms_shipped <> 1
and index_id > 0
)
AS T WHERE T.index_name LIKE '_DTA%'

--silmek isdersen DROP INDEX index_name;

SQL SERVER TEK HUCREDE VIRGULLU DATAYI ROWA CEVIRME

create FUNCTION [dbo].[SM_SPLIT](
@delimited NVARCHAR(MAX),
@delimiter NVARCHAR(100)
) RETURNS @table TABLE (id INT IDENTITY(1,1), [value] NVARCHAR(MAX))
AS
BEGIN
DECLARE @xml XML
SET @xml = N'<t>' + REPLACE(@delimited,@delimiter,'</t><t>') + '</t>'
INSERT INTO @table([value])
SELECT r.value('.','Nvarchar(MAX)') as item
FROM @xml.nodes('/t') as records(r)
RETURN
END

select * from dbo.SM_SPLIT(REPLACE('0, 111, 121, 131, 141, 211, 221, 231, 241, 251, 261, 271, 281, 212, 222, 232, 242, 252, 262, 272',' ',''),',')

LOGO KURULU SERVERIN ŞİFRESİ DEĞİŞTİĞİNDE LOGODA SERVİS BAŞLAMAMA HATASI ÇÖZÜMÜ

start run comexp.msc /32
Administrative Tools -> Component Services -> My computer -> DCOM
bunu bul {00CDA438-213D-47A3-AF3E-13DB9290D2A0}
sağ tık identity de mevcut admini kaydet şifresiyle

SC DELETE "LOGO_TIGER3ENTERPRISE_Service_17500_300794_2_57"

SC CREATE "LOGO_TIGER3ENTERPRISE_Service_17500_300794_2_57" binpath="D:\LOGO\TIGER3ENTERPRISE\LOGO_TIGER3ENTERPRISE_Service.exe"

SERVİS YİNEDE ÇALIŞMAZSA VERSİON GUNCELLE OLARAK 2 defa TEKRAR KUR

C# WCF (SVC) WEB SERVISLERINI IISDE CALISTIRMAK ICIN YAPILMASI GEREKENLER

WINDOWS 10: TURN WINDOWS FEATURE ON OFF
.NET FRAMEWORK 4.7 ADVANCED SETTINGS- WCF SERVICES-HTTP ACTIVATION SEC VE OK DE

SONRA WEBCONFIGDE.

<serviceHostingEnvironment aspNetCompatibilityEnabled="true" multipleSiteBindingsEnabled="true" />
YAP:
<serviceHostingEnvironment aspNetCompatibilityEnabled="false" multipleSiteBindingsEnabled="true" />

LOD LOGO OBJECTS DESIGNER

STRUCTURE - > CUSTENTR1
EVENTS BEFORESAVEE AŞAĞIDAKINI YAPIŞTIR
Sub BeforeSave(proceed as Integer)
MaxGridLine=MGridLines("InvoiceGrid")-1
for i=0 to MaxGridLine
GetGridRowHandle("InvoiceGrid",i,recH)
AppTables[9].GetIntFieldR(recH,"LINETYPE",intLineType)
AppTables[9].GetIntFieldR(recH,"GLOBTRANS",intGlobTrans)
AppTables[9].GetIntFieldR(recH,"PARENTLNREF",intPARENTLNREF)
AppTables[9].GetIntFieldR(recH,"TRCODE",intTRCODE)
if (intLineType = 1) and (intGlobTrans = 1) and (intPARENTLNREF = 0) and (intTRCODE = 1) then
warn("Fiş Genelindeki Promosyonları silmelisiniz")
proceed = 0
end if
next i
End Sub

sql belirli bir karakterden sonrasını alsın - charindex

select right('sadasd-sadasd', charindex('-', reverse('sadasd-sadasd')) - 1)

logo sql mail at sp_Send_DBMail

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON

DECLARE @Delimiter Char(1)
DECLARE @Dosyaadi VARCHAR(249)

SET @Delimiter = CHAR(9) /*Tab char(9) Line feed char(10) Carriage return char(13)*/
SET @Dosyaadi='Sanayi_Güvenli_Stok_Seviye_Kontrolü_'+CONVERT(VARCHAR(10),GETDATE(),104)+'-'+ REPLACE( CONVERT(VARCHAR(8), CONVERT(TIME, GETDATE())),':','.')+'.xls';

EXEC MSDB.dbo.sp_Send_DBMail
@profile_name = 'MAIL_PROFILE',
@Recipients='erinc@bilmarkltd.com',
@Subject='erinç',
@Body='erinç',
@Query='set nocount on; SET QUOTED_IDENTIFIER On ;SELECT
[Ambar]
,[Malzeme Kodu]
,[Malzeme Açıklaması]
,REPLACE( CONVERT(VARCHAR, [Asgari Miktar]),''.'','','') AS [Asgari Miktar]
,REPLACE( CONVERT(VARCHAR, [Güvenli Miktar]),''.'','','') AS [Güvenli Miktar]
,REPLACE( CONVERT(VARCHAR, [Azami Miktar]),''.'','','') AS [Azami Miktar]
,REPLACE( CONVERT(VARCHAR, Miktar),''.'','','') AS [Miktar]
,[Kontrol]
,[Asgari Seviye Kontrolü]
,[Azami Seviye Kontrolü]
,[Güvenli Seviye Kontrolü]
,[Negatif Seviye Kontrolü]

FROM MYDB.[dbo].[BM_211_AMBAR_PARAMETRE_KONTROL]',
@Attach_Query_Result_As_File = 1,
@Query_Result_Header = 1,
@Query_Attachment_Filename = @Dosyaadi,
@query_result_no_padding=1,
@query_result_width = 32767,
@Query_Result_Separator = @Delimiter

SET ANSI_NULLS OFF
SET QUOTED_IDENTIFIER OFF

LOGO SQL MUHASEBE FİŞLERİ KAYNAK FİŞ TARİH KONTROLU

CREATE FUNCTION [dbo].[BMF_EMFICHE_MODULENR_KAYNAKFISTURU] (@EMFICHEMODULENR INT)
RETURNS VARCHAR(255)
BEGIN
DECLARE @EMFICHE_MODULENR_KAYNAKFISTURU VARCHAR(255);
SET @EMFICHE_MODULENR_KAYNAKFISTURU = (
CASE @EMFICHEMODULENR
WHEN 0 THEN '(0) Kaynak Fiş Türü Olmayan'
WHEN 1 THEN '(1) Malzeme Fişleri'
WHEN 2 THEN '(2) Satın Alma Faturası'
WHEN 3 THEN '(3) Satis Faturasi'
WHEN 4 THEN '(4) Cari Hesap Fisleri'
WHEN 5 THEN '(5) Cek/Senet Bordrosu'
WHEN 6 THEN '(6) Banka Fisleri'
WHEN 7 THEN '(7) Kasa Islemleri'
WHEN 20 THEN '(20) Dagitim Fisi'
WHEN 25 THEN '(25) Sabit Kiymet'
WHEN 160 THEN '(160) Teminat Bordrolari'
WHEN 170 THEN '(170) Leasing Islemleri'
END
)
RETURN @EMFICHE_MODULENR_KAYNAKFISTURU
END
GO

CREATE FUNCTION [dbo].[BMF_EMFLINE_TRCODE_ACIKLAMA] (@EMFLINETRCODE INT)
RETURNS VARCHAR(255)
BEGIN
DECLARE @BMF_EMFLINE_TRCODE_ACIKLAMA VARCHAR(255);
SET @BMF_EMFLINE_TRCODE_ACIKLAMA = (
CASE @EMFLINETRCODE
WHEN 1 THEN '(1) Açılış Fişi'
WHEN 2 THEN '(2) Tahsil Fişi'
WHEN 3 THEN '(3) Tediye Fişi'
WHEN 4 THEN '(4) Mahsup Fişi'
WHEN 5 THEN '(5) Özel Fiş'
WHEN 6 THEN '(6) Kur Farkı Fişi'
WHEN 7 THEN '(7) Kapanış Fişi'
WHEN 8 THEN '(8) Enflasyon Muh. Fişi'
WHEN 9 THEN '(9) Konsolidasyon Düzeltme Fişi'
WHEN 10 THEN '(10) TFRS Düzeltme Fişi'
END
)
RETURN @BMF_EMFLINE_TRCODE_ACIKLAMA
END
GO

----------------

SELECT * FROM (
SELECT
-- DISTINCT dbo.BMF_EMFICHE_MODULENR_KAYNAKFISTURU(EMFICHE.MODULENR) KAYNAKFISTURUMODULENR,dbo.[BMF_EMFICHE_TRCODE_FISTURU] (EMFICHE.TRCODE) FISTURUTRCODE
dbo.BMF_EMFICHE_MODULENR_KAYNAKFISTURU(EMFICHE.MODULENR) KAYNAKFISTURUMODULENR,dbo.[BMF_EMFICHE_TRCODE_FISTURU] (EMFICHE.TRCODE) FISTURUTRCODE,
EMFICHE.FICHENO,
EMFICHE.DATE_ EMFICHEDATE,
CASE EMFICHE.MODULENR
WHEN 1 THEN (SELECT TOP 1 K.DATE_ FROM LG_211_01_STFICHE K WHERE K.LOGICALREF=EMFLINE.SOURCEFREF)
WHEN 2 THEN (SELECT TOP 1 K.DATE_ FROM LG_211_01_INVOICE K WHERE K.LOGICALREF=EMFLINE.SOURCEFREF)
WHEN 3 THEN (SELECT TOP 1 K.DATE_ FROM LG_211_01_INVOICE K WHERE K.LOGICALREF=EMFLINE.SOURCEFREF)
WHEN 4 THEN (SELECT TOP 1 K.DATE_ FROM LG_211_01_CLFICHE K WHERE K.LOGICALREF=EMFLINE.SOURCEFREF)
WHEN 5 THEN (SELECT TOP 1 K.DATE_ FROM LG_211_01_CSROLL K WHERE K.LOGICALREF=EMFLINE.SOURCEFREF)
WHEN 6 THEN (SELECT TOP 1 K.DATE_ FROM LG_211_01_BNFICHE K WHERE K.LOGICALREF=EMFLINE.SOURCEFREF)
WHEN 7 THEN (SELECT TOP 1 K.DATE_ FROM LG_211_01_KSLINES K WHERE K.LOGICALREF=EMFLINE.SOURCEFREF)
WHEN 11 THEN (SELECT TOP 1 K.DATE_ FROM LG_211_01_CSROLL K WHERE K.LOGICALREF=EMFLINE.SOURCEFREF)
WHEN 0 THEN EMFICHE.DATE_
END KAYNAKFISDATE
FROM LG_211_01_EMFICHE EMFICHE WITH(NOLOCK)
LEFT JOIN LG_211_01_EMFLINE EMFLINE ON EMFICHE.LOGICALREF=EMFLINE.ACCFICHEREF
) AS T WHERE T.EMFICHEDATE<>T.KAYNAKFISDATE

C# INSERT COMMAND WITH SCOPE IDENTITY

private void CREATE_NEW_LIST()
{
SqlCommand COM = null;
SqlTransaction TRANSACTION = null;
SqlConnection CON = new SqlConnection(CONSTR_LG);
if (CON.State != ConnectionState.Open)
CON.Open();
TRANSACTION = CON.BeginTransaction();

COM = INSERT_INTO_BM_PNTAWARDFICHE(int.Parse(le_CAPIFIRM.EditValue.ToString()), int.Parse(textEditYIL.Text), int.Parse(comboBoxEditAY.Text));
COM.Connection = CON;
COM.Transaction = TRANSACTION;
int LOGICALREF = int.Parse(COM.ExecuteScalar().ToString());
// YUKARIDA EXECUTE SCALAR OLDUGU ICIN GEREK YOK BUNA: COM.ExecuteNonQuery();

COM = UTILITIES.DELETE_PNTAWARDLINES(int.Parse(le_CAPIFIRM.EditValue.ToString()), int.Parse(textEditYIL.Text), int.Parse(comboBoxEditAY.Text));
COM.Connection = CON;
COM.Transaction = TRANSACTION;
COM.ExecuteNonQuery();

TRANSACTION.Commit();
}

public static SqlCommand INSERT_INTO_BM_PNTAWARDFICHE(int FIRMNR, int YEAR, int MONTH)
{
try
{
return new SqlCommand("INSERT INTO BM_PNTAWARDFICHE(FIRMNR, YEAR, MONTH, FOOD, TRANSPORTATION, OBIM, KASA, FOOD_B, TRANSPORTATION_B, OBIM_B, KASA_B, CREATEDBY, CREATEDDATE, MODIFIEDBY, MODIFIEDDATE) VALUES ('" + FIRMNR + "', " + YEAR + ", " + MONTH + ", 0, 0,0, 0, 0, 0, 0, 0, 99, '" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "', 0, 0) select SCOPE_IDENTITY() ");
}
catch { return null; }
}

public static string CONSTR_LG = string.Format("Data Source={0};Initial Catalog={1};User Id={2};Password={3};MultipleActiveResultSets=True;", BMS_DLL.CFGICERIK.LGDBSERVER, BMS_DLL.CFGICERIK.LGDBDATABASE, BMS_DLL.CFGICERIK.LGDBUSERNAME, BMS_DLL.CFGICERIK.LGDBPASSWORD);

SQL SERVER SCALAR VALUED FUNCTION - LOGO

-- KULLANIMLAR BU TARZ OLUR:
SELECT [dbo].BMF_STLINE_TRCODE_ACIKLAMA(STLINE.TRCODE) [Fiş Türü],* FROM LG_211_01_STLINE AS STLINE
-- STLINE FİŞ TÜRLERİ

CREATE FUNCTION [dbo].[BMF_STLINE_TRCODE_ACIKLAMA](@TRCODE INT)
RETURNS VARCHAR(255)
BEGIN
DECLARE @STLINE_TRCODE_ACIKLAMA VARCHAR(255);
SET @STLINE_TRCODE_ACIKLAMA = (
CASE
@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

)
RETURN @STLINE_TRCODE_ACIKLAMA
END

-- KULLANIMI SELECT [dbo].BMF_STLINE_TRCODE_ACIKLAMA(STLINE.TRCODE) [Fiş Türü],* FROM LG_211_01_STLINE AS STLINE

--- CLFLINE ISLEM TURLERI

CREATE FUNCTION [dbo].[BMF_CLFLINE_ISLEMTURU_ACIKLAMA](@CLFLINEMODULENR INT, @CLFLINETRCODE INT)
RETURNS VARCHAR(255)
BEGIN
DECLARE @CLFLINE_ISLEMTURU_ACIKLAMA VARCHAR(255);
SET @CLFLINE_ISLEMTURU_ACIKLAMA = (
CASE ((@CLFLINEMODULENR*100)+@CLFLINETRCODE)
WHEN 381 THEN 'Satış Siparişi'
WHEN 382 THEN 'Satınalma Siparişi'
WHEN 431 THEN 'Satın Alma Faturası'
WHEN 432 THEN 'Perakende Satış İade Faturası'
WHEN 433 THEN 'Toptan satış iade faturası'
WHEN 434 THEN 'Alınan Hizmet Faturası'
WHEN 435 THEN 'Alınan proforma faturası'
WHEN 436 THEN 'Alım iade faturası'
WHEN 437 THEN 'Perakende Satış Faturası'
WHEN 438 THEN 'Toptan satış faturası'
WHEN 439 THEN 'Verilen hizmet faturası'
WHEN 440 THEN 'Verilen proforma faturası'
WHEN 441 THEN 'Verilen vade farkı faturası'
WHEN 442 THEN 'Alınan Vade farkı faturası'
WHEN 443 THEN 'Alınan fiyat farkı faturası'
WHEN 444 THEN 'Verilen fiyat farkı faturası'
WHEN 456 THEN 'Müstahsil makbuzu'
WHEN 501 THEN 'Nakit tahsilat'
WHEN 502 THEN 'Nakit ödeme'
WHEN 503 THEN 'Borç Dekontu'
WHEN 504 THEN 'Alacak Dekontu'
WHEN 505 THEN 'Virman Işlemi'
WHEN 506 THEN 'Kur farkı işlemi'
WHEN 512 THEN 'Özel işlem'
WHEN 514 THEN 'Açılış Fişi'
WHEN 570 THEN 'Kredi Kartı Fişi'
WHEN 661 THEN 'Çek girişi'
WHEN 662 THEN 'Senet girişi'
WHEN 663 THEN 'Çek çıkış cari hesaba'
WHEN 664 THEN 'Senet çıkış cari hesaba'
WHEN 720 THEN 'Gelen havaleler'
WHEN 721 THEN 'Gönderilen havaleler'
WHEN 728 THEN 'Banka Alınan Hizmet'
WHEN 729 THEN 'Banka Verilen Hizmet'
WHEN 1001 THEN 'Nakit tahsilat'
WHEN 1002 THEN 'Nakit ödeme'
END
)
RETURN @CLFLINE_ISLEMTURU_ACIKLAMA
END

--PAYTRANS DOVIZ TURU
USE [LEVENT]
GO

/****** Object: UserDefinedFunction [dbo].[Get_Ambar_Aciklama] Script Date: 1.07.2019 10:32:36 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[BMF_PAYTRANS_DOVIZ_CINSI](@PAYTRANSTRCURR INT)
RETURNS VARCHAR(255)
BEGIN
DECLARE @PAYTRANS_DOVIZ_CINSI VARCHAR(255);
SET @PAYTRANS_DOVIZ_CINSI = (
CAST((case @PAYTRANSTRCURR
WHEN 1 then 'USD'
WHEN 2 then 'DEM'
WHEN 3 then 'AUD'
WHEN 4 then 'ATS'
WHEN 5 then 'BEF'
WHEN 6 then 'DKK'
WHEN 7 then 'FIM'
WHEN 8 then 'FRF'
WHEN 9 then 'NLG'
WHEN 10 then 'SEK'
WHEN 11 then 'CHF'
WHEN 12 then 'ITL'
WHEN 13 then 'JPY'
WHEN 14 then 'CAD'
WHEN 15 then 'KWD'
WHEN 16 then 'NOK'
WHEN 17 then 'GBP'
WHEN 18 then 'SAR'
WHEN 19 then 'XEU'
WHEN 20 then 'EUR'
WHEN 21 then 'AZM'
WHEN 22 then 'BRL'
WHEN 23 then 'BLV'
WHEN 24 then 'CZK'
WHEN 25 then 'CNY'
WHEN 26 then 'EKR'
WHEN 27 then 'GMS'
WHEN 28 then 'INR'
WHEN 29 then 'HKD'
WHEN 30 then 'IQD'
WHEN 31 then 'IRR'
WHEN 32 then 'IEP'
WHEN 33 then 'ESP'
WHEN 34 then 'ILS'
WHEN 35 then 'ISK'
WHEN 36 then 'CYP'
WHEN 37 then 'KGS'
WHEN 38 then 'LTL'
WHEN 39 then 'LBD'
WHEN 40 then 'LBP'
WHEN 41 then 'LVL'
WHEN 42 then 'LXF'
WHEN 43 then 'HNL'
WHEN 44 then 'MYR'
WHEN 45 then 'MXP'
WHEN 46 then 'EGP'
WHEN 47 then 'BBD'
WHEN 48 then 'PLN'
WHEN 49 then 'PTE'
WHEN 50 then 'RML'
WHEN 51 then 'RRU'
WHEN 52 then 'TWD'
WHEN 53 then 'TRL'
WHEN 54 then 'JOD'
WHEN 55 then 'GRD'
WHEN 56 then 'ARA'
WHEN 57 then 'LAK'
WHEN 58 then 'ADP'
WHEN 59 then 'AED'
WHEN 60 then 'AFA'
WHEN 61 then 'ALL'
WHEN 62 then 'ANG'
WHEN 63 then 'AON'
WHEN 64 then 'BDT'
WHEN 65 then 'BHD'
WHEN 66 then 'BIF'
WHEN 67 then 'BMD'
WHEN 68 then 'BND'
WHEN 69 then 'BOB'
WHEN 70 then 'BSD'
WHEN 71 then 'BTN'
WHEN 72 then 'BWP'
WHEN 73 then 'BZD'
WHEN 74 then 'CLP'
WHEN 75 then 'COP'
WHEN 76 then 'CRC'
WHEN 77 then 'CUP'
WHEN 78 then 'CVE'
WHEN 79 then 'DJF'
WHEN 80 then 'DOP'
WHEN 81 then 'DZD'
WHEN 82 then 'ECS'
WHEN 83 then 'ETB'
WHEN 84 then 'FJD'
WHEN 85 then 'FKP'
WHEN 86 then 'GHC'
WHEN 87 then 'GIP'
WHEN 88 then 'GMD'
WHEN 89 then 'GNF'
WHEN 90 then 'GTQ'
WHEN 91 then 'GWP'
WHEN 92 then 'GYD'
WHEN 93 then 'HNL'
WHEN 94 then 'HTG'
WHEN 95 then 'IDR'
WHEN 96 then 'JMD'
WHEN 97 then 'KES'
WHEN 98 then 'KHR'
WHEN 99 then 'KMF'
WHEN 100 then 'KPW'
WHEN 101 then 'KRW'
WHEN 102 then 'KYD'
WHEN 103 then 'LKR'
WHEN 104 then 'LRD'
WHEN 105 then 'LSL'
WHEN 106 then 'MAD'
WHEN 107 then 'MNT'
WHEN 108 then 'MOP'
WHEN 109 then 'MRO'
WHEN 110 then 'MTL'
WHEN 111 then 'MUR'
WHEN 112 then 'MVR'
WHEN 113 then 'MWK'
WHEN 114 then 'MZM'
WHEN 115 then 'NGN'
WHEN 116 then 'NIC'
WHEN 117 then 'NPR'
WHEN 118 then 'NZD'
WHEN 119 then 'OMR'
WHEN 120 then 'PAB'
WHEN 121 then 'PEN'
WHEN 122 then 'PGK'
WHEN 123 then 'PHP'
WHEN 124 then 'PKR'
WHEN 125 then 'PYG'
WHEN 126 then 'QAR'
WHEN 127 then 'RWF'
WHEN 128 then 'SBD'
WHEN 129 then 'SCR'
WHEN 130 then 'SDP'
WHEN 131 then 'SGD'
WHEN 132 then 'SHP'
WHEN 133 then 'SLL'
WHEN 134 then 'SOS'
WHEN 135 then 'SRG'
WHEN 136 then 'STD'
WHEN 137 then 'SVC'
WHEN 138 then 'SYP'
WHEN 139 then 'SZL'
WHEN 140 then 'THB'
WHEN 141 then 'TND'
WHEN 142 then 'TPE'
WHEN 143 then 'TTD'
WHEN 144 then 'TZS'
WHEN 145 then 'UGS'
WHEN 146 then 'UYP'
WHEN 147 then 'VEB'
WHEN 148 then 'VND'
WHEN 149 then 'WST'
WHEN 150 then 'YDD'
WHEN 151 then 'YER'
WHEN 152 then 'YUD'
WHEN 153 then 'ZAR'
WHEN 154 then 'ZMK'
WHEN 155 then 'ZWD'
WHEN 156 then 'KZT'
WHEN 157 then 'UAH'
WHEN 158 then 'TMM'
WHEN 159 then 'UZS'
WHEN 160 then 'TL'
WHEN 0 then 'TL' else '' end) AS VARCHAR(4))
)
RETURN @PAYTRANS_DOVIZ_CINSI
END
GO

--IRSALIYE TURU

CREATE FUNCTION [dbo].[BMF_STFICHE_IRSALIYETURU](@STFICHETRCODE INT)
RETURNS VARCHAR(255)
BEGIN
DECLARE @STFICHE_IRSALIYETURU VARCHAR(255);
SET @STFICHE_IRSALIYETURU = (
(CASE @STFICHETRCODE
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 15 THEN 'Tanımlı Malzeme Fişi - Giriş Fişi 1'
WHEN 16 THEN 'Tanımlı Malzeme Fişi - Giriş Fişi 2'
WHEN 17 THEN 'Tanımlı Malzeme Fişi - Giriş Fişi 3'
WHEN 18 THEN 'Tanımlı Malzeme Fişi - Giriş Fişi 4'
WHEN 19 THEN 'Tanımlı Malzeme Fişi - Giriş Fişi 5'
WHEN 20 THEN 'Tanımlı Malzeme Fişi - Çıkış Fişi 1'
WHEN 21 THEN 'Tanımlı Malzeme Fişi - Çıkış Fişi 2'
WHEN 22 THEN 'Tanımlı Malzeme Fişi - Çıkış Fişi 3'
WHEN 23 THEN 'Tanımlı Malzeme Fişi - Çıkış Fişi 4'
WHEN 24 THEN 'Tanımlı Malzeme Fişi - Çıkış Fişi 5'
WHEN 25 THEN 'Ambar Fişi'
WHEN 26 THEN 'Müstahsil İrsaliyesi'
WHEN 30 THEN 'Tanımlı Satınalma İrsaliyesi 1'
WHEN 31 THEN 'Tanımlı Satınalma İrsaliyesi 2'
WHEN 32 THEN 'Tanımlı Satınalma İrsaliyesi 3'
WHEN 33 THEN 'Tanımlı Satınalma İrsaliyesi 4'
WHEN 34 THEN 'Tanımlı Satınalma İrsaliyesi 5'
WHEN 35 THEN 'Tanımlı Satış İrsaliyesi 1'
WHEN 36 THEN 'Tanımlı Satış İrsaliyesi 2'
WHEN 37 THEN 'Tanımlı Satış İrsaliyesi 3'
WHEN 38 THEN 'Tanımlı Satış İrsaliyesi 4'
WHEN 39 THEN 'Tanımlı Satış İrsaliyesi 5' ELSE '' END)
)
RETURN @STFICHE_IRSALIYETURU
END

--AMBAR ACIKLAMA
CREATE FUNCTION [dbo].[Get_Ambar_Aciklama](@ambarno VARCHAR(255), @sirketno VARCHAR(255))
RETURNS VARCHAR(255)
BEGIN
DECLARE @ambar VARCHAR(255);
SET @ambar = (SELECT NAME FROM L_CAPIWHOUSE WHERE NR = @ambarno AND FIRMNR = @sirketno )
RETURN @ambar
END
GO

--------------------EMFLINE TRCODE
USE [LEVENT]
GO

/****** Object: UserDefinedFunction [dbo].[BMF_CLFLINE_ISLEMTURU_ACIKLAMA] Script Date: 5.07.2019 12:58:25 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[BMF_EMFLINE_TRCODE_ACIKLAMA] (@EMFLINETRCODE INT)
RETURNS VARCHAR(255)
BEGIN
DECLARE @BMF_EMFLINE_TRCODE_ACIKLAMA VARCHAR(255);
SET @BMF_EMFLINE_TRCODE_ACIKLAMA = (
CASE @EMFLINETRCODE
WHEN 1 THEN 'Açılış Fişi'
WHEN 2 THEN 'Tahsil Fişi'
WHEN 3 THEN 'Tediye Fişi'
WHEN 4 THEN 'Mahsup Fişi'
WHEN 5 THEN 'Özel Fiş'
WHEN 6 THEN 'Kur Farkı Fişi'
WHEN 7 THEN 'Kapanış Fişi'
WHEN 8 THEN 'Enflasyon Muh. Fişi'
WHEN 9 THEN 'Konsolidasyon Düzeltme Fişi'
WHEN 10 THEN 'TFRS Düzeltme Fişi'
END
)
RETURN @BMF_EMFLINE_TRCODE_ACIKLAMA
END
GO

LOGO KULLANILMAYAN BIRIMLERI SILME SQL

DELETE FROM LG_210_UNITSETL WHERE LOGICALREF IN (
SELECT LOGICALREF FROM (
SELECT DISTINCT
LOGICALREF,
M.ANAMALZEMEKOD,
M.ANAMZELEMEAD,
M.BIRIMKODU,
M.BIRIMADI,
ISNULL(
(
SELECT DISTINCT
CONVERT(VARCHAR, UOMREF)
FROM LG_210_01_STLINE S
WHERE S.UOMREF = M.LOGICALREF
AND S.LINETYPE = 0
),
'YOK'
) STLINEDAVARMI,
ISNULL(
(
SELECT DISTINCT
CONVERT(VARCHAR, UOMREF)
FROM LG_210_01_ORFLINE S
WHERE S.UOMREF = M.LOGICALREF
AND S.LINETYPE = 0
),
'YOK'
) ORFLINEDAVARMI,
CASE
WHEN ISNULL(
(
SELECT DISTINCT
CONVERT(VARCHAR, UOMREF)
FROM LG_210_01_STLINE S
WHERE S.UOMREF = M.LOGICALREF
AND S.LINETYPE = 0
),
'YOK'
) = 'YOK'
AND ISNULL(
(
SELECT DISTINCT
CONVERT(VARCHAR, UOMREF)
FROM LG_210_01_ORFLINE S
WHERE S.UOMREF = M.LOGICALREF
AND S.LINETYPE = 0
),
'YOK'
) = 'YOK' THEN
'SIL'
ELSE
'SILME'
END SILINEBILIR
FROM
(
SELECT UNITSETL.CODE BIRIMKODU,
UNITSETL.NAME BIRIMADI,
(
SELECT CODE
FROM LG_210_UNITSETF UNITSETF
WHERE UNITSETF.LOGICALREF = UNITSETL.UNITSETREF
) ANAMALZEMEKOD,
(
SELECT UNITSETF.NAME
FROM LG_210_UNITSETF UNITSETF
WHERE UNITSETF.LOGICALREF = UNITSETL.UNITSETREF
) ANAMZELEMEAD,
LOGICALREF,
CODE,
NAME,
UNITSETREF,
LINENR,
MAINUNIT,
CONVFACT1,
CONVFACT2,
WIDTH,
LENGTH,
HEIGHT,
AREA,
VOLUME_,
WEIGHT,
WIDTHREF,
LENGTHREF,
HEIGHTREF,
AREAREF,
VOLUMEREF,
WEIGHTREF,
DIVUNIT,
MEASURECODE,
GLOBALCODE
FROM LG_210_UNITSETL UNITSETL WITH (NOLOCK)
) M ) AS T WHERE T.SILINEBILIR='SIL' )

SQL TAKVIM CALISMA SAATLI

CREATE VIEW [dbo].[BM_TAKVIM] AS
WITH Calender AS (
SELECT CAST('2019-01-01' AS DATETIME) AS dt
UNION ALL
SELECT dt + 1 FROM Calender
WHERE dt + 1 <= CAST('2029-12-31' AS DATETIME)
)
SELECT
CONVERT(VARCHAR, DATEFROMPARTS(YEAR(dt),MONTH(dt) , DAY(DT)), 112) G112,
dt

,CASE
WHEN DATENAME (MONTH,dt)='January' THEN 'Ocak'
WHEN DATENAME (MONTH,dt)='February' THEN 'Şubat'
WHEN DATENAME (MONTH,dt)='March' THEN 'Mart'
WHEN DATENAME (MONTH,dt)='April' THEN 'Nisan'
WHEN DATENAME (MONTH,dt)='May' THEN 'Mayıs'
WHEN DATENAME (MONTH,dt)='June' THEN 'Haziran'
WHEN DATENAME (MONTH,dt)='July' THEN 'Temmuz'
WHEN DATENAME (MONTH,dt)='August' THEN 'Ağustos'
WHEN DATENAME (MONTH,dt)='September' THEN 'Eylül'
WHEN DATENAME (MONTH,dt)='October' THEN 'Ekim'
WHEN DATENAME (MONTH,dt)='November' THEN 'Kasım'
WHEN DATENAME (MONTH,dt)='December' THEN 'Aralık' ELSE '' END Ay
,CASE WHEN DATENAME (WEEKDAY,dt)='Monday' THEN 'Pazartesi'
WHEN DATENAME (WEEKDAY,dt)='Tuesday' THEN 'Salı'
WHEN DATENAME (WEEKDAY,dt)='Wednesday' THEN 'Çarşamba'
WHEN DATENAME (WEEKDAY,dt)='Thursday' THEN 'Perşembe'
WHEN DATENAME (WEEKDAY,dt)='Friday' THEN 'Cuma'
WHEN DATENAME (WEEKDAY,dt)='Saturday' THEN 'Cumartesi'
WHEN DATENAME (WEEKDAY,dt)='Sunday' THEN 'Pazar' ELSE '' END Gün
,Hafta = DATEPART(WEEK, dt) ,
CASE WHEN DATENAME (WEEKDAY,dt)='Saturday' THEN '05:00' WHEN DATENAME (WEEKDAY,dt)='Sunday' THEN '00:00' ELSE '09:00' END NM,
CASE WHEN DATENAME (WEEKDAY,dt)='Saturday' THEN '13:00' WHEN DATENAME (WEEKDAY,dt)='Sunday' THEN '00:00' ELSE '17:00' END MS

FROM Calender

GO

--SELECT * FROM BM_TAKVIM WITH(NOLOCK) OPTION ( MAXRECURSION 0 )

DEVEXPRESS XTRAWAITFORM SPLASH FORM AUTO SIZE

OPEN SPLASH FORM AND CHANGE PROGRESS PANEL AUTOHEIGHT AND AUTOWIDTH TO TRUE

LOGO SQL MALZEME BIRIM DURUMLARI

CREATE VIEW BM_MALZEME_BIRIM_DURUMLARI AS
SELECT DISTINCT LOGICALREF,M.ANAMALZEMEKOD,M.ANAMZELEMEAD, M.BIRIMKODU,M.BIRIMADI,
ISNULL((SELECT DISTINCT CONVERT(VARCHAR,UOMREF) FROM LG_210_01_STLINE S WHERE S.UOMREF=M.LOGICALREF AND S.LINETYPE=0),'YOK') STLINEDAVARMI,
ISNULL((SELECT DISTINCT CONVERT(VARCHAR,UOMREF) FROM LG_210_01_ORFLINE S WHERE S.UOMREF=M.LOGICALREF AND S.LINETYPE=0),'YOK') ORFLINEDAVARMI,
CASE WHEN ISNULL((SELECT DISTINCT CONVERT(VARCHAR,UOMREF) FROM LG_210_01_STLINE S WHERE S.UOMREF=M.LOGICALREF AND S.LINETYPE=0),'YOK') ='YOK'
AND ISNULL((SELECT DISTINCT CONVERT(VARCHAR,UOMREF) FROM LG_210_01_ORFLINE S WHERE S.UOMREF=M.LOGICALREF AND S.LINETYPE=0),'YOK')='YOK' THEN 'SIL' ELSE 'SILME'
END
SILINEBILIR
FROM (SELECT
UNITSETL.CODE BIRIMKODU,
UNITSETL.NAME BIRIMADI,
(SELECT CODE FROM LG_210_UNITSETF UNITSETF WHERE UNITSETF.LOGICALREF = UNITSETL.UNITSETREF) ANAMALZEMEKOD,
(SELECT UNITSETF.NAME FROM LG_210_UNITSETF UNITSETF WHERE UNITSETF.LOGICALREF = UNITSETL.UNITSETREF) ANAMZELEMEAD,
LOGICALREF, CODE, NAME, UNITSETREF, LINENR, MAINUNIT, CONVFACT1, CONVFACT2, WIDTH, LENGTH, HEIGHT, AREA, VOLUME_, WEIGHT, WIDTHREF, LENGTHREF, HEIGHTREF, AREAREF, VOLUMEREF, WEIGHTREF, DIVUNIT, MEASURECODE, GLOBALCODE
FROM
LG_210_UNITSETL UNITSETL WITH(NOLOCK) ) M

LOGO SQL ALT BIRIMLERIYLE ANA BIRIM SETLERI

SET ROWCOUNT 0

SELECT
(SELECT CODE FROM LG_212_UNITSETF UNITSETF WHERE UNITSETF.LOGICALREF = UNITSETL.UNITSETREF) ANAMALZEMEKOD,
(SELECT UNITSETF.NAME FROM LG_212_UNITSETF UNITSETF WHERE UNITSETF.LOGICALREF = UNITSETL.UNITSETREF) ANAMZELEMEAD,
LOGICALREF, CODE, NAME, UNITSETREF, LINENR, MAINUNIT, CONVFACT1, CONVFACT2, WIDTH, LENGTH, HEIGHT, AREA, VOLUME_, WEIGHT, WIDTHREF, LENGTHREF, HEIGHTREF, AREAREF, VOLUMEREF, WEIGHTREF, DIVUNIT, MEASURECODE, GLOBALCODE
FROM
LG_212_UNITSETL UNITSETL WITH(NOLOCK)

ORDER BY
UNITSETREF, LINENR

C# excel dxgridview fast load

//DXGRIDVIEW EXCEL ACMA HIZLI
using DevExpress.XtraGrid;
using DevExpress.XtraGrid.Views.Grid;
using Excel;
using System;
using System.Data;
using System.IO;
using System.Windows.Forms;
//F:\googledrive\programming\csharp içinde iki adet dll var Excel.dll ve ICSharpCode.SharpZipLib.dll referans olarak ekle

public void SET_GC_DATASOURCE(DataTable DT, string GVNAME){
try
{
if (DT == null || DT.Rows.Count <= 0)
throw new Exception("EXCEL VERİLERİ OKUNAMADI.");
gridControl1.DataSource = DT;
gridControl1.RefreshDataSource();
gridControl1.Refresh();
INITIALIZE_GRID(gridControl1, gridView1, new GRIDOPTIONS() { ALLOWSORT = true, CLEARSORTINFO = true, ENABLEGROUPPANELMENU = true, HORIZONTALSCROOLVISIBILITY = true, SHOWGROUPPANEL = true }, GVNAME);
//for (int i = 0; i < COLUMNS.Length / 2; i++)
//{
// _gv_EKSTRE.Columns[i].Caption = (string)COLUMNS[i, 0];
// _gv_EKSTRE.Columns[i].Visible = (bool)COLUMNS[i, 1];
//}

}
catch (Exception E)
{

MessageBox.Show(E.Message, "HATA!", MessageBoxButtons.OK, MessageBoxIcon.Error);

}
}

public string READ_FILE(string title){
try
{
string[] fileformats = new string[] { "xls", "xlsx" };
OpenFileDialog openFileDialogEKSTRE = new OpenFileDialog();
openFileDialogEKSTRE.Title = title;
//openFileDialogEKSTRE.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);//@"C:\Users\BilMark Yazılım\Desktop";//AppDomain.CurrentDomain.BaseDirectory;
string filterText = string.Empty;
for (int i = 0; i < fileformats.Length; i++)
{
filterText += "(*." + fileformats[i] + "*)|*." + fileformats[i];
if (i != fileformats.Length - 1)
filterText += "|";
}
openFileDialogEKSTRE.Filter = "Excel Files|*.xls;*.xlsx;";
//openFileDialogEKSTRE.FileName = "1001.xlsx";
openFileDialogEKSTRE.FilterIndex = 0;
openFileDialogEKSTRE.RestoreDirectory = true;

if (openFileDialogEKSTRE.ShowDialog() == DialogResult.OK)
{
return openFileDialogEKSTRE.FileName;
}
else return "closed";
}
catch (Exception E)
{

return null;
}
}

public DataTable CONVERT_EXCEL_TO_DATATABLE(bool ISXLSX, bool ISFIRSTROWCOLUMNNAME, string PATH, int RETURNEDTABLENR){
FileStream stream = File.Open(PATH, FileMode.Open, FileAccess.Read);
IExcelDataReader excelReader = null;

if (ISXLSX)
excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
else
excelReader = ExcelReaderFactory.CreateBinaryReader(stream);

excelReader.IsFirstRowAsColumnNames = ISFIRSTROWCOLUMNNAME;

DataSet result = excelReader.AsDataSet();

////5. Data Reader methods
//while (excelReader.Read())
//{
// //excelReader.GetInt32(0);
//}

excelReader.Close();

return result.Tables[RETURNEDTABLENR];}

public void INITIALIZE_GRID(GridControl GC, GridView GV, GRIDOPTIONS GO, string XMLLAYOUTNAME){

try
{
RESTORE_GRIDVIEW_LAYOUTS(GV, XMLLAYOUTNAME);
//GV.RestoreLayoutFromXml(AppDomain.CurrentDomain.BaseDirectory + "\\Layouts\\" + XMLLAYOUTNAME + ".xml");
if (GO.CLEARSORTINFO)
GV.SortInfo.Clear();
GV.ClearColumnsFilter();
GV.FocusedRowHandle = 0;
}
catch { }

GV.OptionsBehavior.Editable = GO.EDITABLE;
GV.OptionsBehavior.AllowAddRows = GO.ALLOWADDROW ? DevExpress.Utils.DefaultBoolean.True : DevExpress.Utils.DefaultBoolean.False;
GV.OptionsBehavior.AllowDeleteRows = GO.ALLOWDELETEROW ? DevExpress.Utils.DefaultBoolean.True : DevExpress.Utils.DefaultBoolean.False;

GV.OptionsCustomization.AllowFilter = GO.ALLOWFILTER;
GV.OptionsCustomization.AllowQuickHideColumns = GO.ALLOWQUICKHIDECOLUMNS;
GV.OptionsCustomization.AllowSort = GO.ALLOWSORT;

GV.OptionsMenu.EnableColumnMenu = GO.ENABLECOLUMNMENU;
GV.OptionsMenu.EnableFooterMenu = GO.ENABLEFOOTERMENU;
GV.OptionsMenu.EnableGroupPanelMenu = GO.ENABLEGROUPPANELMENU;

GV.OptionsSelection.MultiSelect = GO.MULTISELECT;
if (GO.MULTISELECTMODECELLSELECT)
GV.OptionsSelection.MultiSelectMode = GridMultiSelectMode.CellSelect;
else if (GO.MULTISELECTMODECHECKBOXSELECT)
GV.OptionsSelection.MultiSelectMode = GridMultiSelectMode.CheckBoxRowSelect;
else if (GO.MULTISELECTMODEWORSELECT)
GV.OptionsSelection.MultiSelectMode = GridMultiSelectMode.RowSelect;

GV.OptionsView.ColumnAutoWidth = GO.COLUMNAUTOWIDTH;
GV.OptionsView.ShowAutoFilterRow = GO.SHOWAUTOFILTERROW;
GV.OptionsView.ShowGroupPanel = GO.SHOWGROUPPANEL;
GV.OptionsView.ShowFilterPanelMode = GO.SHOWFILTERPANELMODE ? DevExpress.XtraGrid.Views.Base.ShowFilterPanelMode.ShowAlways : DevExpress.XtraGrid.Views.Base.ShowFilterPanelMode.Default;}

public void RESTORE_GRIDVIEW_LAYOUTS(GridView GV, string XMLLAYOUTNAME){
try
{
GV.RestoreLayoutFromXml(AppDomain.CurrentDomain.BaseDirectory + "\\Layouts\\" + XMLLAYOUTNAME + ".xml");
}
catch { }}

public class GRIDOPTIONS{
#region OPTIONSBEHAVIOR
public bool EDITABLE { get; set; }
public bool ALLOWADDROW { get; set; }
public bool ALLOWDELETEROW { get; set; }
#endregion

#region OPTIONSCUSTOMIZATION
public bool ALLOWFILTER { get; set; }
public bool ALLOWSORT { get; set; }
public bool ALLOWQUICKHIDECOLUMNS { get; set; }
#endregion

#region OPTIONSMENU
public bool ENABLECOLUMNMENU { get; set; }
public bool ENABLEGROUPPANELMENU { get; set; }
public bool ENABLEFOOTERMENU { get; set; }
#endregion

#region OPTIONSSELECTION
public bool MULTISELECT { get; set; }
public bool MULTISELECTMODECELLSELECT { get; set; }
public bool MULTISELECTMODECHECKBOXSELECT { get; set; }
public bool MULTISELECTMODEWORSELECT { get; set; }
#endregion

#region OPTIONSVIEW
public bool COLUMNAUTOWIDTH { get; set; }
public bool SHOWGROUPPANEL { get; set; }
public bool SHOWAUTOFILTERROW { get; set; }
public bool SHOWFILTERPANELMODE { get; set; }
#endregion

#region BEHAVIOR
public bool HORIZONTALSCROOLVISIBILITY { get; set; }
#endregion

public bool CLEARSORTINFO { get; set; }
public bool CLEARFILTERINFO { get; set; }}

private void simpleButton1_Click(object sender, EventArgs e){
gridControl1.DataSource = null;
gridView1.Columns.Clear();
string FILEPATH = READ_FILE("gridView1");
SET_GC_DATASOURCE(CONVERT_EXCEL_TO_DATATABLE(true, true, FILEPATH, 0), "gridView1");}