WITH ItemHierarcyh (STCREF, MAINCREF, CARDTYPE)
AS (
SELECT STCREF, MAINCREF, CARDTYPE
FROM LG_123_STCOMPLN
WHERE MAINCREF = (SELECT I.LOGICALREF FROM LG_123_ITEMS I WHERE I.CODE='RECETE0016')
UNION ALL
SELECT S.STCREF, S.MAINCREF, S.CARDTYPE
FROM LG_123_STCOMPLN S
JOIN ItemHierarcyh PH ON S.MAINCREF = PH.STCREF
)
SELECT
STCREF,
AltMalzeme=(select I.CODE+'-'+I.NAME from LG_123_ITEMS I WHERE I.LOGICALREF =STCREF),
MAINCREF,
AnaMalzeme=(select I.CODE+'-'+I.NAME from LG_123_ITEMS I WHERE I.LOGICALREF =MAINCREF),
CARDTYPE
FROM ItemHierarcyh --where CARDTYPE in (11,12)
OPTION (MAXRECURSION 0)
C# TCMBDEN KURLARI GETİR(ESKİLER DAHİL)
public class Currency
{
public string Tarih { get; set; }
public string Kod { get; set; }
public string CurrencyCode { get; set; }
public string CrossOrder { get; set; }
public string Unit { get; set; }
public string Isim { get; set; }
public string CurrencyName { get; set; }
public double ForexBuying { get; set; }
public double ForexSelling { get; set; }
public double BanknoteBuying { get; set; }
public double BanknoteSelling { get; set; }
public double CrossRateUSD { get; set; }
public double CrossRateOther { get; set; }
}
public static List<Currency> GetCurrencies(DateTime Tarih)
{
if (Tarih.DayOfWeek == DayOfWeek.Saturday || Tarih.DayOfWeek == DayOfWeek.Sunday)
return null;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
List<Currency> currenciesL = new List<Currency>();
string yearmonth = Tarih.ToString("yyyyMM");
string monthyear = Tarih.ToString("MMyyyy");
string day = Tarih.ToString("dd");
string url = "";
if (Tarih == DateTime.Now.Date)
url = "https://www.tcmb.gov.tr/kurlar/today.xml";
else url = "https://www.tcmb.gov.tr/kurlar/" + yearmonth + "/" + day + monthyear + ".xml";
System.Net.WebRequest request = System.Net.WebRequest.Create(url);
System.Net.WebResponse response = request.GetResponse();
System.IO.StreamReader reader = new System.IO.StreamReader(response.GetResponseStream());
string xml = reader.ReadToEnd();
reader.Close();
response.Close();
System.Xml.XmlDocument doc = new System.Xml.XmlDocument();
doc.LoadXml(xml);
System.Xml.XmlNodeList nodes = doc.SelectNodes("Tarih_Date/Currency");
foreach (System.Xml.XmlNode node in nodes)
{
Currency currency = new Currency();
currency.Tarih = Tarih.ToString("dd.MM.yyyy");
currency.Kod = node.Attributes["Kod"].Value;
currency.CurrencyCode = node.Attributes["CurrencyCode"].Value;
currency.CrossOrder = node.Attributes["CrossOrder"].Value;
currency.Unit = node.SelectSingleNode("Unit").InnerText;
currency.Isim = node.SelectSingleNode("Isim").InnerText;
currency.CurrencyName = node.SelectSingleNode("CurrencyName").InnerText;
try { currency.ForexBuying = Convert.ToDouble(node.SelectSingleNode("ForexBuying").InnerText); } catch { }
try { currency.ForexSelling = Convert.ToDouble(node.SelectSingleNode("ForexSelling").InnerText); } catch { }
try { currency.BanknoteBuying = Convert.ToDouble(node.SelectSingleNode("BanknoteBuying").InnerText); } catch { }
try { currency.BanknoteSelling = Convert.ToDouble(node.SelectSingleNode("BanknoteSelling").InnerText); } catch { }
try { currency.CrossRateUSD = Convert.ToDouble(node.SelectSingleNode("CrossRateUSD").InnerText); } catch { }
try { currency.CrossRateOther = Convert.ToDouble(node.SelectSingleNode("CrossRateOther").InnerText); } catch { }
currenciesL.Add(currency);
}
return currenciesL;
}
//Form içinde örnek kullanım:
private void sb_Currencies_Click(object sender, EventArgs e)
{
//if datetime is saturday or sunday
if (de_CurrencyDate.DateTime.DayOfWeek == DayOfWeek.Saturday || de_CurrencyDate.DateTime.DayOfWeek == DayOfWeek.Sunday)
{
MessageBox.Show("Hafta Sonu Para Birimleri Alınamaz", "HATA", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
List<Methods.Currency> CURRENCIES = Methods.GetCurrencies(de_CurrencyDate.DateTime);
te_Usd.Text = CURRENCIES.Where(x => x.CurrencyCode == "USD").FirstOrDefault().ForexSelling.ToString();
te_Eur.Text = CURRENCIES.Where(x => x.CurrencyCode == "EUR").FirstOrDefault().ForexSelling.ToString();
te_Gbp.Text = CURRENCIES.Where(x => x.CurrencyCode == "GBP").FirstOrDefault().ForexSelling.ToString();
te_EurUsdParite.Text = CURRENCIES.Where(x => x.CurrencyCode == "EUR").FirstOrDefault().CrossRateOther.ToString();
}
LOGO ERP MAMUL RECETE
select
Mamul_ItemRef_= t.Mamul_ItemRef,
t.Mamul_Kodu,
t.Mamul_Aciklamasi,
t.Mamul_Birim,
AltMalzeme_ReceteRef_= isnull(t.AltMalzeme2_ReceteRef,t.AltMalzeme_ReceteRef),
AltMalzeme_ItemRef_= isnull(t.AltMalzeme2_ItemRef,t.AltMalzeme_ItemRef),
AltMalzeme_Kodu= isnull(t.AltMalzeme2_Kodu,t.AltMalzeme_Kodu),
AltMalzeme_Aciklamasi= isnull(t.AltMalzeme2_Aciklamasi,t.AltMalzeme_Aciklamasi),
AltMalzeme_Birim= isnull(t.AltMalzeme2_Birim,t.AltMalzeme_Birim),
AltMalzeme_ReceteMiktar_= isnull(t.AltMalzeme2_ReceteMiktar,t.AltMalzeme_ReceteMiktar),
AltMalzeme_CARDTYPE_= isnull(t.AltMalzeme2_CARDTYPE,t.AltMalzeme_CARDTYPE),
HesaplananMiktar= CASE
WHEN t.AltMalzeme2_ReceteMiktar IS NULL THEN t.AltMalzeme_ReceteMiktar
ELSE
ISNULL(CAST ((t.AltMalzeme_ReceteMiktar/NULLIF(AltMalzeme_Miktar,0.000))*t.AltMalzeme2_ReceteMiktar AS NUMERIC(10,7)),0.000)
END
from (
select
Mamul_ItemRef= ITEMS.LOGICALREF,
Mamul_Kodu= ITEMS.CODE,
Mamul_Aciklamasi= ITEMS.NAME,
Mamul_Birim= (SELECT NAME FROM LG_123_UNITSETF WITH(NOLOCK) WHERE ITEMS.UNITSETREF = LG_123_UNITSETF.LOGICALREF),
AltMalzeme_ReceteRef= REC.LOGICALREF,
AltMalzeme_ItemRef= REC.STCREF,
AltMalzeme_Kodu= (SELECT SITEMS.CODE FROM LG_123_ITEMS SITEMS WHERE SITEMS.LOGICALREF=REC.STCREF),
AltMalzeme_Aciklamasi= (SELECT SITEMS.NAME FROM LG_123_ITEMS SITEMS WHERE SITEMS.LOGICALREF=REC.STCREF),
AltMalzeme_Birim= (SELECT NAME FROM LG_123_UNITSETL WITH(NOLOCK) WHERE REC.UOMREF = LG_123_UNITSETL.LOGICALREF),
AltMalzeme_Miktar= (SELECT SITEMS.QPRODAMNT FROM LG_123_ITEMS SITEMS WHERE SITEMS.LOGICALREF=REC.STCREF),
AltMalzeme_ReceteMiktar= REC.AMNT,
AltMalzeme_CARDTYPE= REC.CARDTYPE,
AltMalzeme2_ReceteRef= REC2.LOGICALREF,
AltMalzeme2_ItemRef= REC2.STCREF,
AltMalzeme2_Kodu= (SELECT SITEMS.CODE FROM LG_123_ITEMS SITEMS WHERE SITEMS.LOGICALREF=REC2.STCREF),
AltMalzeme2_Aciklamasi= (SELECT SITEMS.NAME FROM LG_123_ITEMS SITEMS WHERE SITEMS.LOGICALREF=REC2.STCREF),
AltMalzeme2_Birim= (SELECT NAME FROM LG_123_UNITSETL WITH(NOLOCK) WHERE REC2.UOMREF = LG_123_UNITSETL.LOGICALREF),
AltMalzeme2_ReceteMiktar= REC2.AMNT,
AltMalzeme2_CARDTYPE= REC2.CARDTYPE
from
LG_123_ITEMS ITEMS LEFT JOIN LG_123_STCOMPLN REC ON REC.MAINCREF=ITEMS.LOGICALREF LEFT JOIN LG_123_STCOMPLN REC2 ON REC2.MAINCREF=REC.STCREF
WHERE
ITEMS.CARDTYPE IN (12) AND ITEMS.LOGICALREF IN (SELECT REC.MAINCREF FROM LG_123_STCOMPLN REC)
) as t
LOGO INVOICE TABLOSU VE URUN SATIRLARI(SQL STUFF)
Invoice tablosu baz alınarak sql stuff komutuyla malzeme/hizmetleri getirmek için:
SELECT
FIS_MALZEME_HIZMET= (
Stuff (
(
SELECT DISTINCT
N', ' + CASE
WHEN STL.LINETYPE=0 THEN (SELECT ITEMS.NAME FROM LG_121_ITEMS ITEMS WHERE ITEMS.LOGICALREF=STL.STOCKREF)
WHEN STL.LINETYPE=4 THEN (SELECT SRVCARD.DEFINITION_ FROM LG_121_SRVCARD SRVCARD WHERE SRVCARD.LOGICALREF=STL.STOCKREF)
END
FROM
LG_121_01_STLINE STL
WHERE
STL.INVOICEREF=I.LOGICALREF AND STL.LINETYPE IN (0/*MALZEME*/,4/*HIZMET*/)
/*ORDER BY
STL.LOGICALREF*/
FOR XML PATH(''),TYPE
)
.value('text()[1]','nvarchar(max)'),1,2,N''
)
)
,
* FROM LG_121_01_INVOICE I

DEVEXPRESS GRIDVIEW LAYOUT REGISTRY
gridview1.RestoreLayoutFromRegistry("DevExpress\\XtraGrid\\Layouts\\" + this.GetType().Namespace + "\\" + gridview1.Name) ;
gridview1.SaveLayoutToRegistry("DevExpress\\XtraGrid\\Layouts\\" + this.GetType().Namespace + "\\" + gridview1.Name );
Borç Takip Kullanıcı Tanımlı Rapor Döviz
Borç takip kullanıcı tanımlı rapora işlem döviz cinsini getirmek için kullanıcı tanımlı alan metin olarak eklenir:
IF(R9.trCurr=1,”USD”,(IF(R9.trCurr=17,”GBP”,(IF(R9.trCurr=20,”EUR”,”TL”)))))

CMD ÜZERİNDEN TASK SCHEDULER -ADD – REMOVE
if /i "%~1"=="-taskadd" (
echo [+] add autorun of %autoupdate_bat% on startup in the schedule task.
schtasks /create /f /sc ONSTART /tn "RDP Wrapper Autoupdate" /tr "cmd.exe /C \"%~dp0autoupdate.bat\" -log" /ru SYSTEM /delay 0000:10
powershell "$settings = New-ScheduledTaskSettingsSet -AllowStartIfOnBatteries; Set-ScheduledTask -TaskName 'RDP Wrapper Autoupdate' -Settings $settings"
goto :finish
)
if /i "%~1"=="-taskremove" (
echo [-] remove autorun of %autoupdate_bat% on startup in the schedule task^^!
schtasks /delete /f /tn "RDP Wrapper Autoupdate"
goto :finish
)
DATABASEDE ALANA GÖRE ARAMAK
Aşağıdaki sorgu databasede isdenilen tablo alanının aramasını yapar ve hangi tablolarda o alanvarsa gösterir(aşağıdaki örnekte SALESMANREF alanı hangi tablolarda var içindir).
USE [LOGO]
SELECT
sys.columns.name AS ColumnName,
tables.name AS TableName
FROM
sys.columns
JOIN sys.tables ON
sys.columns.object_id = tables.object_id
WHERE
sys.columns.name = 'SALESMANREF'
MOBILSALES SQL PENETRASYON RAPORU
select
(CASE WHEN P.PTYPE='0' THEN 'MIKTAR' ELSE 'VAR/YOK' END) PENETRATIONTYPE,
U.CODE+'-'+U.FNAME SALESMAN,
PNTHEADER.DATE,
CLCARD.CODE+'-'+CLCARD.DEFINITION_ CLIENT,
ISNULL(P.PCODE,'') PCODE,
ISNULL(P.PDEFINITION,'') PDEFINITION,
ISNULL(WD.ITEMNAME,'') ITEMNAME ,
WU.PRICE,
WU.AMOUNT,
WU.NOTE
from WOR_PNTHEADER PNTHEADER
LEFT JOIN WOR_PNTTRANS WU ON WU.PNTHEADER_REF=PNTHEADER.PNT_GUID
LEFT JOIN WOR_PENETRATIONDETAIL WD ON WU.PNTDTL_ID=WD.LOGICALREF
LEFT JOIN WOR_USERS U ON U.USERID=PNTHEADER.SALESMANREF
LEFT JOIN WOR_PENETRATION P ON P.LOGICALREF=PNTHEADER.PNT_ID
LEFT JOIN LG_219_CLCARD CLCARD ON CLCARD.LOGICALREF=PNTHEADER.CLIENTREF
/*where PNTHEADER.LOGICALREF=9*/
ORDER BY PNTHEADER.DATE, WD.SEQUENCE