Estou tentando usar o SUM para somar um atributo de uma entidade usando HQL do NHibernate mas sempre que executo a query retorna vazia. Estou seguindo o exemplo aqui mas ainda não consegui fazer. Como fazer isso ?
Estou tentando assim.
public IList<Conta> findAllContasReceber() {
ISession _session = getSession();
String SQL = "SELECT c.cliente, c.historico, c.dtLancamento, c.dtVencimento, SUM(c.valorPagar), " +
"c.valorAcrescimo, c.valorFinal, c.dtPagamento, c.tipoConta, c.planoConta, c.status, c.venda " +
"FROM Conta c WHERE (c.tipoConta = 1) AND (c.status = 0) GROUP BY c.dtVencimento, c.cliente ORDER BY c.dtVencimento";
IList<Conta> list = _session.CreateQuery(SQL)
.List<Conta>();
return list;
}
Entidade
[Serializable]
public class Conta {
public virtual long id { set; get; }
public virtual Cliente cliente { set; get; }
public virtual String historico { set; get; }
public virtual DateTime dtLancamento { set; get; }
public virtual DateTime dtVencimento { set; get; }
public virtual decimal valorPagar { set; get; } //total vendas
public virtual decimal valorAcrescimo { set; get; } //total acrescimo
public virtual decimal valorFinal { set; get; } //total pagar
public virtual DateTime dtPagamento { set; get; }
public virtual int tipoConta { set; get; } //1 receber, 2 pagar
public virtual PlanoDeConta planoConta { set; get; }
public virtual int status { set; get; } //0 ativa, 1 fechada, 2 cancelada, 3 aguardando pagamento
public virtual Venda venda { set; get; }
public Conta() {
}
}
Conexao
public class BDConnect {
private static ISessionFactory session;
private const String HOST = "127.0.0.1"; //ip servidor
private const String USER = "root"; //usuario banco
private const String PASSWORD = ""; //senha banco
private const String DB = "ctrlvendas_db"; //banco de dados
//cria conexao com banco de dados
private static ISessionFactory createConnection() {
//verifica se a sessao esta vazia se nao estiver retorna a sessao
if (session != null)
return session;
//configuracoes do banco de dados
FluentConfiguration _config = Fluently.Configure().Database(MySQLConfiguration.Standard.ConnectionString(
x => x.Server(HOST).
Username(USER).
Password(PASSWORD).
Database(DB)
))
.Mappings(m => m.FluentMappings.AddFromAssemblyOf<EmpresaMap>())
.Mappings(m => m.FluentMappings.AddFromAssemblyOf<UsuarioMap>())
.Mappings(m => m.FluentMappings.AddFromAssemblyOf<TerminalMap>())
.Mappings(m => m.FluentMappings.AddFromAssemblyOf<CategoriaProdutoMap>())
.Mappings(m => m.FluentMappings.AddFromAssemblyOf<UnidadeProdutoMap>())
.Mappings(m => m.FluentMappings.AddFromAssemblyOf<ProdutoMap>())
.Mappings(m => m.FluentMappings.AddFromAssemblyOf<EstoqueMap>())
.Mappings(m => m.FluentMappings.AddFromAssemblyOf<VendaMap>())
.Mappings(m => m.FluentMappings.AddFromAssemblyOf<ItemVendaMap>())
.Mappings(m => m.FluentMappings.AddFromAssemblyOf<CaixaMap>())
.Mappings(m => m.FluentMappings.AddFromAssemblyOf<ClienteMap>())
.Mappings(m => m.FluentMappings.AddFromAssemblyOf<ContaMap>())
.Mappings(m => m.FluentMappings.AddFromAssemblyOf<PlanoDeContaMap>())
.Mappings(m => m.FluentMappings.AddFromAssemblyOf<CrediarioMap>())
.ExposeConfiguration(cfg => new SchemaUpdate(cfg).Execute(false, true));
session = _config.BuildSessionFactory();
return session;
}
//abre a sessao para persistencia
public static ISession openSession() {
return createConnection().OpenSession();
}
IPersist
public interface IPersist <T>{
void insert(T obj);
void update(T obj);
void delete(T obj);
void saveOrUpdate(T obj);
T findObject(long id);
ISession getSession();
}
GenericDAO
public class GenericDAO<T> : IPersist<T> where T : class {
private ISession session;
public GenericDAO() {
session = BDConnect.openSession();
}
public void insert(T obj) {
ITransaction _transaction = session.BeginTransaction();
try {
session.Save(obj);
_transaction.Commit();
}catch (Exception e) {
if (!_transaction.WasCommitted) {
_transaction.Rollback();
}
MessageBox.Show("Erro tentando salvar: " + e.Message, "Aviso",
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public void update(T obj) {
ITransaction _transaction = session.BeginTransaction();
try {
session.Update(obj);
_transaction.Commit();
}catch (Exception e) {
if (!_transaction.WasCommitted) {
_transaction.Rollback();
}
MessageBox.Show("Erro tentando alterar: " + e.Message, "Aviso",
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public void delete(T obj) {
ITransaction _transaction = session.BeginTransaction();
try {
session.Delete(obj);
_transaction.Commit();
}catch (Exception e) {
if (!_transaction.WasCommitted) {
_transaction.Rollback();
}
MessageBox.Show("Erro tentando deletar: " + e.Message, "Aviso",
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public T findObject(long id) {
return session.Get<T>(id);
}
public void saveOrUpdate(T obj) {
ITransaction _transaction = session.BeginTransaction();
try {
session.SaveOrUpdate(obj);
_transaction.Commit();
}catch (Exception e) {
if (!_transaction.WasCommitted) {
_transaction.Rollback();
}
MessageBox.Show("Erro tentando salvar ou alterar: " + e.Message, "Aviso",
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public ISession getSession() {
return session;
}
}