public static List <KasaHareketleri> GetListKasaHareketleri(string Tarih1, string Tarih2)
{
object tLockObj = new object();
OleDbCommand command = new OleDbCommand();
OleDbDataReader reader;
List <KasaHareketleri> KasaList = new List <KasaHareketleri>();
string queryString = "";
lock (tLockObj)
{
using (var connection = new OleDbConnection(connectionAdress))
{
try
{
connection.Open();
if (Tarih1 == null || Tarih1 == "")
{
queryString = "SELECT * FROM KasaHareketleri ORDER BY ID DESC";
}
else
{
var t1 = Tarih1;
var t2 = Tarih2;
queryString = "SELECT * FROM KasaHareketleri WHERE Tarih > #" + t1 + "# AND Tarih < #" + t2 + "# ORDER BY ID DESC";
}
command.CommandText = queryString;
command.Connection = connection;
reader = command.ExecuteReader();
while (reader.Read())
{
var kasa = new KasaHareketleri
{
ID = reader["ID"] as int? ?? default(int),
Bakiye = reader["Bakiye"] as int? ?? default(int),
KartSeriNo = reader["KartSeriNo"].ToString(),
Tarih = reader["Tarih"] as DateTime? ?? default(DateTime),
YuklenenKontor = reader["YuklenenKontor"] as int? ?? default(int)
};
KasaList.Add(kasa);
}
}
catch (Exception ex)
{
string temp = ex.Message;
return(null);
}
finally
{
connection.Close();
connection.Dispose();
}
return(KasaList);
}
}
}
public object OdemeTuruToplamListele(NetSatisContext context, int kasaId, Expression <Func <KasaHareket, bool> > pred = null)
{
IQueryable <KasaHareket> KasaHareketleri;
if (pred == null)
{
KasaHareketleri = context.KasaHareketleri.AsNoTracking();
}
else
{
KasaHareketleri = context.KasaHareketleri.AsNoTracking().Where(pred);
}
var res = (from k in KasaHareketleri.Where(x => x.KasaId == kasaId)
group k by new { k.OdemeTuru } into grp
select new
{
grp.Key.OdemeTuru.OdemeTuruAdi,
KasaGiris = grp.Where(x => x.OdemeTuruId == grp.Key.OdemeTuru.Id && x.Hareket == "Kasa Giriş").Sum(s => s.Tutar) ?? 0,
KasaCikis = grp.Where(x => x.OdemeTuruId == grp.Key.OdemeTuru.Id && x.Hareket == "Kasa Çıkış").Sum(s => s.Tutar) ?? 0,
Bakiye = (grp.Where(x => x.OdemeTuruId == grp.Key.OdemeTuru.Id && x.Hareket == "Kasa Giriş").Sum(s => s.Tutar) ?? 0) - (grp.Where(x => x.OdemeTuruId == grp.Key.OdemeTuru.Id && x.Hareket == "Kasa Çıkış").Sum(s => s.Tutar) ?? 0)
}).ToList();
return(res);
#region eski kodlar
//old code
//var result = (from c in context.KasaHareketleri.Where(c => c.KasaId == kasaId)
// group c by new { c.OdemeTuru }
//into grp
// select new
// {
// grp.Key.OdemeTuru.OdemeTuruAdi,
// KasaGiris = (grp.Where(c => c.OdemeTuruId == grp.Key.OdemeTuru.Id && c.Hareket == "Kasa Giriş")
// .Sum(c => c.Tutar) ?? 0),
// KasaCikis = (grp.Where(c => c.OdemeTuruId == grp.Key.OdemeTuru.Id && c.Hareket == "Kasa Çıkış")
// .Sum(c => c.Tutar) ?? 0),
// Bakiye =
// (grp.Where(c => c.OdemeTuruId == grp.Key.OdemeTuru.Id && c.Hareket == "Kasa Giriş")
// .Sum(c => c.Tutar) ?? 0) -
// (grp.Where(c => c.OdemeTuruId == grp.Key.OdemeTuru.Id && c.Hareket == "Kasa Çıkış")
// .Sum(c => c.Tutar) ?? 0)
// }).ToList();
//return result;
#endregion
}
public static bool UpdateKasaHareketleri(KasaHareketleri kasaHareketleri)
{
object tLockObj = new object();
OleDbCommand command = new OleDbCommand();
string queryString = "";
int rowsCount = 0;
lock (tLockObj)
{
using (var connection = new OleDbConnection(connectionAdress))
{
try
{
connection.Open();
queryString = "UPDATE KasaHareketleri SET(Bakiye=@bakiye,KartSeriNo=@kartSeriNo,Tarih=@tarih,YuklenenKontor=@yuklenenKontor) WHERE ID=@id";
command.Connection = connection;
command.CommandText = queryString;
command.Parameters.AddWithValue("@bakiye", kasaHareketleri.Bakiye);
command.Parameters.AddWithValue("@kartSeriNo", kasaHareketleri.KartSeriNo);
command.Parameters.AddWithValue("@yuklenenKontor", kasaHareketleri.YuklenenKontor);
command.Parameters.AddWithValue("@tarih", kasaHareketleri.Tarih);
command.Parameters.AddWithValue("@id", kasaHareketleri.ID);
rowsCount = command.ExecuteNonQuery();
}
catch (Exception)
{
rowsCount = 0;
}
finally
{
connection.Close();
connection.Dispose();
}
if (rowsCount > 0)
{
return(true);
}
else
{
return(false);
}
}
}
}
public static bool AddKasaHareketleri(KasaHareketleri kasaHareketleri)
{
object tLockObj = new object();
OleDbCommand command = new OleDbCommand();
string queryString = "";
int rowsCount = 0;
lock (tLockObj)
{
using (var conncection = new OleDbConnection(connectionAdress))
{
try
{
conncection.Open();
queryString = "INSERT INTO KasaHareketleri(Bakiye,KartSeriNo,Tarih,YuklenenKontor) VALUES(@bakiye,@kartSeriNo,@Tarih,@yuklenenKontor)";
command.CommandText = queryString;
command.Connection = conncection;
command.Parameters.AddWithValue("@bakiye", kasaHareketleri.Bakiye);
command.Parameters.AddWithValue("@kartSeriNo", kasaHareketleri.KartSeriNo);
command.Parameters.AddWithValue("@Tarih", kasaHareketleri.Tarih.ToString("yyyy-MM-dd HH:mm:ss"));
command.Parameters.AddWithValue("@yuklenenKontor", kasaHareketleri.YuklenenKontor);
rowsCount = command.ExecuteNonQuery();
}
catch (Exception ex)
{
string result = ex.Message;
rowsCount = 0;
}
finally
{
conncection.Close();
conncection.Dispose();
}
if (rowsCount > 0)
{
return(true);
}
else
{
return(false);
}
}
}
}
public List <KasaHareketleri> KasaHareketListesi()
{
OleDbDataReader reader;
OleDbCommand command = new OleDbCommand();
List <KasaHareketleri> kasaHareketleri = new List <KasaHareketleri>();
string strKomut = "";
using (var connection = new OleDbConnection(DataTransferObject.connectionAdress))
{
try
{
connection.Open();
strKomut = "SELECT * FROM KasaHareketleri";
command = new OleDbCommand(strKomut, connection);
reader = command.ExecuteReader();
while (reader.Read())
{
var kasaHareket = new KasaHareketleri
{
ID = reader["ID"] as int? ?? default(int),
KartSeriNo = reader["KartSeriNo"].ToString(),
Bakiye = reader["Bakiye"] as int? ?? default(int),
YuklenenKontor = reader["YuklenenKontor"] as int? ?? default(int),
Tarih = reader["Tarih"] as DateTime? ?? default(DateTime)
};
kasaHareketleri.Add(kasaHareket);
}
return(kasaHareketleri);
}
catch (Exception)
{
return(new List <KasaHareketleri>());
}
finally
{
connection.Close();
connection.Dispose();
}
}
}
