public async Task <IEnumerable <FormattedStokSatisDTO> > GetIllerSatis(string interval, int?sayfa)
{
var date = Methods.GetDateFromInterval(interval);
var param = new SqlParameter("date", date);
var query = _db.FormattedStokSatis.FromSql(@"SELECT CARI_IL as Marka,SUM(STHAR_GCMIK) as Satilanadet, str(SUM(STHAR_GCMIK*STHAR_NF),13,2) as Satis FROM TBLSTHAR sth
JOIN TBLCASABIT cas on sth.STHAR_CARIKOD = cas.CARI_KOD
join TBLSTSABIT sts on sth.stok_kodu=sts.stok_kodu
where sth.STHAR_TARIH>=@date and sts.KOD_3 is not null
GROUP BY cas.CARI_IL", param).OrderByDescending(a => a.Satilanadet);
var returnmodel = await PaginatedList <FormattedStokSatisDTO> .CreateAsync(query.AsNoTracking(), sayfa ?? 1, pageSize);
var toplam = new FormattedStokSatisDTO()
{
Marka = "--Toplam--",
Satilanadet = query.Sum(a => a.Satilanadet),
Satis = query.Sum(s => Convert.ToDecimal(s.Satis)).ToString("N")
};
returnmodel.Add(toplam);
return(returnmodel);
}
public async Task <PaginatedList <FormattedStokSatisDTO> > GetStok(string tur, string sezon, int?sayfa)
{
var initialStock = from p in _db.Tblstokph.Include(s => s.Tblstsabit)
where p.DepoKodu == 1 && p.Tblstsabit.GrupKodu != null
select p;
if (!string.IsNullOrEmpty(tur))
{
if (tur == "binek")
{
initialStock = initialStock.Where(a => a.Tblstsabit.Kod3 == "PCR" || a.Tblstsabit.Kod3 == "LTR");
}
else if (tur == "endustriyel")
{
initialStock = initialStock.Where(a => a.Tblstsabit.Kod3 == "TBR" || a.Tblstsabit.Kod3 == "OTR" || a.Tblstsabit.Kod3 == "AGRO");
}
}
if (!string.IsNullOrEmpty(sezon))
{
if (sezon == SezonSabitler.Yaz)
{
initialStock = initialStock.Where(a => a.Tblstsabit.Kod2 == SezonSabitler.Yaz);
}
else if (sezon == SezonSabitler.Kis)
{
initialStock = initialStock.Where(a => a.Tblstsabit.Kod2 == SezonSabitler.Kis);
}
else if (sezon == SezonSabitler.DortMevsim)
{
initialStock = initialStock.Where(a => a.Tblstsabit.Kod2 == SezonSabitler.DortMevsim);
}
}
var stocks = from p in initialStock
group p by p.Tblstsabit.GrupKodu into g
select new FormattedStokSatisDTO
{
Marka = g.Key,
Satilanadet = g.Sum(k => k.TopGirisMik) - g.Sum(k => k.TopCikisMik),
Satis = g.Sum(d => d.Tblstsabit.SatisFiat1 * (d.TopGirisMik - d.TopCikisMik) * 1.18M).Value.ToString("N")
};
var returnmodel = await PaginatedList <FormattedStokSatisDTO> .CreateAsync(stocks.OrderByDescending(g => g.Satilanadet).AsNoTracking(), sayfa ?? 1, pageSize);
var total = new FormattedStokSatisDTO()
{
Marka = "--Toplam--",
Satilanadet = stocks.Sum(s => s.Satilanadet),
Satis = stocks.Sum(s => Convert.ToDecimal(s.Satis)).ToString("N")
};
returnmodel.Add(total);
return(returnmodel);
}
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 <PaginatedList <FormattedStokSatisDTO> > GetEbat(string interval, DateTime?beginingDate, DateTime?endDate, int?sayfa)
{
var query = from b in _db.Tblsthar
where b.Tblstsabit.GrupKodu != null && b.StharBgtip == "F" && b.StharGckod == "C"
select b;
if (!string.IsNullOrEmpty(interval))
{
var date = Methods.GetDateFromInterval(interval);
query = query.Where(a => a.StharTarih >= date);
}
if (beginingDate != null)
{
query = query.Where(a => a.StharTarih >= beginingDate);
}
if (endDate != null)
{
query = query.Where(a => a.StharTarih <= endDate);
}
var markamodel = query.Include(d => d.Tblstsabit).ThenInclude(a => a.Tblstsabitek).GroupBy(a => a.Tblstsabit.Tblstsabitek.Kull7s)
.Select(a => new FormattedStokSatisDTO()
{
Marka = a.Key, Satilanadet = a.Sum(s => s.StharGcmik), Satis = a.Sum(s => s.StharNf * s.StharGcmik).Value.ToString("N")
}).OrderByDescending(d => d.Satilanadet);
var returnmodel = await PaginatedList <FormattedStokSatisDTO> .CreateAsync(markamodel.AsNoTracking(), sayfa ?? 1, pageSize);
var total = new FormattedStokSatisDTO()
{
Marka = "--Toplam--",
Satilanadet = markamodel.Sum(s => s.Satilanadet),
Satis = markamodel.Sum(s => Convert.ToDecimal(s.Satis)).ToString("N")
};
returnmodel.Add(total);
return(returnmodel);
}