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