public static Response <Servicio> Get(this Servicio request,
Factory factory,
IHttpRequest httpRequest)
{
return(factory.Execute(proxy => {
long?totalCount = null;
var paginador = new Paginador(httpRequest);
var predicate = PredicateBuilder.True <Servicio>();
var visitor = ReadExtensions.CreateExpression <Servicio>();
if (!request.Nombre.IsNullOrEmpty())
{
predicate = predicate.AndAlso(q => q.Nombre.Contains(request.Nombre));
}
visitor.Where(predicate).OrderBy(f => f.Nombre);
if (paginador.PageNumber.HasValue)
{
visitor.Select(r => Sql.Count(r.Id));
totalCount = proxy.Count(visitor);
visitor.Select();
int rows = paginador.PageSize.HasValue? paginador.PageSize.Value:BL.ResponsePageSize;
visitor.Limit(paginador.PageNumber.Value * rows, rows);
}
return new Response <Servicio>()
{
Data = proxy.Get(visitor),
TotalCount = totalCount
};
}));
}
public override object OnGet(Tarea request)
{
try{
var httpRequest = RequestContext.Get <IHttpRequest>();
return(Factory.Execute(proxy => {
long?totalCount = null;
var paginador = new Paginador(httpRequest);
var visitor = ReadExtensions.CreateExpression <Tarea>();
var predicate = PredicateBuilder.True <Tarea>();
var userId = int.Parse(httpRequest.GetSession().UserAuthId);
predicate = q => q.UserId == userId;
if (request.IdCliente.HasValue && request.IdCliente.Value != default(int))
{
predicate = q => q.IdCliente == request.IdCliente.Value;
}
if (!request.NitCliente.IsNullOrEmpty())
{
predicate = predicate.AndAlso(q => q.NitCliente.StartsWith(request.NitCliente));
}
if (!request.NombreCliente.IsNullOrEmpty())
{
predicate = predicate.AndAlso(q => q.NombreCliente.Contains(request.NombreCliente));
}
var qs = httpRequest.QueryString["Cumplida"];
bool cumplida;
if (bool.TryParse(qs, out cumplida))
{
predicate = predicate.AndAlso(q => q.Cumplida == cumplida);
}
visitor.Where(predicate).OrderByDescending(f => f.Fecha);
if (paginador.PageNumber.HasValue)
{
visitor.Select(r => Sql.Count(r.Id));
totalCount = proxy.Count(visitor);
visitor.Select();
int rows = paginador.PageSize.HasValue? paginador.PageSize.Value:BL.ResponsePageSize;
visitor.Limit(paginador.PageNumber.Value * rows, rows);
}
return new Response <Tarea>()
{
TotalCount = totalCount,
Data = proxy.Get(visitor)
};
}));
}
catch (Exception e) {
return(HttpResponse.ErrorResult <Response <Tarea> >(e, "GetTareaError"));
}
}
public static Response <RolePermission> Get(this RolePermission request,
Factory factory,
IHttpRequest httpRequest)
{
return(factory.Execute(proxy => {
long?totalCount = null;
var paginador = new Paginador(httpRequest);
var visitor = ReadExtensions.CreateExpression <RolePermission>();
var predicate = PredicateBuilder.True <RolePermission>();
predicate = q => q.AuthRoleId == request.AuthRoleId;
visitor.Where(predicate).OrderBy(f => f.Name);
if (paginador.PageNumber.HasValue)
{
visitor.Select(r => Sql.Count(r.Id));
totalCount = proxy.Count(visitor);
visitor.Select();
int rows = paginador.PageSize.HasValue? paginador.PageSize.Value:BL.ResponsePageSize;
visitor.Limit(paginador.PageNumber.Value * rows, rows);
}
return new Response <RolePermission>()
{
Data = proxy.Get(visitor),
TotalCount = totalCount
};
}));
}
public T FirstOrDefault<T>(string tableName, SqlExpressionVisitor<T> visitor)
where T: new()
{
if(visitor==null) visitor= ReadExtensions.CreateExpression<T>();
string sql= string.Format(visitor.ToSelectStatement(),tableName);
return Execute(dbCmd=> dbCmd.FirstOrDefault<T>(sql) );
}
public void Can_Chain_Order_Expressions_using_ThenBy()
{
db.Insert(People);
var visitor = ReadExtensions.SqlExpression <Person>();
visitor.OrderBy(x => x.Age);
visitor.ThenBy(x => x.FirstName);
var results = db.Select(visitor);
Console.WriteLine("Sorting using Linq");
var expected = People.OrderBy(x => x.Age).ThenBy(x => x.FirstName).ToList();
foreach (var e in expected)
{
Console.WriteLine(e.ToString());
}
Console.WriteLine("Retrieved from DB");
foreach (var r in results)
{
Console.WriteLine(r.ToString());
}
for (int i = 0; i < expected.Count(); i++)
{
if (results[i].Id != expected[i].Id)
{
Assert.Fail("Expected person with id {0}, got {1}", expected[i].Id, results[i].Id);
}
}
visitor.OrderBy(); //clears orderBy Expression
visitor.OrderBy(x => x.Age);
visitor.ThenByDescending(x => x.FirstName);
results = db.Select(visitor);
Console.WriteLine("Sorting using Linq");
expected = People.OrderBy(x => x.Age).ThenByDescending(x => x.FirstName).ToList();
foreach (var e in expected)
{
Console.WriteLine(e.ToString());
}
Console.WriteLine("Retrieved from DB");
foreach (var r in results)
{
Console.WriteLine(r.ToString());
}
for (int i = 0; i < expected.Count(); i++)
{
if (results[i].Id != expected[i].Id)
{
Assert.Fail("Expected person with id {0}, got {1}", expected[i].Id, results[i].Id);
}
}
}
public static Response <User> Get(this User request,
Factory factory,
IHttpRequest httpRequest)
{
return(factory.Execute(proxy => {
var userSession = httpRequest.GetSession();
//if(!( userSession.HasRole(RoleNames.Admin) ||
// userSession.HasPermission("User.read") ))
// throw HttpError.Unauthorized("Usuario no autorizado para leer listado de usuarios");
long?totalCount = null;
var paginador = new Paginador(httpRequest);
var visitor = ReadExtensions.CreateExpression <User>();
var predicate = PredicateBuilder.True <User>();
if (userSession.HasRole(RoleNames.Admin))
{
if (request.Id != default(int))
{
predicate = q => q.Id == request.Id;
}
if (!request.UserName.IsNullOrEmpty())
{
predicate = q => q.UserName.StartsWith(request.UserName);
}
if (userSession.UserName != BL.AdminUser)
{
predicate = predicate.AndAlso(q => q.UserName != BL.AdminUser);
}
}
else
{
var id = int.Parse(userSession.UserAuthId);
predicate = q => q.Id == id;
}
visitor.Where(predicate).OrderBy(r => r.UserName);;
if (paginador.PageNumber.HasValue)
{
visitor.Select(r => Sql.Count(r.Id));
totalCount = proxy.Count(visitor);
visitor.Select();
int rows = paginador.PageSize.HasValue? paginador.PageSize.Value:BL.ResponsePageSize;
visitor.Limit(paginador.PageNumber.Value * rows, rows);
}
return new Response <User>()
{
Data = proxy.Get(visitor),
TotalCount = totalCount
};
}));
}
/*
public void Update<T>(T request,Expression<Func<T,bool>> predicate=null)
where T: new()
{
Execute(dbCmd=>{
if(predicate==null) dbCmd.Update(request);
else dbCmd.Update(request,predicate);
});
}
public void Update<T>(T request, SqlExpressionVisitor<T> expression=null)
where T: new()
{
Execute(dbCmd=>{
if(expression==null) dbCmd.Update(request);
else dbCmd.UpdateOnly(request,expression);
});
}
*/
public void Update<T>(T request, string tableName, SqlExpressionVisitor<T> expression=null )
where T : new()
{
if(expression==null) expression=ReadExtensions.CreateExpression<T>();
string sql = OrmLiteConfig.DialectProvider.ToUpdateRowStatement(request,expression.UpdateFields);
sql= string.Format(sql,tableName);
sql = sql +( !expression.WhereExpression.IsNullOrEmpty()? expression.WhereExpression:"" );
Execute(dbCmd=>dbCmd.ExecuteSql( sql));
}
public void TestTwoBucketCountOverflow()
{
ushort[] addresses = { 0, 1, 2, 3, 4, 5, 6, 7 };
ushort maxContiguous = (ushort)(addresses.Length - 1);
var buckets = ReadExtensions.FormBuckets(addresses, maxContiguous);
Assert.AreEqual(2, buckets.Count());
}
public void TestFourBucketCountMultipleOverflow()
{
ushort[] addresses = { 0, 1, 2, 3, 4, 5, 6, 7 };
ushort maxContiguous = 2;
var buckets = ReadExtensions.FormBuckets(addresses, maxContiguous);
Assert.AreEqual(4, buckets.Count());
}
public void TestOneBucketLengthNoOverflow()
{
ushort[] addresses = { 0, 1, 2, 3, 4, 5, 6, 7 };
ushort maxContiguous = (ushort)addresses.Length;
var buckets = ReadExtensions.FormBuckets(addresses, maxContiguous);
ushort length = buckets.ElementAt(0).Item2;
Assert.AreEqual(addresses.Length, length);
}
public void TestTwoBucketsLengthsNoOverflow()
{
ushort[] addresses = { 0, 1, 2, 4, 5, 6, 7 };
ushort maxContiguous = (ushort)addresses.Length;
var buckets = ReadExtensions.FormBuckets(addresses, maxContiguous);
ushort length1 = buckets.ElementAt(0).Item2;
ushort length2 = buckets.ElementAt(1).Item2;
Assert.AreEqual(3, length1);
Assert.AreEqual(4, length2);
}
public static Response <Contacto> Get(this Contacto request,
Factory factory,
IHttpRequest httpRequest)
{
return(factory.Execute(proxy => {
long?totalCount = null;
var paginador = new Paginador(httpRequest);
var queryString = httpRequest.QueryString;
var visitor = ReadExtensions.CreateExpression <Contacto>();
var predicate = PredicateBuilder.True <Contacto>();
if (request.IdCliente != default(int))
{
predicate = q => q.IdCliente == request.IdCliente;
}
else if (!request.Nombre.IsNullOrEmpty())
{
predicate = q => q.Nombre.StartsWith(request.Nombre);
visitor.OrderBy(r => r.Nombre);
}
var qs = queryString["Activo"];
bool activo;
if (bool.TryParse(qs, out activo))
{
predicate = predicate.AndAlso(q => q.Activo == activo);
}
visitor.Where(predicate);
if (paginador.PageNumber.HasValue)
{
visitor.Select(r => Sql.Count(r.Id));
totalCount = proxy.Count(visitor);
visitor.Select();
int rows = paginador.PageSize.HasValue? paginador.PageSize.Value:BL.ResponsePageSize;
visitor.Limit(paginador.PageNumber.Value * rows, rows);
}
return new Response <Contacto>()
{
Data = proxy.Get(visitor),
TotalCount = totalCount
};
}));
}
public static Response <Pedido> Get(this Pedido request,
Factory factory,
IHttpRequest httpRequest)
{
return(factory.Execute(proxy => {
long?totalCount = null;
var paginador = new Paginador(httpRequest);
var visitor = ReadExtensions.CreateExpression <Pedido>();
var predicate = PredicateBuilder.True <Pedido>();
if (request.Consecutivo != default(int))
{
predicate = q => q.Consecutivo == request.Consecutivo;
}
else
{
visitor.OrderByDescending(f => f.Consecutivo);
if (!request.NombreCliente.IsNullOrEmpty())
{
predicate = q => q.NombreCliente.StartsWith(request.NombreCliente);
visitor.OrderBy(f => f.NombreCliente);
}
if (!request.NitCliente.IsNullOrEmpty())
{
predicate = predicate.AndAlso(q => q.NitCliente.StartsWith(request.NitCliente));
visitor.OrderBy(f => f.NitCliente);
}
}
visitor.Where(predicate);
if (paginador.PageNumber.HasValue)
{
visitor.Select(r => Sql.Count(r.Id));
totalCount = proxy.Count(visitor);
visitor.Select();
int rows = paginador.PageSize.HasValue? paginador.PageSize.Value:BL.ResponsePageSize;
visitor.Limit(paginador.PageNumber.Value * rows, rows);
}
return new Response <Pedido>()
{
Data = proxy.Get(visitor),
TotalCount = totalCount
};
}));
}
public void Create<T>(T request, string tableName, SqlExpressionVisitor<T> expression=null )
where T: IHasId<System.Int32>, new()
{
if(expression==null) expression=ReadExtensions.CreateExpression<T>();
Execute(dbCmd=>{
string sql= OrmLiteConfig.DialectProvider.ToInsertRowStatement(request,
expression.InsertFields,
dbCmd);
sql=string.Format(sql,tableName);
dbCmd.ExecuteSql(sql);
dbCmd.AssertId(request);
});
}
public override object OnGet(PedidoItem request)
{
try{
var factory = Factory;
var httpRequest = RequestContext.Get <IHttpRequest>();
return(factory.Execute(proxy => {
long?totalCount = null;
var paginador = new Paginador(httpRequest);
var visitor = ReadExtensions.CreateExpression <PedidoItem>();
var predicate = PredicateBuilder.True <PedidoItem>();
if (request.IdPedido != default(int))
{
predicate = q => q.IdPedido == request.IdPedido;
visitor.OrderBy(f => f.Id);
}
visitor.Where(predicate);
if (paginador.PageNumber.HasValue)
{
visitor.Select(r => Sql.Count(r.Id));
totalCount = proxy.Count(visitor);
visitor.Select();
int rows = paginador.PageSize.HasValue? paginador.PageSize.Value:BL.ResponsePageSize;
visitor.Limit(paginador.PageNumber.Value * rows, rows);
}
var data = proxy.Get(visitor);
foreach (var d in data)
{
d.Descripcion = d.Descripcion.Decode();
d.Nota = d.Nota.Decode();
}
return new Response <PedidoItem>()
{
Data = data,
TotalCount = totalCount
};
}));
}
catch (Exception e) {
return(HttpResponse.ErrorResult <Response <PedidoItem> >(e, "GetPedidoItemError"));
}
}
public static Response <UserRole> Get(this UserRole request,
Factory factory,
IHttpRequest httpRequest)
{
return(factory.Execute(proxy => {
var userSession = httpRequest.GetSession();
long?totalCount = null;
var paginador = new Paginador(httpRequest);
var visitor = ReadExtensions.CreateExpression <UserRole>();
var predicate = PredicateBuilder.True <UserRole>();
if (request.UserId == default(int))
{
request.UserId = int.Parse(userSession.UserAuthId);
}
else if (!(userSession.HasRole(RoleNames.Admin)) &&
request.UserId != int.Parse(userSession.UserAuthId)
)
{
throw HttpError.Unauthorized("Usuario no puede leer listado de roles de otro usuario");
}
predicate = q => q.UserId == request.UserId;
visitor.Where(predicate).OrderBy(f => f.Name);
if (paginador.PageNumber.HasValue)
{
visitor.Select(r => Sql.Count(r.Id));
totalCount = proxy.Count(visitor);
visitor.Select();
int rows = paginador.PageSize.HasValue? paginador.PageSize.Value:BL.ResponsePageSize;
visitor.Limit(paginador.PageNumber.Value * rows, rows);
}
return new Response <UserRole>()
{
Data = proxy.Get(visitor),
TotalCount = totalCount
};
}));
}
public void Can_Chain_Expressions_Using_And()
{
db.Insert(People);
var visitor = ReadExtensions.SqlExpression <Person>();
visitor.Where(x => x.FirstName.StartsWith("Jim")).And(x => x.LastName.StartsWith("Hen"));
var results = db.Select <Person>(visitor);
Assert.AreEqual(1, results.Count);
visitor.Where(); //clears underlying expression
visitor.Where(x => x.LastName.StartsWith("J")).And(x => x.Age > 40);
results = db.Select(visitor);
Assert.AreEqual(results[0].FirstName, "Michael");
}
public static Response <Ciudad> Get(this Ciudad request,
Factory factory,
IHttpRequest httpRequest)
{
return(factory.Execute(proxy => {
long?totalCount = null;
var paginador = new Paginador(httpRequest);
var visitor = ReadExtensions.CreateExpression <Ciudad>();
var predicate = PredicateBuilder.True <Ciudad>();
if (request.Id != default(int))
{
predicate = q => q.Id == request.Id;
}
if (!request.Nombre.IsNullOrEmpty())
{
predicate = q => q.Nombre.StartsWith(request.Nombre);
visitor.OrderBy(r => r.Nombre);
}
visitor.Where(predicate);
if (paginador.PageNumber.HasValue)
{
visitor.Select(r => Sql.Count(r.Id));
totalCount = proxy.Count(visitor);
visitor.Select();
int rows = paginador.PageSize.HasValue? paginador.PageSize.Value:BL.ResponsePageSize;
visitor.Limit(paginador.PageNumber.Value * rows, rows);
}
return new Response <Ciudad>()
{
Data = proxy.Get(visitor),
TotalCount = totalCount
};
}));
}
public List <Course> Get(CoursesListReq request)
{
var exp = ReadExtensions.CreateExpression <Course> ();
if (!String.IsNullOrEmpty(request.language))
{
exp.Where(x => x.Language == request.language);
}
if (!String.IsNullOrEmpty(request.sort))
{
exp.OrderBy("ORDER BY " + request.sort);
}
exp.Limit(String.IsNullOrEmpty(request.offset) ? 0 : Int32.Parse(request.offset),
String.IsNullOrEmpty(request.limit) ? 20 : Int32.Parse(request.limit));
var list = Db.Select <Course> (exp);
Console.WriteLine(Db.GetLastSql());
return(list);
}
public void When_chaining_expressions_using_Where_it_behaves_like_And()
{
db.Insert(People);
var visitor = ReadExtensions.SqlExpression <Person>();
visitor.Where(x => x.FirstName.StartsWith("Jim"));
visitor.Where(x => x.LastName.StartsWith("Hen"));
//WHERE (upper("FirstName") like 'JIM%' AND upper("LastName") like 'HEN%' )
var results = db.Select <Person>(visitor);
Assert.AreEqual(1, results.Count);
visitor.Or(x => x.FirstName.StartsWith("M"));
//WHERE ((upper("FirstName") like 'JIM%' AND upper("LastName") like 'HEN%' ) OR upper("FirstName") like 'M%' )
results = db.Select(visitor);
Assert.AreEqual(2, results.Count);
visitor.Where(x => x.FirstName.StartsWith("M"));
//WHERE (((upper("FirstName") like 'JIM%' AND upper("LastName") like 'HEN%' ) OR upper("FirstName") like 'M%' ) AND upper("FirstName") like 'M%' )
results = db.Select(visitor);
Assert.AreEqual(1, results.Count);
}
private static void TestDialect(Dialect dialect)
{
Console.Clear();
Console.WriteLine("Testing expressions for Dialect {0}", dialect.Name);
OrmLiteConfig.ClearCache();
OrmLiteConfig.DialectProvider = dialect.DialectProvider;
using (IDbConnection db =
dialect.ConnectionString.OpenDbConnection())
using (IDbCommand dbCmd = db.CreateCommand())
{
dbCmd.DropTable <Person>();
dbCmd.DropTable <City>();
dbCmd.DropTable <Country>();
dbCmd.CreateTable <Country>();
dbCmd.CreateTable <City>();
dbCmd.CreateTable <Person>();
dbCmd.InsertAll <Country>(Factory.CountryList);
dbCmd.InsertAll <City>(Factory.CityList);
dbCmd.InsertAll <Person>(Factory.PersonList);
try{
var vis = ReadExtensions.CreateExpression <TestPerson>();
vis.Where(r => r.Continent == "Europe");
Console.WriteLine(vis.ToSelectStatement());
Console.WriteLine("-----------------------------------------");
vis.ExcludeJoin = true;
vis.Where();
Console.WriteLine(vis.ToSelectStatement());
Console.WriteLine("-----------------------------------------");
var r0 = dbCmd.Select <TestPerson>();
Console.WriteLine("Records en person: '{0}'", r0.Count);
vis.ExcludeJoin = false;
vis.Select(r => new { r.Continent, r.Name }).OrderBy(r => r.BirthCountry);
Console.WriteLine(vis.ToSelectStatement());
Console.WriteLine("-----------------------------------------");
vis.SelectDistinct(r => r.Name);
Console.WriteLine(vis.ToSelectStatement());
Console.WriteLine("-----------------------------------------");
vis.Select();
vis.Where(r => r.Continent == "Europe").OrderBy(r => r.BirthCountry);
r0 = dbCmd.Select(vis);
Console.WriteLine("Records en person r.Continent=='Europe': '{0}'", r0.Count);
r0 = dbCmd.Select <TestPerson>(r => r.BirthCity == "London");
Console.WriteLine("Records en person r.BirthCity=='London': '{0}'", r0.Count);
TestPerson tp = r0[0];
tp.Id = 0;
dbCmd.Insert(tp);
tp.Id = (int)dbCmd.GetLastInsertId();
Console.WriteLine("El id es :'{0}'", tp.Id);
Console.WriteLine("Actualizados : '{0}'", dbCmd.UpdateOnly(tp, r => r.Name, r => r.Id == 0));
try{
dbCmd.Update(tp); // all fields, except PK are updated where tp.Id==15
}
catch (Exception e) {
Console.WriteLine(e.Message);
}
Console.WriteLine("Borrados : '{0}'", dbCmd.Delete <TestPerson>(r => r.Id == 0));
int expected = 6;
var r1 = dbCmd.Select <City>(qr => qr.Population >= 10);
Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, r1.Count, expected == r1.Count?"OK":"********* FAILED *********");
expected = 7;
var r2 = dbCmd.Select <Join1>(qr => qr.Population <= 5);
Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, r2.Count, expected == r2.Count?"OK":"********* FAILED *********");
expected = 3;
var r3 = dbCmd.Select <Join2>(qr => qr.BirthCity == "London");
Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, r3.Count, expected == r3.Count?"OK":"********* FAILED *********");
expected = 5;
var r4 = dbCmd.Select <Join3>(qr => qr.Continent == "Europe");
Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, r4.Count, expected == r4.Count?"OK":"********* FAILED *********");
expected = 5;
var city = "Bogota";
var r5 = dbCmd.Select <PersonCity>(qr => qr.JobCity == city);
Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, r5.Count, expected == r5.Count?"OK":"********* FAILED *********");
expected = 6;
var r6 = dbCmd.Select <DerivatedFromPerson>(qr => qr.BirthCityId != qr.JobCityId);
Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, r6.Count, expected == r6.Count?"OK":"********* FAILED *********");
expected = 2;
var r7 = dbCmd.Select <DerivatedFromDerivatedFromPerson>(qr => qr.Continent == "Asia");
Console.WriteLine("Expected:{0} Selected:{1} {2}", expected, r7.Count, expected == r7.Count?"OK":"********* FAILED *********");
var r8 = dbCmd.Select <DerivatedFromDerivatedFromPerson>(
exp => exp.
Where(qr => qr.BirthCityId != qr.JobCityId).
OrderBy(qr => qr.Continent));
Console.WriteLine("Expected:{0} Selected:{1} {2}", "America", r8.FirstOrDefault().Continent, "America" == r8.FirstOrDefault().Continent?"OK":"********* FAILED *********");
}
catch (Exception e) {
Console.WriteLine(e);
Console.WriteLine(e.Message);
}
}
Console.WriteLine("Press enter to return to main menu");
Console.ReadLine();
PaintMenu();
}
public override object OnGet(OfertaInformeRequest request)
{
try{
var visitor = ReadExtensions.CreateExpression <OfertaInforme>();
var predicate = PredicateBuilder.True <OfertaInforme>();
if (request.Desde != default(DateTime))
{
predicate = q => q.FechaEnvio >= request.Desde || q.FechaAceptacion >= request.Desde;
}
else
{
throw HttpError.Unauthorized("Debe Indicar la fecha de inicio del informe (Desde)");
}
if (request.Hasta != default(DateTime))
{
predicate = predicate.AndAlso(q => q.FechaEnvio <= request.Hasta || q.FechaAceptacion <= request.Hasta);
}
else
{
throw HttpError.Unauthorized("Debe Indicar la fecha de terminacion del informe (Hasta)");
}
predicate = predicate.AndAlso(q => q.FechaAnulado == null);
visitor.Where(predicate);
return(Factory.Execute(proxy => {
var ofertas = proxy.Get(visitor);
var resumenPorUsuario = (
from o in ofertas
group o by o.NombreEnviadoPor into newGroup1
from newGroup2 in
(
from o in newGroup1
group o by o.Id into gi
select new {
FechaEnvio = gi.Max(p => p.FechaEnvio),
Valor = gi.Sum(p => p.Valor * p.Cantidad),
FechaAceptacion = gi.Max(p => p.FechaAceptacion),
}
)
group newGroup2 by newGroup1.Key into g
select new OfertaAgrupada {
AgrupadaPor = g.Key,
CantidadEnviada = g.Sum(p => (p.FechaEnvio >= request.Desde && p.FechaEnvio <= request.Hasta)?1:0),
ValorEnviado = g.Sum(p => (p.FechaEnvio >= request.Desde && p.FechaEnvio <= request.Hasta)?p.Valor:0),
CantidadAceptada = g.Sum(p => (p.FechaAceptacion >= request.Desde && p.FechaAceptacion <= request.Hasta)?1:0),
ValorAceptado = g.Sum(p => (p.FechaAceptacion >= request.Desde && p.FechaAceptacion <= request.Hasta)?p.Valor:0)
}
).ToList();
HtmlGrid <OfertaAgrupada> gridUsuario =
BuildGridAgrupadoPor(resumenPorUsuario,
string.Format("Ofertas por Asesor Comercial<br/> Desde: {0} Hasta: {1}",
request.Desde.Format(), request.Hasta.Format()));
var resumenPorProcedimiento =
(from o in ofertas
group o by o.NombreProcedimiento into g
select new OfertaAgrupada {
AgrupadaPor = g.Key,
CantidadEnviada = g.Sum(p => (p.FechaEnvio >= request.Desde && p.FechaEnvio <= request.Hasta)?p.Cantidad:0),
ValorEnviado = g.Sum(p => (p.FechaEnvio >= request.Desde && p.FechaEnvio <= request.Hasta)?p.Valor * p.Cantidad:0),
CantidadAceptada = g.Sum(p => (p.FechaAceptacion >= request.Desde && p.FechaAceptacion <= request.Hasta)?p.Cantidad:0),
ValorAceptado = g.Sum(p => (p.FechaAceptacion >= request.Desde && p.FechaAceptacion <= request.Hasta)?p.Valor * p.Cantidad:0)
}).ToList();
HtmlGrid <OfertaAgrupada> gridProcedimiento =
BuildGridAgrupadoPor(resumenPorProcedimiento,
string.Format("Ofertas por Procedimiento<br/>Desde: {0} Hasta: {1}",
request.Desde.Format(), request.Hasta.Format()));
var resumenPorCliente = (
from o in ofertas
group o by o.NombreCliente into newGroup1
from newGroup2 in
(
from o in newGroup1
group o by o.Id into gi
select new {
FechaEnvio = gi.Max(p => p.FechaEnvio),
Valor = gi.Sum(p => p.Valor * p.Cantidad),
FechaAceptacion = gi.Max(p => p.FechaAceptacion),
}
)
group newGroup2 by newGroup1.Key into g
select new OfertaAgrupada {
AgrupadaPor = g.Key,
CantidadEnviada = g.Sum(p => (p.FechaEnvio >= request.Desde && p.FechaEnvio <= request.Hasta)?1:0),
ValorEnviado = g.Sum(p => (p.FechaEnvio >= request.Desde && p.FechaEnvio <= request.Hasta)?p.Valor:0),
CantidadAceptada = g.Sum(p => (p.FechaAceptacion >= request.Desde && p.FechaAceptacion <= request.Hasta)?1:0),
ValorAceptado = g.Sum(p => (p.FechaAceptacion >= request.Desde && p.FechaAceptacion <= request.Hasta)?p.Valor:0)
}
).ToList();
HtmlGrid <OfertaAgrupada> gridCliente =
BuildGridAgrupadoPor(resumenPorCliente,
string.Format("Ofertas por Cliente<br/>Desde: {0} Hasta: {1}",
request.Desde.Format(), request.Hasta.Format()));
//----------------------------------
var empresa = proxy.GetEmpresa();
var pedidos = (
from p in ofertas
group p by p.Id into g
select new OfertaInforme {
Valor = g.Sum(f => f.Valor * f.Cantidad),
NombreCliente = g.Max(f => f.NombreCliente),
Consecutivo = g.Max(f => f.Consecutivo),
FechaAceptacion = g.Max(f => f.FechaAceptacion),
NombreEnviadoPor = g.Max(f => f.NombreEnviadoPor),
}).ToList();
HtmlGrid <OfertaInforme> gridOfertas = new HtmlGrid <OfertaInforme>();
gridOfertas.DataSource = pedidos.OrderByDescending(f => f.Valor).ToList();
gridOfertas.Css = new CayitaGridGrey();
gridOfertas.Title = string.Format("Relacion de Ofertas Enviadas<br/>Desde: {0} Hasta: {1}",
request.Desde.Format(), request.Hasta.Format());
var gc = gridOfertas.CreateGridColumn();
gc.HeaderText = "Consecutivo";
gc.CellRenderFunc = (row, index) => row.Consecutivo.Format();
gc.FooterRenderFunc = () => pedidos.Count.Format();
gridOfertas.AddGridColum(gc);
gc = gridOfertas.CreateGridColumn();
gc.HeaderText = "Cliente";
gc.CellRenderFunc = (row, index) => row.NombreCliente;
gridOfertas.AddGridColum(gc);
gc = gridOfertas.CreateGridColumn();
gc.HeaderText = "Valor";
gc.CellRenderFunc = (row, index) => row.Valor.Format();
gc.FooterRenderFunc = () => pedidos.Sum(f => f.Valor).Format();
gridOfertas.AddGridColum(gc);
gc = gridOfertas.CreateGridColumn();
gc.HeaderText = "Aceptada?";
gc.CellRenderFunc = (row, index) => {
if (row.FechaAceptacion.HasValue)
{
var img = new HtmlImage {
Url = empresa.ApplicationHost + "/resources/icons/fam/accept.png"
};
var p = new HtmlParagragh {
Style = new HtmlStyle {
TextAlign = "center"
}
};
p.AddHtmlTag(img);
return p.ToString();
}
return "";
};
gc.FooterRenderFunc = () => pedidos.Count(f => f.FechaAceptacion.HasValue).Format();
gridOfertas.AddGridColum(gc);
gc = gridOfertas.CreateGridColumn();
gc.HeaderText = "Asesor";
gc.CellRenderFunc = (row, index) => row.NombreEnviadoPor;
gridOfertas.AddGridColum(gc);
return new HtmlResponse {
Html = gridUsuario.ToString() +
"<br/>" + gridCliente.ToString() +
"<br/>" + gridOfertas.ToString() +
"<br/>" + gridProcedimiento.ToString()
};
}));
}
catch (Exception e) {
return(HttpResponse.ErrorResult <Response <OfertaInforme> >(e, "GetOfertaInformeError"));
}
}
public override object OnGet(ClienteProcedimientoRequest request)
{
try{
Empresa empresa = new Empresa();
var visitor = ReadExtensions.CreateExpression <OfertaInforme>();
visitor.Where(q => q.IdCliente == request.Id);
var procs = Factory.Execute(proxy => {
empresa = proxy.GetEmpresa();
return(proxy.Get(visitor));
});
HtmlGrid <OfertaInforme> grid = new HtmlGrid <OfertaInforme>();
grid.DataSource = procs.OrderByDescending(f => f.Consecutivo).ToList();
grid.Css = new CayitaGridGrey();
grid.Title = "Procedimientos Ofertados";
var gc = grid.CreateGridColumn();
gc.HeaderText = "Consecutivo";
gc.CellRenderFunc = (row, index) => row.Consecutivo.Format();
gc.FooterRenderFunc = () => procs.Count.Format();
grid.AddGridColum(gc);
gc = grid.CreateGridColumn();
gc.HeaderText = "Procedimiento";
gc.CellRenderFunc = (row, index) => row.NombreProcedimiento;
grid.AddGridColum(gc);
gc = grid.CreateGridColumn();
gc.HeaderText = "Ctdad";
gc.CellRenderFunc = (row, index) => row.Cantidad.Format();
grid.AddGridColum(gc);
gc = grid.CreateGridColumn();
gc.HeaderText = "V/Unit $";
gc.CellRenderFunc = (row, index) => row.Valor.Format();
grid.AddGridColum(gc);
gc = grid.CreateGridColumn();
gc.HeaderText = "Total $";
gc.CellRenderFunc = (row, index) => (row.Valor * row.Cantidad).Format();
grid.AddGridColum(gc);
gc = grid.CreateGridColumn();
gc.HeaderText = "Enviada?";
gc.CellRenderFunc = (row, index) => {
if (row.FechaEnvio.HasValue)
{
var img = new HtmlImage {
Url = empresa.ApplicationHost + "/resources/icons/fam/accept.png"
};
var p = new HtmlParagragh {
Style = new HtmlStyle {
TextAlign = "center"
}
};
p.AddHtmlTag(img);
return(p.ToString());
}
return("");
};
grid.AddGridColum(gc);
gc = grid.CreateGridColumn();
gc.HeaderText = "Aceptada?";
gc.CellRenderFunc = (row, index) => {
if (row.FechaAceptacion.HasValue)
{
var img = new HtmlImage {
Url = empresa.ApplicationHost + "/resources/icons/fam/accept.png"
};
var p = new HtmlParagragh {
Style = new HtmlStyle {
TextAlign = "center"
}
};
p.AddHtmlTag(img);
return(p.ToString());
}
return("");
};
grid.AddGridColum(gc);
return(new HtmlResponse {
Html = grid.ToString()
});
}
catch (Exception e) {
return(HttpResponse.ErrorResult <Response <ClienteProcedimientoRequest> >(e, "GetClienteProcedimientoError"));
}
}
