MySQL | Burak Çalışkan

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

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

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

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

PHP MySQL Bağlantısı

6 sene önce, Burak Çalışkan tarafından yazılmıştır.
PHP MySQL Bağlantısı için yorumlar kapalı
MySQL,PHP

$host=”192.168.1.10″;
$db=”database_adi”;
$user=”database_kullanici”;
$pass=”db_sifre”;
$conn=@mysql_connect($host,$user,$pass) or die(“Mysql Baglanamadi”);

mysql_query(“INSERT INTO tablo(ad,soyad,zaman) VALUES(‘a’,’b’,now()) ON DUPLICATE KEY UPDATE ad= ‘burak’ , zaman=now() “);

veya

$dbhost=”192.168.1.10″;
$dbuser=”database_kullanici”;
$dbpassword=”db_sifre”;
$dbname=”database_adi”;

$baglan=mysql_connect($dbhost,$dbuser,$dbpassword) or die (“MySQL Baglantisi kurulamadi.”);
mysql_select_db($dbname,$baglan) or die (“Veritabani baglantisi kurulamadi”);
mysql_query(“SET NAMES UTF8”);

MySQL 60 dk önceki kayıtlar

7 sene önce, Burak Çalışkan tarafından yazılmıştır.
MySQL 60 dk önceki kayıtlar için yorumlar kapalı
MySQL

select * from tabloadi where
TIMESTAMPDIFF(MINUTE,zaman,NOW()) > 60 AND islem=1

MySQL Kayıt var ise güncelle

9 sene önce, Burak Çalışkan tarafından yazılmıştır.
MySQL Kayıt var ise güncelle için yorumlar kapalı
MySQL

Daha önce var olan bir kayıt üzerinde UPDATE sorgusu çalıştırmak yerine duplicate kullanabilirsiniz.

INSERT ON DUPLICATE KEY UPDATE `a`=VALUES(`a`), `b`=VALUES(`b`), `c`=VALUES(`c`);

1	INSERT ON DUPLICATE KEY UPDATE `a`=VALUES(`a`), `b`=VALUES(`b`), `c`=VALUES(`c`);