public async Task <IEnumerable <FormattedStokSatisDTO> > GetPlasiyerSatis(string interval, int?sayfa) { var date = Methods.GetDateFromInterval(interval); var plasiyerler = await _db.Tblcariplasiyer.Where(s => s.Aciklama1 == "1").Select(a => new { plasiyeradi = a.PlasiyerAciklama, plasiyerkodu = a.PlasiyerKodu }).ToListAsync(); var cariler = await _db.Tblcasabit.Select(s => new { carikod = s.CariKod, plasiyerkodu = s.PlasiyerKodu }).ToListAsync(); var satislist = await _db.Tblcahar.Where(a => a.HareketTuru == "B" && a.Borc > 0 && a.Tarih >= date) .Select(a => new { CariKod = a.CariKod, Tutar = a.Borc }).ToListAsync(); var adetlist = await _db.Tblsthar.Where(a => a.StharBgtip == "F" && a.StharGckod == "C" && a.StharTarih >= date) .Select(a => new { CariKod = a.StharCarikod, Adet = a.StharGcmik }).ToListAsync(); var tempmodel = new List <StokSatisDTO>(); foreach (var plasiyer in plasiyerler) { decimal?sum = 0; decimal?count = 0; var onemodel = new StokSatisDTO() { Marka = plasiyer.plasiyeradi }; foreach (var cari in cariler.Where(a => a.plasiyerkodu == plasiyer.plasiyerkodu)) { sum += satislist.Where(a => a.CariKod == cari.carikod).Sum(a => a.Tutar); count += adetlist.Where(a => a.CariKod == cari.carikod).Sum(a => a.Adet); } ; onemodel.Satis = sum; onemodel.Satilanadet = count; tempmodel.Add(onemodel); } var returnmodel = tempmodel.OrderByDescending(a => a.Satis) .Select(s => new FormattedStokSatisDTO { Marka = s.Marka, Satilanadet = s.Satilanadet, Satis = s.Satis?.ToString("N") }).Where(a => a.Satilanadet > 0).ToList(); var toplam = new FormattedStokSatisDTO() { Marka = "--Toplam--", Satilanadet = returnmodel.Sum(a => a.Satilanadet), Satis = returnmodel.Sum(a => Convert.ToDecimal(a.Satis)).ToString("N") }; returnmodel = returnmodel.Take(15).ToList(); returnmodel.Add(toplam); //int pageSize = 15; //var returnmodel = await PaginatedList<FormattedStokSatisDTO>.CreateAsync(satis.AsNoTracking(), sayfa ?? 1, pageSize); return(returnmodel); }
public async Task <IActionResult> StocksRemain(string tur, string mevsim) { var query = _db.Tblstokph.Include(a => a.Tblstsabit).Where(a => a.Tblstsabit.GrupKodu != null && a.DepoKodu == 1 && (a.TopGirisMik - a.TopCikisMik) > 0); if (!string.IsNullOrEmpty(tur)) { switch (tur) { case "Binek": query = query.Where(a => a.Tblstsabit.Kod3.Contains("PCR") || a.Tblstsabit.Kod3.Contains("LTR")); ViewData["tur"] = "Binek"; break; case "Kamyon": query = query.Where(a => a.Tblstsabit.Kod3.Contains("OTR") || a.Tblstsabit.Kod3.Contains("TBR") || a.Tblstsabit.Kod3.Contains("AGRO")); ViewData["tur"] = "Kamyon"; break; default: break; } } if (!string.IsNullOrEmpty(mevsim)) { switch (mevsim) { case "YAZ": query = query.Where(a => a.Tblstsabit.Kod2.Contains("YAZ")); ViewData["mevsim"] = "yaz"; break; case "KIS": query = query.Where(a => a.Tblstsabit.Kod2.Contains("KIS")); ViewData["mevsim"] = "kis"; break; case "4MEVSIM": query = query.Where(a => a.Tblstsabit.Kod2.Contains("4MEVSIM")); ViewData["mevsim"] = "4mevsim"; break; default: break; } } var stocks = await query.Select(a => new { a.TopGirisMik, a.TopCikisMik, a.Tblstsabit.GrupKodu, a.Tblstsabit.SatisFiat1 }).ToListAsync(); var markalar = stocks.Select(a => a.GrupKodu).Distinct(); var stocklist = new List <StokSatisDTO>(); foreach (var marka in markalar) { var listitem = new StokSatisDTO() { Marka = marka, Satilanadet = stocks.Where(a => a.GrupKodu == marka).Sum(a => (a.TopGirisMik - a.TopCikisMik)), Satis = stocks.Where(a => a.GrupKodu == marka).Sum(a => a.SatisFiat1 * (a.TopGirisMik - a.TopCikisMik) * 1.18M) }; stocklist.Add(listitem); } var stocklist2 = stocklist.OrderByDescending(a => a.Satilanadet); return(View(stocklist2)); }
public async Task <IActionResult> SellBrand(string interval, DateTime?begindate, DateTime?enddate, string tur, string mevsim) { var query = from b in _db.Tblsthar where b.Tblstsabit.GrupKodu != null && b.StharBgtip == "F" && b.StharGckod == "C" select b; if (!string.IsNullOrEmpty(tur)) { switch (tur) { case "Binek": query = query.Where(a => a.Tblstsabit.Kod3.Contains("PCR") || a.Tblstsabit.Kod3.Contains("LTR")); ViewData["tur"] = "Binek"; break; case "Kamyon": query = query.Where(a => a.Tblstsabit.Kod3.Contains("OTR") || a.Tblstsabit.Kod3.Contains("TBR") || a.Tblstsabit.Kod3.Contains("AGRO")); ViewData["tur"] = "Kamyon"; break; default: break; } } if (!string.IsNullOrEmpty(mevsim)) { switch (mevsim) { case "YAZ": query = query.Where(a => a.Tblstsabit.Kod2.Contains("YAZ")); ViewData["mevsim"] = "yaz"; break; case "KIS": query = query.Where(a => a.Tblstsabit.Kod2.Contains("KIS")); ViewData["mevsim"] = "kis"; break; case "4MEVSIM": query = query.Where(a => a.Tblstsabit.Kod2.Contains("4MEVSIM")); ViewData["mevsim"] = "4mevsim"; break; default: break; } } var date = DateTime.Today; if (!string.IsNullOrEmpty(interval)) { date = Methods.GetDateFromInterval(interval); query = query.Where(a => a.StharTarih >= date); } else if (begindate != null && enddate != null) { query = query.Where(a => a.StharTarih >= begindate && a.StharTarih <= enddate); ViewData["aralik"] = "Rapor Aralığı : " + begindate.Value.ToShortDateString() + " - " + enddate.Value.ToShortDateString(); } else { ViewData["aralik"] = "Günlük Rapor"; query = query.Where(a => a.StharTarih >= date); } //var yeni =await query.Where(a => a.StharBgtip == "F") // .GroupBy(a => a.Tblstsabit.GrupKodu).Select(a => new StokSatisDTO() { Marka = a.Select(b => b.Tblstsabit.GrupKodu).FirstOrDefault(), Satilanadet = a.Sum(b => b.StharGcmik),Satis=a.Sum(b=>(b.StharNf * b.StharGcmik *1.18M)) }) // .OrderByDescending(a => a.Satilanadet).ToListAsync(); var toplamsatilan = await query.Include(a => a.Tblstsabit).Select(a => new { a.StharGcmik, a.StharNf, a.Tblstsabit.GrupKodu }).ToListAsync(); var markalist = toplamsatilan.Select(a => a.GrupKodu).Distinct(); var satisliste = new List <StokSatisDTO>(); foreach (var marka in markalist) { var satis = new StokSatisDTO { Marka = marka, Satilanadet = toplamsatilan.Where(a => a.GrupKodu == marka).Sum(a => a.StharGcmik), Satis = toplamsatilan.Where(a => a.GrupKodu == marka).Sum(a => a.StharNf * a.StharGcmik) * 1.18M }; satisliste.Add(satis); } var satisliste2 = satisliste.OrderByDescending(a => a.Satilanadet); return(View(satisliste2)); }