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(); } } }