public static void GetExchangeRate()
{
try
{
Uri uri = new Uri("http://www.tcmb.gov.tr/kurlar/today.xml");
DataSet ds = GetTCMBKur(uri);
if (ds != null)
{
DovizKurlari kur = new DovizKurlari();
kur.Tarih_Date = new Tarih_Date();
kur.Tarih_Date.Tarih = Convert.ToDateTime(ds.Tables[0].Rows[0]["Tarih"]);
kur.Tarih_Date.Date = ds.Tables[0].Rows[0]["Date"].ToString();
kur.Tarih_Date.Bulten_No = ds.Tables[0].Rows[0]["Bulten_No"].ToString();
kur.Tarih_Date.Currency = new List <Currency>();
foreach (DataRow dr in ds.Tables[1].Rows)
{
Currency data = new Currency();
data.BanknoteBuying = (dr["BanknoteBuying"].ToString() == "" ? default(decimal?) : Convert.ToDecimal(dr["BanknoteBuying"].ToString().Replace(".", ",")));
data.BanknoteSelling = (dr["BanknoteSelling"].ToString() == "" ? default(decimal?) : Convert.ToDecimal(dr["BanknoteSelling"].ToString().Replace(".", ",")));
data.CrossOrder = Convert.ToDecimal(dr["CrossOrder"].ToString().Replace(".", ","));
data.CrossRateOther = (dr["CrossRateOther"].ToString() == "" ? default(decimal?) : Convert.ToDecimal(dr["CrossRateOther"].ToString().Replace(".", ",")));
data.CrossRateUSD = (dr["CrossRateUSD"].ToString() == "" ? default(decimal?) : Convert.ToDecimal(dr["CrossRateUSD"].ToString().Replace(".", ",")));
data.CurrencyCode = dr["CurrencyCode"].ToString();
data.CurrencyName = dr["CurrencyName"].ToString();
data.ForexBuying = (dr["ForexBuying"].ToString() == "" ? default(decimal?) : Convert.ToDecimal(dr["ForexBuying"].ToString().Replace(".", ",")));
data.ForexSelling = (dr["ForexSelling"].ToString() == "" ? default(decimal?) : Convert.ToDecimal(dr["ForexSelling"].ToString().Replace(".", ",")));
data.Kod = dr["Kod"].ToString();
data.Unit = Convert.ToDecimal(dr["Unit"].ToString());
kur.Tarih_Date.Currency.Add(data);
}
SaveCurrency(kur);
}
else
{
logger.Error("Merkez bankasından kur alınamadı.");
}
}
catch (Exception ex)
{
logger.ErrorException("Hata", ex);
}
}
private static void SaveCurrency(DovizKurlari kur)
{
foreach (Currency item in kur.Tarih_Date.Currency)
{
string sConn = System.Configuration.ConfigurationManager.ConnectionStrings["NLog"].ConnectionString;
SqlConnection oConnection = new SqlConnection(sConn);
oConnection.Open();
SqlCommand oCommand = new SqlCommand("SP_InsertCurrency", oConnection);
oCommand.CommandType = CommandType.StoredProcedure;
oCommand.Parameters.Add(new SqlParameter("@BanknoteBuying", SqlDbType.Decimal));
oCommand.Parameters["@BanknoteBuying"].Value = (item.BanknoteBuying == null ? DBNull.Value : (object)item.BanknoteBuying);
oCommand.Parameters.Add(new SqlParameter("@BanknoteSelling", SqlDbType.Decimal));
oCommand.Parameters["@BanknoteSelling"].Value = (item.BanknoteSelling == null ? DBNull.Value : (object)item.BanknoteSelling);
oCommand.Parameters.Add(new SqlParameter("@CrossOrder", SqlDbType.Decimal));
oCommand.Parameters["@CrossOrder"].Value = (item.CrossOrder == null ? DBNull.Value : (object)item.CrossOrder);
oCommand.Parameters.Add(new SqlParameter("@CrossRateOther", SqlDbType.Decimal));
oCommand.Parameters["@CrossRateOther"].Value = (item.CrossRateOther == null ? DBNull.Value : (object)item.CrossRateOther);
oCommand.Parameters.Add(new SqlParameter("@CrossRateUSD", SqlDbType.Decimal));
oCommand.Parameters["@CrossRateUSD"].Value = (item.CrossRateUSD == null ? DBNull.Value : (object)item.CrossRateUSD);
oCommand.Parameters.Add(new SqlParameter("@CurrencyCode", SqlDbType.NVarChar, 10));
oCommand.Parameters["@CurrencyCode"].Value = (item.CurrencyCode == null ? DBNull.Value : (object)item.CurrencyCode);
oCommand.Parameters.Add(new SqlParameter("@CurrencyName", SqlDbType.NVarChar, 100));
oCommand.Parameters["@CurrencyName"].Value = (item.CurrencyName == null ? DBNull.Value : (object)item.CurrencyName);
oCommand.Parameters.Add(new SqlParameter("@ForexBuying", SqlDbType.Decimal));
oCommand.Parameters["@ForexBuying"].Value = (item.ForexBuying == null ? DBNull.Value : (object)item.ForexBuying);
oCommand.Parameters.Add(new SqlParameter("@ForexSelling", SqlDbType.Decimal));
oCommand.Parameters["@ForexSelling"].Value = (item.ForexSelling == null ? DBNull.Value : (object)item.ForexSelling);
oCommand.Parameters.Add(new SqlParameter("@Kod", SqlDbType.NVarChar, 10));
oCommand.Parameters["@Kod"].Value = (item.Kod == null ? DBNull.Value : (object)item.Kod);
oCommand.Parameters.Add(new SqlParameter("@Unit", SqlDbType.Int));
oCommand.Parameters["@Unit"].Value = (item.Unit == null ? DBNull.Value : (object)item.Unit);
SqlDataAdapter oDataAdapter = new SqlDataAdapter(oCommand);
DataSet ds = new DataSet();
oDataAdapter.Fill(ds);
}
}
/// <summary>
/// Depo Transfer kayıt işlemleri
/// </summary>
public Result TahsilatKaydet(frmOnayTahsilatList tahsilatItem, string user)
{
int seriNo = 1;
if (tahsilatItem.IslemTuru == "Tahsilat")
{
seriNo = 1;
}
else if (tahsilatItem.IslemTuru == "İskonto")
{
seriNo = 3;
}
List <string> EvrakBilgi = SqlExper.SelectList <string>(string.Format(@"
SELECT EvrakNo FROM YNS{{0}}.YNS{{0}}.EvrakIni(NOLOCK) WHERE SeriNo={0}
UNION ALL
SELECT CAST(MAX(CAR003_SEQNo) as VarChar) EvrakNo FROM YNS{{0}}.YNS{{0}}.CAR003(NOLOCK)", seriNo));
string EvrakNo = EvrakBilgi[0];
int SeqNo = EvrakBilgi[1].ToInt32();
int tarih = tahsilatItem.Tarih.ToDatetime().ToOADateInt();
string kayitSaat = DateTime.Now.ToString("HHmmss"); ///O anın saatini döner
if (tahsilatItem.IslemTuru == "Tahsilat")
{
if (tahsilatItem.KapatilanTL > 0)
{
SqlExper.Komut(string.Format("EXEC YNS{0}.dbo.FaturalariKapat 0,'{1}','{2}',{3},{4},{5},'{6}','{7}','{8}',{9},'{10}','{11}',{12}",
SirketKodu, tahsilatItem.HesapKodu, EvrakNo, tahsilatItem.KapatilanTL.ToDot(), tahsilatItem.KapatilanTL.ToDot(),
tarih, EvrakNo, tahsilatItem.OdemeTuru, "TL", SeqNo + 1, kayitSaat, user, 0.0));
}
if (tahsilatItem.KapatilanUSD > 0)
{
SqlExper.Komut(string.Format("EXEC YNS{0}.dbo.FaturalariKapat 0,'{1}','{2}',{3},{4},{5},'{6}','{7}','{8}',{9},'{10}','{11}',{12}",
SirketKodu, tahsilatItem.HesapKodu + "$", EvrakNo, tahsilatItem.KapatilanUSD.ToDot(), (tahsilatItem.KapatilanUSD * tahsilatItem.USDKur).ToDot(),
tarih, EvrakNo, tahsilatItem.OdemeTuru, "USD", SeqNo + 1, kayitSaat, user, tahsilatItem.USDKur.ToDot()));
}
if (tahsilatItem.KapatilanEUR > 0)
{
SqlExper.Komut(string.Format("EXEC YNS{0}.dbo.FaturalariKapat 0,'{1}','{2}',{3},{4},{5},'{6}','{7}','{8}',{9},'{10}','{11}',{12}",
SirketKodu, tahsilatItem.HesapKodu + "EU", EvrakNo, tahsilatItem.KapatilanEUR.ToDot(), (tahsilatItem.KapatilanEUR * tahsilatItem.EURKur).ToDot(),
tarih, EvrakNo, tahsilatItem.OdemeTuru, "EUR", SeqNo + 1, kayitSaat, user, tahsilatItem.EURKur.ToDot()));
}
}
else if (tahsilatItem.IslemTuru == "İskonto")
{
DovizKurlari DovizKurlari = SqlExper.SelectFirst <DovizKurlari>(string.Format(DovizKurlari.Sorgu, tarih, tarih - 1, tarih - 2));
if (DovizKurlari.IsNull() || DovizKurlari.USD <= 0 || DovizKurlari.EUR <= 0)
{
throw new Exception("İlgili tarihle alakalı geçerli(0 dan büyük) döviz kuru bulunamadı ! \n DVZ002 tablosunu kontrol ediniz.");
}
string paraCinsi = "TL", ekHesapKod = "";
decimal islemTutar = 0, islemTutarTL = 0, dovizKuru = 0;
islemTutar = tahsilatItem.Tutar;
islemTutarTL = islemTutar;
if (tahsilatItem.DovizCinsi == "USD")
{
paraCinsi = "USD";
islemTutarTL = islemTutar * DovizKurlari.USD;
dovizKuru = DovizKurlari.USD;
ekHesapKod = "$";
}
else if (tahsilatItem.DovizCinsi == "EUR")
{
paraCinsi = "EUR";
islemTutarTL = islemTutar * DovizKurlari.EUR;
dovizKuru = DovizKurlari.EUR;
ekHesapKod = "EU";
}
SqlExper.Komut(string.Format("EXEC YNS{0}.dbo.FaturalariKapat 1,'{1}','{2}',{3},{4},{5},'{6}','{7}','{8}',{9},'{10}','{11}',{12}",
SirketKodu, tahsilatItem.HesapKodu + ekHesapKod, EvrakNo, islemTutar.ToDot(), islemTutarTL.ToDot(),
tarih, EvrakNo, tahsilatItem.OdemeTuru, paraCinsi, SeqNo + 1, kayitSaat, user, dovizKuru.ToDot()));
}
string seri = EvrakNo.Substring(0, 2);
EvrakNo = EvrakNo.Remove(0, 2);
EvrakNo = EvrakNoOlustur(8, seri, EvrakNo.ToInt32());
EvrakIni evrakIni = new EvrakIni
{
pk_SeriNo = seriNo,
EvrakNo = EvrakNo
};
SqlExper.Update(evrakIni);
return(SqlExper.AcceptChanges());
}