public Ponuda Get(int id) { Ponuda ponuda; PonudaPredmet ponudaPredmet = null; PonudaStavka ponudaStavka = null; PonudaStatus ponudaStatus = null; if (id == 0) { ponuda = new Ponuda() { Id = 0, Status = 0, Valuta = "EUR", Vazenje = "Mesec", Vazi = 3 } } ; else { ponuda = _session.QueryOver <Ponuda>() .Left.JoinAlias(x => x.PredmetPonude, () => ponudaPredmet) .Left.JoinAlias(x => x.Stavke, () => ponudaStavka) .Where(x => !x.Obrisan) .And(x => x.Id == id) .And(x => !ponudaPredmet.Obrisan || ponudaPredmet.Id == null) .And(x => !ponudaStavka.Obrisan || ponudaStavka.Id == null) .List <Ponuda>() .FirstOrDefault(); } return(ponuda); }
public KendoResult <Ponuda> PregledGrid([FromBody] KendoRequest kr) //Get([FromUri] FilterContainer filter, int take, int skip, int page, int pageSize) { //if (kr.Filter != null && kr.Filter.Filters.FirstOrDefault(x => x.Field == "all") != null) //{ // var sp = _session.CreateSQLQuery("exec KupacLookup") //} Klijent Klijent = null; Ponuda Ponuda = null; PonudaPredmet PonudaPredmet = null; ObimPoslovanja Obim = null; PonudaStatus PonudaStatus = null; PonudaStavka ponudaStavka = null; var upit = _session.QueryOver <Ponuda>(() => Ponuda) .Where(x => !x.Obrisan); //var subSvedenoEur = QueryOver.Of<Ponuda>() // .Where(p => !p.Obrisan) // .And(p => p.Id == Ponuda.Id) // .And(p => p.Valuta == "RSD") // .JoinQueryOver(p => p.Stavke, () => ponudaStavka) // .And(() => !ponudaStavka.Obrisan) // .SelectList(ls => ls // .Select( // Projections.SqlFunction("Coalesce", NHibernateUtil.Decimal, // Projections.Sum( // Projections.SqlFunction( // new VarArgsSQLFunction("(", " / ", ")"), // NHibernateUtil.Decimal, // Projections.SqlFunction( // new VarArgsSQLFunction("(", " * ", ")"), // NHibernateUtil.Decimal, // Projections.Property(() => ponudaStavka.Kolicina), // Projections.Property(() => ponudaStavka.Cena)) // , // Projections.Property(() => ponudaStavka.Kurs) // )), Projections.Constant(0)) // )); //var subPredmet = QueryOver.Of<PonudaPredmet>() // .Where(x => x.Ponuda.Id == Ponuda.Id); upit.JoinAlias(x => x.Klijent, () => Klijent) .JoinAlias(x => x.PonudaStatus, () => PonudaStatus) .JoinAlias(x => x.PredmetPonude, () => PonudaPredmet) .JoinAlias(() => PonudaPredmet.ObimPoslovanja, () => Obim); //upit.Select(Projections.Distinct( // Projections.ProjectionList() // .Add(Projections.Property(() => Ponuda.Id), "Id") // .Add(Projections.Property(() => Ponuda.Rbr), "Rbr") // .Add(Projections.Property(() => Ponuda.Broj), "Broj") // .Add(Projections.Property(() => Ponuda.DatumPonude), "DatumPonude") // .Add(Projections.Property(() => Ponuda.DatumVazenja), "DatumVazenja") // .Add(Projections.Property(() => Ponuda.Vrednost), "Vrednost") // .Add(Projections.Property(() => Ponuda.Klijent), "Klijent") // .Add(Projections.Property(() => Klijent.Naziv), "KlijentNaziv") // .Add(Projections.Property(() => Ponuda.Prihvacena), "Prihvacena") // .Add(Projections.Property(() => Ponuda.Status), "Status") // .Add(Projections.Property(() => Ponuda.Valuta), "Valuta") // .Add(Projections.Property(() => Ponuda.PonudaStatus), "PonudaStatus") // .Add(Projections.SubQuery(subPredmet), "PredmetPonude") // .Add(Projections.SubQuery(subSvedenoEur), "VrednostSvedenoEur") // )); TextInfo textInfo = CultureInfo.InvariantCulture.TextInfo; if (kr.Filter != null && kr.Filter.Filters.Any()) { foreach (FilterDescription filter in kr.Filter.Filters) { //string prop = textInfo.ToTitleCase(filter.Field); string prop = filter.Field.FirstCharToUpper(); if (prop.ToLower().Contains("klijent.id")) { upit.And(() => Klijent.Id == int.Parse(filter.Value)); } else if (prop.ToLower().Contains("predmetponude")) { upit.And(Restrictions.Disjunction() .Add(Restrictions.On(() => Obim.Sifra) .IsInsensitiveLike(filter.Value, MatchMode.Anywhere)) .Add(Restrictions.On(() => Obim.Naziv) .IsInsensitiveLike(filter.Value, MatchMode.Anywhere))); } else if (prop.ToLower().Contains("vrednost") && filter.Operator == "eq") { upit.And(p => p.Vrednost == decimal.Parse(filter.Value)); } else if (prop.ToLower().Contains("ponudastatus")) { upit.And(() => PonudaStatus.Id == int.Parse(filter.Value)); } else if (prop.ToLower().Contains("vrednost") && filter.Operator == "gt") { upit.And(p => p.Vrednost >= decimal.Parse(filter.Value)); } else if (prop.ToLower().Contains("vrednost") && filter.Operator == "lt") { upit.And(p => p.Vrednost <= decimal.Parse(filter.Value)); } else if (prop.ToLower().Contains("rbr")) { upit.And(() => Ponuda.Rbr == int.Parse(filter.Value)); } else if (prop.ToLower().Contains("klijent.naziv")) { upit.AndRestrictionOn(() => Klijent.Naziv).IsInsensitiveLike(filter.Value, MatchMode.Anywhere); } else if (filter.Value == "Da" || filter.Value == "Ne") { var f = filter.Value != "Ne"; upit.And(Restrictions.Eq(prop, f)); } else if (prop.Contains("DatumPonude")) { var d = Convert.ToDateTime(filter.Value); filter.Value = d.ToLocalTime().ToString("yyyyMMdd"); if (filter.Operator == "gte") { upit.And(x => x.DatumPonude >= d); } if (filter.Operator == "lte") { upit.And(x => x.DatumPonude <= d); } } else if (prop.Contains("DatumVazenja")) { var d = Convert.ToDateTime(filter.Value); filter.Value = d.ToLocalTime().ToString("yyyyMMdd"); if (filter.Operator == "gte") { upit.And(x => x.DatumVazenja >= d); } if (filter.Operator == "lte") { upit.And(x => x.DatumVazenja <= d); } } else if (prop.Contains("DatumPrihvatanja")) { var d = Convert.ToDateTime(filter.Value); filter.Value = d.ToLocalTime().ToString("yyyyMMdd"); if (filter.Operator == "gte") { upit.And(x => x.DatumPrihvatanja.Value >= d); } if (filter.Operator == "lte") { upit.And(x => x.DatumPrihvatanja.Value <= d); } } else if (prop.Contains("DatumOdobrenjaR")) { var d = Convert.ToDateTime(filter.Value); filter.Value = d.ToLocalTime().ToString("yyyyMMdd"); if (filter.Operator == "gte") { upit.And(x => x.DatumOdobrenjaR.Value >= d); } if (filter.Operator == "lte") { upit.And(x => x.DatumOdobrenjaR.Value <= d); } } else if (prop.Contains("DatumOdobrenjaD")) { var d = Convert.ToDateTime(filter.Value); filter.Value = d.ToLocalTime().ToString("yyyyMMdd"); if (filter.Operator == "gte") { upit.And(x => x.DatumOdobrenjaD.Value >= d); } if (filter.Operator == "lte") { upit.And(x => x.DatumOdobrenjaD.Value <= d); } } else { upit.Where(Restrictions.InsensitiveLike(prop, filter.Value, MatchMode.Anywhere)); } } } if (kr.PageSize != 0) { upit.Skip(kr.Skip); upit.Take(kr.Take); } var rowcount = upit.ToRowCountQuery(); if (kr.Sort.Any()) { foreach (Sort sort in kr.Sort) { string prop = sort.Field.FirstCharToUpper(); //textInfo.ToTitleCase(sort.Field); upit.UnderlyingCriteria.AddOrder(new Order(prop, sort.Dir.ToLower() == "asc")); } } else { upit.UnderlyingCriteria.AddOrder(new Order("Id", false)); } upit.Future <Ponuda>(); upit.TransformUsing(Transformers.DistinctRootEntity); //upit.TransformUsing(new AliasToBeanResultTransformer(typeof(PonudaPregled))); //var rowcount = upit.ToRowCountQuery(); rowcount.Select(Projections.CountDistinct(() => Ponuda.Id)); //rowcount.UnderlyingCriteria.AddOrder(new Order("redova", true)); var redova = rowcount.FutureValue <int>().Value; var lista = upit.List <Ponuda>(); var res = new KendoResult <Ponuda> { Data = lista, Total = redova }; return(res); }