Burak Çalışkan | Yazılım

T-SQL İle Merkez Bankası Döviz Kurları Getirme

2 sene önce, Burak Çalışkan tarafından yazılmıştır.
T-SQL İle Merkez Bankası Döviz Kurları Getirme için yorumlar kapalı
MSSQL


CREATE PROCEDURE SP_MBK_EXCHANGE_RATE
AS
BEGIN
DECLARE @URL AS VARCHAR(250) = 'https://www.tcmb.gov.tr/kurlar/today.xml',
			@OBJ AS INT,
			@RESULT AS INT

	EXEC @RESULT = SP_OACREATE 'MSXML2.XMLHttp', @OBJ OUT 
	EXEC @RESULT = SP_OAMethod @OBJ, 'open', NULL,'GET', @URL, false
	EXEC @RESULT = SP_OAMethod @OBJ,SEND,NULL,''

	CREATE TABLE #TEMPXML (STRXML VARCHAR(MAX))
	INSERT INTO #TEMPXML (STRXML) EXEC @RESULT = SP_OAGetProperty @OBJ,'ResponseXML.xml'

	DECLARE @XML AS XML
	SELECT @XML = STRXML FROM #TEMPXML
	--SELECT @XML
	DROP TABLE #TEMPXML

	DECLARE @HDOC AS INT
	EXEC SP_XML_PREPAREDOCUMENT @HDOC OUTPUT,@XML

	-- Tüm veriler siliniyor
	DELETE FROM dbMEDIUM.dbo.MBK_EXCHANGE_RATE 

	-- Veriler yeniden ekleniyor
	INSERT dbMEDIUM.dbo.MBK_EXCHANGE_RATE
	SELECT * FROM OPENXML(@HDOC,'Tarih_Date/Currency')
	WITH
	(
		Unit VARCHAR(50) 'Unit',
		Isim VARCHAR(50) 'Isim', 
		CurrencyName VARCHAR(50) 'CurrencyName', 
		ForexBuying FLOAT 'ForexBuying', 
		ForexSelling FLOAT 'ForexSelling', 
		BanknoteBuying FLOAT 'BanknoteBuying', 
		BanknoteSelling FLOAT 'BanknoteSelling'
	)

END

MSSQL Command Line Backup

2 sene önce, Burak Çalışkan tarafından yazılmıştır.
MSSQL Command Line Backup için yorumlar kapalı
MSSQL,Yazılım

DECLARE @BackupFile varchar(255), @DB varchar(30), @Description varchar(255), @LogFile varchar(50)
DECLARE @Name varchar(30), @MediaName varchar(30), @BackupDirectory nvarchar(200) 

--Change this variable
SET @BackupDirectory = 'D:\MSSQL_YEDEK\'

--Add a list of all databases you don't want to backup to this.
DECLARE Database_CURSOR CURSOR FOR SELECT name FROM sysdatabases WHERE name <> 'tempdb' AND name <> 'model' AND name <> 'Northwind'
OPEN Database_Cursor
FETCH next FROM Database_CURSOR INTO @DB
WHILE @@fetch_status = 0

    BEGIN
    	SET @Name = @DB + '( Daily BACKUP )'
    	SET @MediaName = @DB + '_Dump' + CONVERT(varchar, CURRENT_TIMESTAMP , 112)
    	SET @BackupFile = @BackupDirectory + + @DB + '_' + 'Full' + '_' + 
    		CONVERT(varchar, CURRENT_TIMESTAMP , 112) + '.bak'
    	SET @Description = 'Normal' + ' BACKUP at ' + CONVERT(varchar, CURRENT_TIMESTAMP) + '.' 

    	IF (SELECT COUNT(*) FROM msdb.dbo.backupset WHERE database_name = @DB) > 0 OR @DB = 'master'
    		BEGIN
    			SET @BackupFile = @BackupDirectory + @DB + '_' + 'Full' + '_' + 
    				CONVERT(varchar, CURRENT_TIMESTAMP , 112) + '.bak'
    			--SET some more pretty stuff for sql server.
    			SET @Description = 'Full' + ' BACKUP at ' + CONVERT(varchar, CURRENT_TIMESTAMP) + '.' 
    		END	
    	ELSE
    		BEGIN
    			SET @BackupFile = @BackupDirectory + @DB + '_' + 'Full' + '_' + 
    				CONVERT(varchar, CURRENT_TIMESTAMP , 112) + '.bak'
    			--SET some more pretty stuff for sql server.
    			SET @Description = 'Full' + ' BACKUP at ' + CONVERT(varchar, CURRENT_TIMESTAMP) + '.' 
    		END
    		BACKUP DATABASE @DB TO DISK = @BackupFile 
    		WITH NAME = @Name, DESCRIPTION = @Description , 
    		MEDIANAME = @MediaName, MEDIADESCRIPTION = @Description , 
    		STATS = 10
    	FETCH next FROM Database_CURSOR INTO @DB
END
CLOSE Database_Cursor
DEALLOCATE Database_Cursor

sqlcmd -S "SERVER-ADI" -U sa -P sifre123 -i "D:\MSSQL_YEDEK\sorgu.sql"

PHP PDO SQLite Database ATTACH

2 sene önce, Burak Çalışkan tarafından yazılmıştır.
PHP PDO SQLite Database ATTACH için yorumlar kapalı
Genel

<?php
error_reporting(E_ALL ^ E_NOTICE); 
echo "<pre>";
try {
$file_db = new PDO('sqlite:/usr/local/var/www/database1.db');

$file_db->setAttribute(PDO::ATTR_ERRMODE,
                        PDO::ERRMODE_EXCEPTION);

$file_db->exec("ATTACH DATABASE '/usr/local/var/www/database2.db' AS db2");

$result = $file_db->query('SQL SORGUSU');

$resultsx = $result->fetchAll(PDO::FETCH_ASSOC);

print_r($resultsx);

$file_db = null;
}
catch(PDOException $e) {
echo $e->getMessage();
}
echo "<pre>";
?>

PHP Centos unixODBC

3 sene önce, Burak Çalışkan tarafından yazılmıştır.
PHP Centos unixODBC için yorumlar kapalı
CentOS,MSSQL,PHP

yum remove unixODBC*

# CentOS 6
curl https://packages.microsoft.com/config/rhel/6/prod.repo &gt; /etc/yum.repos.d/mssql-release.repo


# CentOS 7
curl https://packages.microsoft.com/config/rhel/7/prod.repo &gt; /etc/yum.repos.d/mssql-release.repo


ACCEPT_EULA=Y yum install msodbcsql17 unixODBC unixODBC-devel

yum remove unixODBC*

# CentOS 6

curl https://packages.microsoft.com/config/rhel/6/prod.repo > /etc/yum.repos.d/mssql-release.repo

# CentOS 7

curl https://packages.microsoft.com/config/rhel/7/prod.repo > /etc/yum.repos.d/mssql-release.repo

ACCEPT_EULA=Y yum install msodbcsql17 unixODBC unixODBC-devel

&lt;?php
$serverName = "your server hostname/IP, port";
$connectionOptions = array(
    "database" =&gt; "yourDatabase",
    "uid" =&gt; "yourUsername",
    "pwd" =&gt; "yourPassword"
);

// Establishes the connection
$conn = sqlsrv_connect($serverName, $connectionOptions);
if ($conn === false) {
    die(formatErrors(sqlsrv_errors()));
}

// Select Query
$tsql = "SELECT @@Version AS SQL_VERSION";

// Executes the query
$stmt = sqlsrv_query($conn, $tsql);

// Error handling
if ($stmt === false) {
    die(formatErrors(sqlsrv_errors()));
}
?&gt;

&lt;h1&gt; Results : &lt;/h1&gt;

&lt;?php
while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
    echo $row&#91;'SQL_VERSION'] . PHP_EOL;
}

sqlsrv_free_stmt($stmt);
sqlsrv_close($conn);

function formatErrors($errors)
{
    // Display errors
    echo "Error information: &lt;br/&gt;";
    foreach ($errors as $error) {
        echo "SQLSTATE: ". $error&#91;'SQLSTATE'] . "&lt;br/&gt;";
        echo "Code: ". $error&#91;'code'] . "&lt;br/&gt;";
        echo "Message: ". $error&#91;'message'] . "&lt;br/&gt;";
    }
}
?&gt;

<?php

$serverName = "your server hostname/IP, port";

$connectionOptions = array(

"database" => "yourDatabase",

"uid" => "yourUsername",

"pwd" => "yourPassword"

);

// Establishes the connection

$conn = sqlsrv_connect($serverName, $connectionOptions);

if ($conn === false) {

die(formatErrors(sqlsrv_errors()));

}

// Select Query

$tsql = "SELECT @@Version AS SQL_VERSION";

// Executes the query

$stmt = sqlsrv_query($conn, $tsql);

// Error handling

if ($stmt === false) {

die(formatErrors(sqlsrv_errors()));

}

<h1> Results : </h1>

<?php

while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {

echo $row['SQL_VERSION'] . PHP_EOL;

}

sqlsrv_free_stmt($stmt);

sqlsrv_close($conn);

function formatErrors($errors)

{

// Display errors

echo "Error information: <br/>";

foreach ($errors as $error) {

echo "SQLSTATE: ". $error['SQLSTATE'] . "<br/>";

echo "Code: ". $error['code'] . "<br/>";

echo "Message: ". $error['message'] . "<br/>";

}

Logo Yazılım Satış Faturaları SQL

3 sene önce, Burak Çalışkan tarafından yazılmıştır.
Logo Yazılım Satış Faturaları SQL için yorumlar kapalı
MSSQL

SELECT

ISNULL(CASE WHEN INV.FROMKASA = 1 AND INV.CANCELLED = 1 THEN 'İ' WHEN INV.FROMKASA = 1 THEN 'K' END,''),
CONVERT(NVARCHAR,INV.DATE_,103) 'TARİH',
INV.FICHENO 'FİŞ NO.',
INV.DOCODE 'BELGE NO',
CASE INV.TRCODE WHEN 8 THEN 'TOPTAN SATIŞ FATURASI' WHEN 3 THEN 'TOPTAN SATIŞ İADE FATURASI' END 'TÜRÜ' ,
INV.NETTOTAL 'TUTAR',
CASE INV.TRCURR
WHEN 0 THEN CAST(INV.TRNET AS NVARCHAR) + ' TL'
WHEN 1 THEN CAST(INV.TRNET AS NVARCHAR) + ' $'
WHEN 20 THEN CAST(INV.TRNET AS NVARCHAR) + ' €'
END 'DÖVİZLİ TUTAR',
CPD.NR 'İŞYERİ',
CPW.NR 'AMBAR'

FROM LG_021_01_INVOICE INV
LEFT JOIN LG_021_CLCARD CLC ON CLC.LOGICALREF = INV.CLIENTREF
LEFT JOIN L_CAPIDEPT CPD ON CPD.NR = INV.BRANCH AND CPD.FIRMNR = 5
LEFT JOIN L_CAPIWHOUSE CPW ON CPW.NR = INV.SOURCEINDEX AND CPW.FIRMNR = 5

WHERE GRPCODE = 2

ORDER BY INV.DATE_ ASC

SELECT

ISNULL(CASE WHEN INV.FROMKASA = 1 AND INV.CANCELLED = 1 THEN 'İ' WHEN INV.FROMKASA = 1 THEN 'K' END,''),

CONVERT(NVARCHAR,INV.DATE_,103) 'TARİH',

INV.FICHENO 'FİŞ NO.',

INV.DOCODE 'BELGE NO',

CASE INV.TRCODE WHEN 8 THEN 'TOPTAN SATIŞ FATURASI' WHEN 3 THEN 'TOPTAN SATIŞ İADE FATURASI' END 'TÜRÜ' ,

INV.NETTOTAL 'TUTAR',

CASE INV.TRCURR

WHEN 0 THEN CAST(INV.TRNET AS NVARCHAR) + ' TL'

WHEN 1 THEN CAST(INV.TRNET AS NVARCHAR) + ' $'

WHEN 20 THEN CAST(INV.TRNET AS NVARCHAR) + ' €'

END 'DÖVİZLİ TUTAR',

CPD.NR 'İŞYERİ',

CPW.NR 'AMBAR'

FROM LG_021_01_INVOICE INV

LEFT JOIN LG_021_CLCARD CLC ON CLC.LOGICALREF = INV.CLIENTREF

LEFT JOIN L_CAPIDEPT CPD ON CPD.NR = INV.BRANCH AND CPD.FIRMNR = 5

LEFT JOIN L_CAPIWHOUSE CPW ON CPW.NR = INV.SOURCEINDEX AND CPW.FIRMNR = 5

WHERE GRPCODE = 2

ORDER BY INV.DATE_ ASC

Logo Yazılım Hangi Ürün Kime Satılmış Raporu

3 sene önce, Burak Çalışkan tarafından yazılmıştır.
Logo Yazılım Hangi Ürün Kime Satılmış Raporu için yorumlar kapalı
MySQL

SELECT
CONVERT(nvarchar(20), INVOICE.DATE_,104) as Tarih,
--INVOICE.DATE_ AS &#91;Tarih],
CLCARD.CODE as &#91;Cari Kod],
CLCARD.DEFINITION_ as &#91;Cari Ad],
INVOICE.SERIALCODE &#91;Seri No],
INVOICE.FICHENO &#91;Fiş No],
COALESCE(
CASE STLINE.UINFO1
WHEN 0 THEN STLINE.AMOUNT
ELSE STLINE.AMOUNT*STLINE.UINFO2/STLINE.UINFO1
END, STLINE.AMOUNT/1000) &#91;Miktar],
UNT.CODE &#91;Birim],
CASE WHEN STLINE.LINETYPE=4 THEN SRV.CODE ELSE INV.CODE END AS &#91;Malzeme Kod],
CASE WHEN STLINE.LINETYPE=4 THEN SRV.DEFINITION_ ELSE INV.NAME END AS &#91;Malzeme Ad],
CASE WHEN STLINE.LINETYPE=4 THEN '' ELSE INV.STGRPCODE END AS &#91;Malzeme Grup],
CASE WHEN STLINE.LINETYPE=4 THEN SRV.SPECODE ELSE INV.SPECODE END AS &#91;Malzeme Özel Kod],
STLINE.PRICE &#91;Birim Fiyat],
STLINE.VATMATRAH &#91;KDV Matrah],
STLINE.VATAMNT &#91;KDV Tutar],
STLINE.VAT &#91;KDV %],
(CASE
WHEN INVOICE.TRCODE=1 THEN 'Satınalma Faturası'
WHEN INVOICE.TRCODE=2 THEN 'Perakende Satış İade Faturası'
WHEN INVOICE.TRCODE=3 THEN 'Toptan Satış İade Faturası'
WHEN INVOICE.TRCODE=4 THEN 'Alınan Hizmet Faturası'
WHEN INVOICE.TRCODE=5 THEN 'Alınan Proforma Faturası'
WHEN INVOICE.TRCODE=6 THEN 'Satınalma İade Faturası'
WHEN INVOICE.TRCODE=7 THEN 'Perakende Satış Faturası'
WHEN INVOICE.TRCODE=8 THEN 'Toptan Satış Faturası'
WHEN INVOICE.TRCODE=9 THEN 'Verilen Hizmet Faturası'
WHEN INVOICE.TRCODE=10 THEN 'Verilen Proforma Faturası'
WHEN INVOICE.TRCODE=12 THEN 'Alınan Vade Farkı Faturası'
WHEN INVOICE.TRCODE=13 THEN 'Satınalma Fiyat Farkı Faturası'
WHEN INVOICE.TRCODE=14 THEN 'Satış Fiyat Farkı Faturası'
WHEN INVOICE.TRCODE=26 THEN 'Müstahsil Makbuzu' END
) as &#91;Fatura Tipi],
CASE WHEN CLCARD.ISPERSCOMP=1 THEN CLCARD.TCKNO ELSE CLCARD.TAXNR END AS &#91;Cari VNO],
CLCARD.TAXOFFICE as &#91;Cari VDaire],
EMUH.CODE as &#91;Muhasebe Hesap Planı Kod],
EMUH.DEFINITION_ as &#91;Muhasebe Hesap Planı Ad],
RTRIM(INVOICE.GENEXP1+' '+INVOICE.GENEXP2+' '+INVOICE.GENEXP3+' '+INVOICE.GENEXP4) AS &#91;Açıklama]
FROM LG_021_01_INVOICE AS INVOICE WITH (NOLOCK)
RIGHT OUTER JOIN LG_021_01_STLINE AS STLINE ON INVOICE.LOGICALREF = STLINE.INVOICEREF
RIGHT OUTER JOIN LG_021_ITEMS INV ON INV.LOGICALREF=STLINE.STOCKREF
LEFT JOIN LG_021_SRVCARD SRV ON SRV.LOGICALREF=STLINE.STOCKREF
RIGHT OUTER JOIN LG_021_CLCARD CLCARD ON CLCARD.LOGICALREF=INVOICE.CLIENTREF
LEFT JOIN LG_021_EMUHACC EMUH ON EMUH.LOGICALREF=STLINE.ACCOUNTREF
RIGHT OUTER JOIN LG_021_UNITSETL UNT ON UNT.UNITSETREF=INV.UNITSETREF AND LINENR=1
WHERE(INVOICE.TRCODE IN(2,3,7,8,9,10,14))
--AND INVOICE.DATE_ >= '2020-01-01 00:00:00.000'
--AND INVOICE.DATE_ &lt;= '2020-12-31 23:59:59.999'
ORDER BY INVOICE.DATE_,
INVOICE.SOURCEINDEX,
INVOICE.FICHENO

SELECT

CONVERT(nvarchar(20), INVOICE.DATE_,104) as Tarih,

--INVOICE.DATE_ AS [Tarih],

CLCARD.CODE as [Cari Kod],

CLCARD.DEFINITION_ as [Cari Ad],

INVOICE.SERIALCODE [Seri No],

INVOICE.FICHENO [Fiş No],

COALESCE(

CASE STLINE.UINFO1

WHEN 0 THEN STLINE.AMOUNT

ELSE STLINE.AMOUNT*STLINE.UINFO2/STLINE.UINFO1

END, STLINE.AMOUNT/1000) [Miktar],

UNT.CODE [Birim],

CASE WHEN STLINE.LINETYPE=4 THEN SRV.CODE ELSE INV.CODE END AS [Malzeme Kod],

CASE WHEN STLINE.LINETYPE=4 THEN SRV.DEFINITION_ ELSE INV.NAME END AS [Malzeme Ad],

CASE WHEN STLINE.LINETYPE=4 THEN '' ELSE INV.STGRPCODE END AS [Malzeme Grup],

CASE WHEN STLINE.LINETYPE=4 THEN SRV.SPECODE ELSE INV.SPECODE END AS [Malzeme Özel Kod],

STLINE.PRICE [Birim Fiyat],

STLINE.VATMATRAH [KDV Matrah],

STLINE.VATAMNT [KDV Tutar],

STLINE.VAT [KDV %],

(CASE

WHEN INVOICE.TRCODE=1 THEN 'Satınalma Faturası'

WHEN INVOICE.TRCODE=2 THEN 'Perakende Satış İade Faturası'

WHEN INVOICE.TRCODE=3 THEN 'Toptan Satış İade Faturası'

WHEN INVOICE.TRCODE=4 THEN 'Alınan Hizmet Faturası'

WHEN INVOICE.TRCODE=5 THEN 'Alınan Proforma Faturası'

WHEN INVOICE.TRCODE=6 THEN 'Satınalma İade Faturası'

WHEN INVOICE.TRCODE=7 THEN 'Perakende Satış Faturası'

WHEN INVOICE.TRCODE=8 THEN 'Toptan Satış Faturası'

WHEN INVOICE.TRCODE=9 THEN 'Verilen Hizmet Faturası'

WHEN INVOICE.TRCODE=10 THEN 'Verilen Proforma Faturası'

WHEN INVOICE.TRCODE=12 THEN 'Alınan Vade Farkı Faturası'

WHEN INVOICE.TRCODE=13 THEN 'Satınalma Fiyat Farkı Faturası'

WHEN INVOICE.TRCODE=14 THEN 'Satış Fiyat Farkı Faturası'

WHEN INVOICE.TRCODE=26 THEN 'Müstahsil Makbuzu' END

) as [Fatura Tipi],

CASE WHEN CLCARD.ISPERSCOMP=1 THEN CLCARD.TCKNO ELSE CLCARD.TAXNR END AS [Cari VNO],

CLCARD.TAXOFFICE as [Cari VDaire],

EMUH.CODE as [Muhasebe Hesap Planı Kod],

EMUH.DEFINITION_ as [Muhasebe Hesap Planı Ad],

RTRIM(INVOICE.GENEXP1+' '+INVOICE.GENEXP2+' '+INVOICE.GENEXP3+' '+INVOICE.GENEXP4) AS [Açıklama]

FROM LG_021_01_INVOICE AS INVOICE WITH (NOLOCK)

RIGHT OUTER JOIN LG_021_01_STLINE AS STLINE ON INVOICE.LOGICALREF = STLINE.INVOICEREF

RIGHT OUTER JOIN LG_021_ITEMS INV ON INV.LOGICALREF=STLINE.STOCKREF

LEFT JOIN LG_021_SRVCARD SRV ON SRV.LOGICALREF=STLINE.STOCKREF

RIGHT OUTER JOIN LG_021_CLCARD CLCARD ON CLCARD.LOGICALREF=INVOICE.CLIENTREF

LEFT JOIN LG_021_EMUHACC EMUH ON EMUH.LOGICALREF=STLINE.ACCOUNTREF

RIGHT OUTER JOIN LG_021_UNITSETL UNT ON UNT.UNITSETREF=INV.UNITSETREF AND LINENR=1

WHERE(INVOICE.TRCODE IN(2,3,7,8,9,10,14))

--AND INVOICE.DATE_ >= '2020-01-01 00:00:00.000'

--AND INVOICE.DATE_ <= '2020-12-31 23:59:59.999'

ORDER BY INVOICE.DATE_,

INVOICE.SOURCEINDEX,

INVOICE.FICHENO

Logo Yazılım Tüm ürünlerlerin Fiyat ve Stok Bilgisi

3 sene önce, Burak Çalışkan tarafından yazılmıştır.
Logo Yazılım Tüm ürünlerlerin Fiyat ve Stok Bilgisi için yorumlar kapalı
MySQL

SELECT
I.CODE AS 'URUN_KODU' ,
I.NAME AS 'URUN_ADI',
I.STGRPCODE AS 'GRUP KODU',
I.SPECODE AS 'ÖZEL KODU',
I.LOGICALREF ,
ISNULL(PR.PRICE,0)AS FIYAT,
ISNULL(PR.CURRENCY,0) AS DOVIZ,
SUM ((CASE S.TRCODE WHEN 1 THEN S.AMOUNT*UINFO2 ELSE 0 END )+ (CASE S.TRCODE WHEN 13 THEN S.AMOUNT*UINFO2 ELSE 0 END)+
(CASE F.TRCODE WHEN 3 THEN S.AMOUNT*UINFO2 ELSE 0 END )+(CASE S.TRCODE WHEN 14 THEN S.AMOUNT*UINFO2 ELSE 0 END)+
(CASE S.TRCODE WHEN 50 THEN S.AMOUNT*UINFO2 ELSE 0 END)) +
SUM((CASE F.TRCODE WHEN 8 THEN S.AMOUNT*UINFO2 ELSE 0 END)+(CASE S.TRCODE WHEN 6 THEN S.AMOUNT*UINFO2 ELSE 0 END)+
(CASE S.TRCODE WHEN 12 THEN S.AMOUNT*UINFO2 ELSE 0 END)+(CASE S.TRCODE WHEN 11 THEN S.AMOUNT*UINFO2 ELSE 0 END)+
(CASE S.TRCODE WHEN 51 THEN S.AMOUNT*UINFO2 ELSE 0 END)+(CASE S.TRCODE WHEN 20 THEN S.AMOUNT*UINFO2 ELSE 0 END)) AS 'STOK'
from LG_021_ITEMS AS I LEFT JOIN
LG_021_01_STLINE AS S ON I.LOGICALREF=S.STOCKREF AND I.ACTIVE='0' and S.CANCELLED='0' LEFT JOIN
LG_021_01_INVOICE AS F ON S.INVOICEREF=F.LOGICALREF LEFT JOIN
LG_021_PRCLIST AS PR ON I.LOGICALREF=PR.CARDREF and PR.PTYPE=2 LEFT JOIN
LG_021_01_STINVTOT AS ST ON I.LOGICALREF=ST.STOCKREF
GROUP BY I.CODE,I.NAME,I.STGRPCODE,I.SPECODE,PR.PRICE,PR.CURRENCY,PR.PRIORITY,I.LOGICALREF,PR.PRICE
ORDER BY URUN_KODU ASC

SELECT

I.CODE AS 'URUN_KODU' ,

I.NAME AS 'URUN_ADI',

I.STGRPCODE AS 'GRUP KODU',

I.SPECODE AS 'ÖZEL KODU',

I.LOGICALREF ,

ISNULL(PR.PRICE,0)AS FIYAT,

ISNULL(PR.CURRENCY,0) AS DOVIZ,

SUM ((CASE S.TRCODE WHEN 1 THEN S.AMOUNT*UINFO2 ELSE 0 END )+ (CASE S.TRCODE WHEN 13 THEN S.AMOUNT*UINFO2 ELSE 0 END)+

(CASE F.TRCODE WHEN 3 THEN S.AMOUNT*UINFO2 ELSE 0 END )+(CASE S.TRCODE WHEN 14 THEN S.AMOUNT*UINFO2 ELSE 0 END)+

(CASE S.TRCODE WHEN 50 THEN S.AMOUNT*UINFO2 ELSE 0 END)) +

SUM((CASE F.TRCODE WHEN 8 THEN S.AMOUNT*UINFO2 ELSE 0 END)+(CASE S.TRCODE WHEN 6 THEN S.AMOUNT*UINFO2 ELSE 0 END)+

(CASE S.TRCODE WHEN 12 THEN S.AMOUNT*UINFO2 ELSE 0 END)+(CASE S.TRCODE WHEN 11 THEN S.AMOUNT*UINFO2 ELSE 0 END)+

(CASE S.TRCODE WHEN 51 THEN S.AMOUNT*UINFO2 ELSE 0 END)+(CASE S.TRCODE WHEN 20 THEN S.AMOUNT*UINFO2 ELSE 0 END)) AS 'STOK'

from LG_021_ITEMS AS I LEFT JOIN

LG_021_01_STLINE AS S ON I.LOGICALREF=S.STOCKREF AND I.ACTIVE='0' and S.CANCELLED='0' LEFT JOIN

LG_021_01_INVOICE AS F ON S.INVOICEREF=F.LOGICALREF LEFT JOIN

LG_021_PRCLIST AS PR ON I.LOGICALREF=PR.CARDREF and PR.PTYPE=2 LEFT JOIN

LG_021_01_STINVTOT AS ST ON I.LOGICALREF=ST.STOCKREF

GROUP BY I.CODE,I.NAME,I.STGRPCODE,I.SPECODE,PR.PRICE,PR.CURRENCY,PR.PRIORITY,I.LOGICALREF,PR.PRICE

ORDER BY URUN_KODU ASC

Logo Yazılım Hangi ürün kaç adet kaç TL ye satılmış

3 sene önce, Burak Çalışkan tarafından yazılmıştır.
Logo Yazılım Hangi ürün kaç adet kaç TL ye satılmış için yorumlar kapalı
MSSQL

SELECT * FROM (SELECT
I.CARDTYPE "Malzeme Türü",
I.CODE "Malzeme Kodu",
I.NAME "Malzeme Adı",
I.SPECODE "Malzeme Özel Kodu",
I.CYPHCODE "Malzeme Yetki Kodu",
U.CODE "Birim Seti ",
T.CODE "Birim Kodu",
SUM(SIGN(L.IOCODE-1)L.AMOUNT) "Satış Miktarı", SUM(SIGN(4-L.IOCODE)L.AMOUNT) "İade Miktarı",
SUM(SIGN(L.IOCODE-1)*L.TOTAL) "Satış Tutarı"
FROM
LG_021_01_STLINE L
LEFT OUTER JOIN LG_021_01_STFICHE F ON L.STFICHEREF=F.LOGICALREF
LEFT OUTER JOIN LG_021_ITEMS I ON L.STOCKREF=I.LOGICALREF AND L.LINETYPE NOT IN (1,2,3,4)
LEFT OUTER JOIN LG_021_UNITSETF U ON I.UNITSETREF=U.LOGICALREF
LEFT OUTER JOIN LG_021_UNITSETL T ON U.LOGICALREF=T.UNITSETREF
WHERE
I.LOGICALREF IS NOT NULL
AND F.GRPCODE=2
AND T.LINENR=1
GROUP BY
I.CARDTYPE,
I.CODE,
I.NAME,
U.CODE,
T.CODE,
I.SPECODE,
I.CYPHCODE
) AS DYNMQRY
ORDER BY DYNMQRY.&#91;Malzeme Kodu] ASC, DYNMQRY.&#91;Malzeme Adı] ASC

SELECT * FROM (SELECT

I.CARDTYPE "Malzeme Türü",

I.CODE "Malzeme Kodu",

I.NAME "Malzeme Adı",

I.SPECODE "Malzeme Özel Kodu",

I.CYPHCODE "Malzeme Yetki Kodu",

U.CODE "Birim Seti ",

T.CODE "Birim Kodu",

SUM(SIGN(L.IOCODE-1)L.AMOUNT) "Satış Miktarı", SUM(SIGN(4-L.IOCODE)L.AMOUNT) "İade Miktarı",

SUM(SIGN(L.IOCODE-1)*L.TOTAL) "Satış Tutarı"

FROM

LG_021_01_STLINE L

LEFT OUTER JOIN LG_021_01_STFICHE F ON L.STFICHEREF=F.LOGICALREF

LEFT OUTER JOIN LG_021_ITEMS I ON L.STOCKREF=I.LOGICALREF AND L.LINETYPE NOT IN (1,2,3,4)

LEFT OUTER JOIN LG_021_UNITSETF U ON I.UNITSETREF=U.LOGICALREF

LEFT OUTER JOIN LG_021_UNITSETL T ON U.LOGICALREF=T.UNITSETREF

WHERE

I.LOGICALREF IS NOT NULL

AND F.GRPCODE=2

AND T.LINENR=1

GROUP BY

I.CARDTYPE,

I.CODE,

I.NAME,

U.CODE,

T.CODE,

I.SPECODE,

I.CYPHCODE

) AS DYNMQRY

ORDER BY DYNMQRY.[Malzeme Kodu] ASC, DYNMQRY.[Malzeme Adı] ASC

Logo Yazılım Satın Alma Faturaları Detaylı Rapor

3 sene önce, Burak Çalışkan tarafından yazılmıştır.
Logo Yazılım Satın Alma Faturaları Detaylı Rapor için yorumlar kapalı
MSSQL

SELECT
CONVERT(nvarchar(20), INVOICE.DATE_,104) as Tarih,
–INVOICE.DATE_ AS [Tarih],
CLCARD.CODE as [Cari Kod],
CLCARD.DEFINITION_ as [Cari Ad],
INVOICE.SERIALCODE [Seri No],
INVOICE.FICHENO [Fiş No],
COALESCE(
CASE STLINE.UINFO1
WHEN 0 THEN STLINE.AMOUNT
ELSE STLINE.AMOUNT*STLINE.UINFO2/STLINE.UINFO1
END, STLINE.AMOUNT/1000) [Miktar],
UNT.CODE [Birim],
CASE WHEN STLINE.LINETYPE=4 THEN SRV.CODE ELSE INV.CODE END AS [Malzeme Kod],
CASE WHEN STLINE.LINETYPE=4 THEN SRV.DEFINITION_ ELSE INV.NAME END AS [Malzeme Ad],
CASE WHEN STLINE.LINETYPE=4 THEN ” ELSE INV.STGRPCODE END AS [Malzeme Grup],
CASE WHEN STLINE.LINETYPE=4 THEN SRV.SPECODE ELSE INV.SPECODE END AS [Malzeme Özel Kod],
STLINE.PRICE [Birim Fiyat],
STLINE.VATMATRAH [KDV Matrah],
STLINE.VATAMNT [KDV Tutar],
STLINE.VAT [KDV %],
(CASE WHEN INVOICE.TRCODE=1 THEN ‘Satınalma Faturası’
WHEN INVOICE.TRCODE=4 THEN ‘Alınan Hizmet Faturası’
WHEN INVOICE.TRCODE=5 THEN ‘Alınan Proforma Fatura’
WHEN INVOICE.TRCODE=6 THEN ‘Satınalma İade Faturası’
WHEN INVOICE.TRCODE=12 THEN ‘Alınan Vade Farkı Faturası’
WHEN INVOICE.TRCODE=13THEN ‘Satınalma Fiyat Farkı Faturası’
WHEN INVOICE.TRCODE=26 THEN ‘Müstahsil Makbuzu’ END
) as [Fatura Tipi],
CASE WHEN CLCARD.ISPERSCOMP=1 THEN CLCARD.TCKNO ELSE CLCARD.TAXNR END AS [Cari VNO],
CLCARD.TAXOFFICE as [Cari VDaire],
EMUH.CODE as [Muhasebe Hesap Planı Kod],
EMUH.DEFINITION_ as [Muhasebe Hesap Planı Ad],
RTRIM(INVOICE.GENEXP1+’ ‘+INVOICE.GENEXP2+’ ‘+INVOICE.GENEXP3+’ ‘+INVOICE.GENEXP4) AS [Açıklama]
FROM LG_171_01_INVOICE AS INVOICE WITH (NOLOCK)
RIGHT OUTER JOIN LG_171_01_STLINE AS STLINE ON INVOICE.LOGICALREF = STLINE.INVOICEREF
RIGHT OUTER JOIN LG_171_ITEMS INV ON INV.LOGICALREF=STLINE.STOCKREF
LEFT JOIN LG_171_SRVCARD SRV ON SRV.LOGICALREF=STLINE.STOCKREF
RIGHT OUTER JOIN LG_171_CLCARD CLCARD ON CLCARD.LOGICALREF=INVOICE.CLIENTREF
LEFT JOIN LG_171_EMUHACC EMUH ON EMUH.LOGICALREF=STLINE.ACCOUNTREF
RIGHT OUTER JOIN LG_171_UNITSETL UNT ON UNT.UNITSETREF=INV.UNITSETREF AND LINENR=1
WHERE(INVOICE.TRCODE IN(1,4,5,6,12,13,26))
–AND INVOICE.DATE_ >= ‘2020-01-01 00:00:00.000’
–AND INVOICE.DATE_ <= ‘2020-12-31 23:59:59.999’
ORDER BY INVOICE.DATE_,
INVOICE.SOURCEINDEX,
INVOICE.FICHENO

Mikrotik Load Balancer PCC

3 sene önce, Burak Çalışkan tarafından yazılmıştır.
Mikrotik Load Balancer PCC için yorumlar kapalı
Mikrotik

Mikrotik Yapılandırması
Router bağlantı noktalarına IP adresi vererek ve yönetilmesi daha kolay olan bağlantı noktalarını yeniden adlandırarak başlıyoruz..
/ip address
add address=192.168.0.1/24 network=192.168.0.0 broadcast=192.168.0.255 interface=LAN
add address=192.168.1.2/24 network=192.168.1.0 broadcast=192.168.1.255 interface=WAN1
add address=192.168.2.2/24 network=192.168.2.0 broadcast=192.168.2.255 interface=WAN2
add address=192.168.3.2/24 network=192.168.3.0 broadcast=192.168.3.255 interface=WAN3
add address=192.168.4.2/24 network=192.168.4.0 broadcast=192.168.4.255 interface=WAN4

/ip firewall mangle
add chain=input in-interface=WAN1 action=mark-connection new-connection-mark=WAN1_conn
add chain=input in-interface=WAN2 action=mark-connection new-connection-mark=WAN2_conn
add chain=input in-interface=WAN3 action=mark-connection new-connection-mark=WAN3_conn
add chain=input in-interface=WAN4 action=mark-connection new-connection-mark=WAN4_conn

add chain=output connection-mark=WAN1_conn action=mark-routing new-routing-mark=to_WAN1
add chain=output connection-mark=WAN2_conn action=mark-routing new-routing-mark=to_WAN2
add chain=output connection-mark=WAN3_conn action=mark-routing new-routing-mark=to_WAN3
add chain=output connection-mark=WAN4_conn action=mark-routing new-routing-mark=to_WAN4

add chain=prerouting dst-address=192.168.1.0/24 action=accept in-interface=LAN
add chain=prerouting dst-address=192.168.2.0/24 action=accept in-interface=LAN
add chain=prerouting dst-address=192.168.3.0/24 action=accept in-interface=LAN
add chain=prerouting dst-address=192.168.4.0/24 action=accept in-interface=LAN

add chain=prerouting dst-address-type=!local in-interface=LAN per-connection-classifier=both-addresses-and-ports:4/0 action=mark-connection new-connection-mark=WAN1_conn passthrough=yes
add chain=prerouting dst-address-type=!local in-interface=LAN per-connection-classifier=both-addresses-and-ports:4/1 action=mark-connection new-connection-mark=WAN2_conn passthrough=yes
add chain=prerouting dst-address-type=!local in-interface=LAN per-connection-classifier=both-addresses-and-ports:4/2 action=mark-connection new-connection-mark=WAN3_conn passthrough=yes
add chain=prerouting dst-address-type=!local in-interface=LAN per-connection-classifier=both-addresses-and-ports:4/3 action=mark-connection new-connection-mark=WAN4_conn passthrough=yes
add chain=prerouting connection-mark=WAN1_conn in-interface=LAN action=mark-routing new-routing-mark=to_WAN1
add chain=prerouting connection-mark=WAN2_conn in-interface=LAN action=mark-routing new-routing-mark=to_WAN2
add chain=prerouting connection-mark=WAN3_conn in-interface=LAN action=mark-routing new-routing-mark=to_WAN3
add chain=prerouting connection-mark=WAN4_conn in-interface=LAN action=mark-routing new-routing-mark=to_WAN4

/ip route
add dst-address=0.0.0.0/0 gateway=192.168.1.1 routing-mark=to_WAN1 check-gateway=ping
add dst-address=0.0.0.0/0 gateway=192.168.2.1 routing-mark=to_WAN2 check-gateway=ping
add dst-address=0.0.0.0/0 gateway=192.168.3.1 routing-mark=to_WAN3 check-gateway=ping
add dst-address=0.0.0.0/0 gateway=192.168.4.1 routing-mark=to_WAN4 check-gateway=ping
add dst-address=0.0.0.0/0 gateway=192.168.1.1 distance=1 check-gateway=ping
add dst-address=0.0.0.0/0 gateway=192.168.2.1 distance=2 check-gateway=ping
add dst-address=0.0.0.0/0 gateway=192.168.3.1 distance=3 check-gateway=ping
add dst-address=0.0.0.0/0 gateway=192.168.4.1 distance=4 check-gateway=ping

/ip firewall nat
add chain=srcnat out-interface=WAN1 action=masquerade
add chain=srcnat out-interface=WAN2 action=masquerade
add chain=srcnat out-interface=WAN3 action=masquerade
add chain=srcnat out-interface=WAN4 action=masquerade

Lastly we need to add the DNS server that we can resolve hostnames
/ip dns set allow-remote-requests=yes cache-max-ttl=1w cache-size=5000KiB max-udp-packet-size=512 servers= 1.1.1.1,8.8.8.8

12 3 ›»