Burak Çalışkan

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

  • 12 ay ö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

  • 12 ay ö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

  • 1 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

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

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

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

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

  • 2 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

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

  • 2 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

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

  • 2 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

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

  • 2 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

  • 2 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