Create a left Stock on the invoicing Example

[fabio]

Hi Javier,
Even if I am a very beginner with zero java experience, thanks to your great examples and your guides it was no difficult to make a first openxava simple project.

But at this point I would to ask you the following question:

I would to add to your invoicing example, a left stock for each product in Invoice details page; so how to do that in Openxava? (I would to store both buying and selling orders on the same table but any suggestion is welcome)
Many thanks and Best Regards
Fabio

[Javier Paniza]

Hi Fabio:

Really, it's not an OpenXava question but one of general programming.

A simple way to do it would be to add a stock property in Product, and when saving the Order you can substract from that stock, maybe with a @PrePersist/@PreUpdate method in Detail entity. Also you can add a validation if there is not enough stock.

---

Help others in this forum as I help you.

[fabio]

Hi Javier,
many thanks for your fast reply.
I see your suggestion and it would be great If I could have a validation of stock during the order creation; I think I should start to study your lesson n. 5 similar to the suggested price, right?

Well... but before proceed in deep, I would have many warehouses, each one can have different stock of the same product.

So, even if on the invoice details, I am asking to have the total remaining stock and of course the validation, my final goal would be to have the possibility to manage and following the products over the warehouses.

I know that is no at my level, but, I have done it in a raw way in Openoffice base by using all different tables:

• Purchase_order
• Purchase_details (with warehouse_ID for adding stock in a specific warehouse)
• Selling_order
• Selling_details (with warehouse_ID for substacting stock in a specific warehouse)
• Warehouses
• Warehose_movement (to move the items through different warehouses)

And then, by different queries I obtained:

• Total remaing stock/ product (Order Forms)
• Partial stock for each warehouse / product (Report)
• Partial left stock per warehouse / product/ per Date (a list of movements per warehouse with scalar quantities of stock (Report)

I found Openxava very interesting, so I decided to move my project on it, so I am asking your kindly suggestion as follows:

• What is the best simple way to do it in Openxava?
• Is the Hsql database engine ok or I need to do it in mysql?
• I am thinking to merge the Purchase_order and Selling_order tables in just General_order, do you think it should be better?
• For the report I need to study Ireport or there are other ways?

Many thanks in advance, I am very glad to know your project and I think that is suitable not only for programmers but for all the Access / Openoffice users with zero programming experience like me, just need to integrate some very, very basic information that for person like me still the biggest obstacle for the first approach. If you agree, It will my pleasure to write on them on this blog in the future.

My Best Regards and Happy New Year!
Fabio

[Federico Alcantara]

Hi Fabio

Happy New Year.

What is the best simple way to do it in Openxava?
I urge you to familiarize with JPA2 specification, as OX data management is
based on JPA standard. Also follow the "Openxava by Example" and get
acquainted with the available reference documentation in openxava.org.

Is the Hsql database engine ok or I need to do it in mysql?
HSQL is not recommended for production. Use mysql, postgresql or any other
compatible DBM. Keep in mind that the DBM you use should not affect the
code you write, as long as you use JPA.
El ene 1, 2016 2:38 p.m., "fabio" fabio12345678@users.sf.net escribió:

Hi Javier,
many thanks for your fast reply.
I see your suggestion and it would be great If I could have a validation
of stock during the order creation; I think I should start to study your
lesson n. 5 similar to the suggested price, right?

Well... but before proceed in deep, I would have many warehouses, each one
can have different stock of the same product.

So, even if on the invoice details, I am asking to have the total
remaining stock and of course the validation, my final goal would be to
have the possibility to manage and following the products over the
warehouses.

I know that is no at my level, but, I have done it in a raw way in
Openoffice base by using all different tables:

• Purchase_order
• Purchase_details (with warehouse_ID for adding stock in a specific
  warehouse)
• Selling_order
• Selling_details (with warehouse_ID for substacting stock in a
  specific warehouse)
• Wharehouses
• Wharehose_movement (to move the items throgh different wharehouses)

And then, by different queries I obtained:

• Total remaing stock/ product (Order Forms)
• Partial stock for each warehouse / product (Report)
• Partial left stock per warehouse / product/ per Date (a list of
  movements per warehouse with scalar quantities of stock (Report)

I found Openxava very interesting, so I decided to move my project on it,
so I am asking your kindly suggestion as follows:

• What is the best simple way to do it in Openxava?
• Is the Hsql database engine ok or I need to do it in mysql?
• I am thinking to merge the Purchase_order and Selling_order tables
  in just General_order, do you think it should be better?
• For the report I need to study Ireport or there are other ways?

Many thanks in advance, I am very glad to know your project and I think
that is suitable not only for programmers but for all the Access /
Openoffice users with zero programming experience like me, just need to
integrate some very, very basic information that for person like me still
the biggest obstacle for the first approach. If you agree, It will my
pleasure to write on them on this blog in the future.

My Best Regards and Happy New Year!
Fabio

---

Create a left Stock on the invoicing Example
https://sourceforge.net/p/openxava/discussion/419690/thread/f5734677/?limit=25#617f

---

Sent from sourceforge.net because you indicated interest in
https://sourceforge.net/p/openxava/discussion/419690/

To unsubscribe from further messages, please visit
https://sourceforge.net/auth/subscriptions/

[fabio]

Hi Federico,
many thanks for your reply.
I tried to search my question (stock in different warehouses) in the help forum but I wasn't able to find it in English.
About the JPA, of course i need to study but I found a huge amount of material that is confusing me; the only affordable documentation was in the openxava lessons but I didn't find such function.
Since I have no experience I think the best way should be to build it, step by step by integrating it in the invoicing example, so I asked for the above suggestions, could you please indicate the right way and from where I can start?
Many thanks and Happy New Year!
Fabio

[Javier Paniza]

Hi Fabio:

my final goal would be to have the possibility to manage and following the products over the warehouses ... I have done it in a raw way in Openoffice base

If you create a similar structure you already have in your new database and wrap it with classes it should work for you.

If you want do a more clean object oriented approach, maybe you can create a collection of stocks in Product, something like this.

public class Product {

    @OneToMany
    private Collection<Stock> stocks;

    public int getTotalStock() {
        // Do a loop over stocks and sum
    }

}

public class Stock {

    private int quantity;

    @ManyToOne
    private Warehouse;

}

That just an idea, maybe using a WarehouseMovement instead of Stock, as you have done, it's better for your case.

The OpenXava book is about how to convert Java classes into an application but it's not much about modeling. Modeling is converting your problem into a group of classes. There are many interesting books about modelins. This one is very good:
http://www.amazon.com/Java-Modeling-Color-With-UML/dp/013011510X

Federico has a lot of experience creating system like yours with OpenXava, perhaps he can do it a better tip for the stock by warehouses problem.

---

Help others in this forum as I help you.

[Federico Alcantara]

Hi Fabio,

It seems that you are talking about LOT management.

I see two approaches to solve that:
1) Establish the relationship from the LOT side, that is to have the
warehouse as a reference (ManyToOne).

2) The one suggested by Javier. Is a OneToMany relationship between the
Warehouse and Lot.

In the first one the transactions MUST be perform referencing the Lot. And
the operations should provoke updates up to the warehouse. These are the
benefits:
- You should have the current balance at warehouse and lot without any
calculation.
- You can perform simple queries for warehouse and lot balances.

But, not all is benefit, here are the setbacks:
- The warehouse must have summarizing fields.
- In multi user environments, the operations must be carefully written to
cope properly with concurrency.
- External updates of Lot balances (third party apps for example) will NOT
update the Warehouse balance (BAD).

Hope this helps.

Hi Fabio:

my final goal would be to have the possibility to manage and following the
products over the warehouses ... I have done it in a raw way in Openoffice
base

If you create a similar structure you already have in your new database and
wrap it with classes it should work for you.

If you want do a more clean object oriented approach, maybe you can create
a collection of stocks in Product, something like this.

public class Product {

@OneToMany
private Collection<Stock> stocks;

public int getTotalStock() {
    // Do a loop over stocks and sum
}

}

public class Stock {

private int quantity;

@ManyToOne
private Warehouse;

}

That just an idea, maybe using a WarehouseMovement instead of Stock, as you
have done, it's better for your case.

The OpenXava book is about how to convert Java classes into an application
but it's not much about modeling. Modeling is converting your problem into
a group of classes. There are many interesting books about modelins. This
one is very good:
http://www.amazon.com/Java-Modeling-Color-With-UML/dp/013011510X

Federico has a lot of experience creating system like yours with OpenXava,
perhaps he can do it a better tip for the stock by warehouses problem.

---

Help others in this forum as I help you.

Create a left Stock on the invoicing Example
https://sourceforge.net/p/openxava/discussion/419690/thread/f5734677/?limit=25#d391

---

Sent from sourceforge.net because you indicated interest in
https://sourceforge.net/p/openxava/discussion/419690/

To unsubscribe from further messages, please visit
https://sourceforge.net/auth/subscriptions/

[fabio]

Hi Federico,
thanks for your reply.
I think that your suggestion of stock management should be the best, but I see a little complicated to me:
at every purchasing order (IN), a lot should be assigned in automatic (or manual) and than, all the warehouse movement should get the reference of the remaining lot, but, for example if I want to move from A to B, how to manage it? Also, in the Invoice_details, the quantity field should depend on the lot availability through different warehouses..how to do that? It shoud be very nice but also compicated to me..anyway I am waiting for your suggestion!

By my side I could do it with 3 tables:
1.Purchasing_order_details with warehouse reference/product= IN (+)
2.Selling_Order_details with warehouse referece/product = OUT (-)
3. Warehouse_movemet, to move from A to B = IN (+) / OUT (-)
id_warehouse_from / id_warehouse_to/ id_product/ quantity that generates two movement per each record, one to substract the A and one to add to B.
This means that finally I need to have 4 data list.

In a scenery like this, I could have a list like Javier's example but with many tables instead one that I could sum per product.

So my question is, in openoffice for Warehouse movement I done it with two simple queries, than I make union, but in java I really don't know how to do!
Waiting for your appreciated answer,
Best Regards
Fabio

[fabio]

Hi Javier,
thanks a lot for your suggestion!
I needed a little time since I moved the project in Mysql, the difficulties I found are:
1. utf-8 encoding problems that I solved by configuring the tomcat context file in utf-8
2. the tables in mysql are not made automatically so, I needed to make the database schema with field 100% the same as declared in Openxava.
UPDATE: finally I am able to update schema, just having care about how to insert the right code in the: Persistence, Hibernate files, of course the context of tomcat is fundamental.
3. Every Entity need to be declared in tables and in fields even in the joincolumn
4. In Stock Entity I needed to add the products field, the code as following, I hope could be useful to others.
5. I was not able to make the total stock, but in table there is a function on Sum, so It is ok already.
6. The ID=0 in not recognized, in mysql I was able to do it, but in Openxava still not solved..

import org.openxava.annotations.*;
@Entity
@Table(name = "stock")
public class Stock {

@Id  @GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name="id")
private int ID;

@Column(name="qty")
private int qty;
@ManyToOne
@JoinColumn(name="magazzino")
@Required
private Magazzino magazzino;

@ManyToOne
@JoinColumn(name="prodotto")
@Required
private Articoli articoli;

It works fine, in the product table, I can have a list and a sum of the related stock; it is simple stock management!

Moreover, in the stock entity, it shoud be better if I can see the Products and the Wharehouse name in list mode, how to do it?
UPDATE: I have done it by using the customization directly in the browser, just click on the small wrench and than you can add a lot of related fields..it is great!

So, right now, since I would need more tables just as above explained, what is the right way to collect all the data from different mysql tables in just one?

Many thanks & Best Regards
Fabio

[Javier Paniza]

Hi Fabio:

if I can see the Products and the Wharehouse name in list mode, how to do it?

You can use @Tab(properties=) for list mode and @ListProperties for collections.

I would need more tables just as above explained, what is the right way to collect all the data from different mysql tables in just one?

Entities references other entities, with that references you can get anything you want. Put here the code of your entities and the data you want to get, and where (a list or a collection), and I'll give you some tip.

---

Help others in this forum as I help you.

[fabio]

Hi Javier,
many thanks for your help, that's great!
I just trying to use @Tab, but I'am not able to combine with @Table and JoinColumn;
as soon I will post all code.

1. Articoli Entity code:

package org.openxava.gestione.model;

import java.io.;
import java.math.;
import java.util.*;

import javax.persistence.*;

import org.openxava.annotations.*;

@Entity
@Table(name = "articoli")
public class Articoli {

@Id  @GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name="ID")
private Integer ID;

@Column(name="nome")
private String nome;

@Column(name="descr")
private String descrizione;

  @Column(name="codice")
  private String codice;

 @ManyToOne
 @JoinColumn(name="fornitore")
 @Required
    private Fornitori fornitori;

---

[Federico Alcantara]

Hi Fabio

The operations are quite simple. You will do the operations on the JPA
callbacks associated with LOT.

For example, create a listener class with the following methods:

@PrePersist
public void onPrePersist(Lot lot){
updateWarehouse(lot, lot.getQty());
}

@PreRemove
public void onPreRemove(Lot lot) {
updateWarehouse(lot,
lot.getQty().negate());
}

private void updateWarehouse(Lot lot,
BigDecimal qty) {
BigDecimal qty = lot.getWarehouse().getQty();
qty = qty.add(lot.qty());
lot.getWarehouse().setQty(qty);
}

For updates you must have a transient property in lot for holding previous
value, and load it on the load callback.

@PreLoad
public void onPreLoad(Lot lot) {
lot.setPreviousQty(lot.getQty());
}

@PreUpdate
public void onPreUpdate(Lot lot) {
updateWarehouse(lot,
lot.getPreviousQty().negate());
updateWarehouse(lot,
lot.getQty());
}

The created class must be declared in the Lot entity with the annotation
@Listeners.

What will happen is that every time you create, update or delete a Lot
object the warehouse qty will be properly updated.

Keep in mind the you should improve your code for avoiding
NullPointerException by either asking for null or make the get methods to
never return a null value.

Hope this helps,

Federico
El ene 7, 2016 2:29 p.m., "fabio" fabio12345678@users.sf.net escribió:

Hi Javier,
many thanks for your help, that's great!
I just trying to use @Tab, but I'am not able to combine with @Table and
JoinColumn;
as soon I will post all code.

1. Articoli Entity code:

package org.openxava.gestione.model;

import java.io.
; import java.math.;
import java.util.*;

import javax.persistence.*;

import org.openxava.annotations.*;

@Entity
@Table(name = "articoli")
public class Articoli {

@Id @GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name="ID")
private Integer ID;

@Column(name="nome")
private String nome;

@Column(name="descr")
private String descrizione;

@Column(name="codice")
private String codice;

@ManyToOne
@JoinColumn(name="fornitore")
@Required
private Fornitori fornitori;

------------------------------

Create a left Stock on the invoicing Example
https://sourceforge.net/p/openxava/discussion/419690/thread/f5734677/?limit=25#c7c1

---

Sent from sourceforge.net because you indicated interest in
https://sourceforge.net/p/openxava/discussion/419690/

To unsubscribe from further messages, please visit
https://sourceforge.net/auth/subscriptions/

[fabio]

package org.openxava.gestione.model;

import java.util.*;

import javax.persistence.*;

import org.openxava.annotations.*;

@Entity
@Table(name = "fornitori")
public class Fornitori {

@Id  @GeneratedValue(strategy = GenerationType.IDENTITY)
@Hidden
@Column(name="ID")
private int ID;

@Column(name="nome")
private String nome;

@OneToMany
@ListProperties ("ID,nome,codice,cartoni")
private Collection<Articoli> articoli;

Movimenti Magazzino: for moving the Items from A to B:

package org.openxava.gestione.model;
import javax.persistence.;
import org.openxava.annotations.;
@Entity
@Table(name = "movimenti_mag")
public class Movimenti_mag {

@Id  @GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name="id_mov_mag")
private int ID;

@Column(name="qty")
private int qty;
@ManyToOne
@JoinColumn(name="id_from")
@Required
private Magazzino from;

@ManyToOne
@JoinColumn(name="id_to")
@Required
private Magazzino to;

@ManyToOne
@JoinColumn(name="id_art")
@Required
private Articoli articoli;

Magazzino - Wharehouses:
package org.openxava.gestione.model;
import javax.persistence.;
import org.openxava.annotations.;
@Entity
@Table(name = "magazzino")
public class Magazzino {

@Id  @GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name="id_mag")
private int ID;

@Column(name="magazzino")
private String magazzino;

[fabio]

Hi Federico,
If I understand well, I need to create a lot table related with product reference and then upgrade it at every movement: buy_detail, selling_detail, move to/from. What is not clear for me is that: stock field must be in product or in every movement I do? Because I would have the total left stock, the partial left stock for every warehouse (many) and the relative stock / date /warehouse.
I really appreciate your suggestion, but at the moment I am not familiar with jpa language, of course I need to study a lot, but I kindly ask if you could post a simple example,
Kindly Regards
Fabio

[Federico Alcantara]

Hi Fabio:

I gave you a simple scenario. You should add more entities/properties as
needed for your requirements. Following the first advice, you can (and
should) relate warehouse with product, at the end you will have a
relationship tree as this:

Lot -> Warehouse -> Product.

Note the implications here, for each Product entity you can have many
Warehouses, for each Warehouse you will have many Lot. So if you have 10
Products and 10 Warehouses with 10 Lots each, you will have 100 Warehouse
entities, and 1000 lot entities.

Again the operations are quite simple and should only occur at transaction
level. When you add, remove or update a Lot referred transaction you will
update Warehouse AND also product.

If, for example, you define these properties in Lot, Warehouse and Products:

enteredQty, outputQty, qtyAtHand.

For the sake of the example, let's assume that we are handling a single
Product A that can be in Warehouses 1 and 2, each with Lot X and Y.

Scenarios:
1- Buy 2 for Lot X in Warehouse 1.
For Lot X:
enteredQty: 2, outputQty: 0, qtyAtHand: 2
For Lot Y:
enteredQty: 0, outputQty: 0, qtyAtHand: 0
For Warehouse 1:
enteredQty: 2, outputQty: 0, qtyAtHand: 2
For Warehouse 2:
enteredQty: 0, outputQty: 0, qtyAtHand: 0
For Product:
enteredQty: 2, outputQty: 0, qtyAtHand: 2

All seems the same but this is what happens if:
2- Buy 3 for Lot X in Warehouse 2.
For Lot X:
enteredQty: 5, outputQty: 0, qtyAtHand: 5
For Lot Y:
enteredQty: 0, outputQty: 0, qtyAtHand: 0
For Warehouse 1:
enteredQty: 2, outputQty: 0, qtyAtHand: 2
For Warehouse 2:
enteredQty: 3, outputQty: 0, qtyAtHand: 3
For Product:
enteredQty: 5, outputQty: 0, qtyAtHand: 5

Can you see what happened?
And if:
3- Buy 4 for Lot Y in Warehouse 1
For Lot X:
enteredQty: 2, outputQty: 0, qtyAtHand: 2
For Lot Y:
enteredQty: 4, outputQty: 0, qtyAtHand: 4
For Warehouse 1:
enteredQty: 6, outputQty: 0, qtyAtHand: 6
For Warehouse 2:
enteredQty: 3, outputQty: 0, qtyAtHand: 3
For Product:
enteredQty: 9, outputQty: 0, qtyAtHand: 9

At this point you will obtained the right balance for any of the cases. If
you want to know how many are left in Lot, Warehouse or Product 1 or 2 you
will read it from qtyAtHand property without any expensive query or
calculation.

Hope this helps
El ene 7, 2016 5:15 p.m., "fabio" fabio12345678@users.sf.net escribió:

Hi Federico,
If I understand well, I need to create a lot table related with product
reference and then upgrade it at every movement: buy, selling, move
to/from. What is not cleat form me is that stock field must be in product
or in every movement I do? Because I would have the total left stock, the
partial left stock for every warehouse (many) and the relative stock / date
/warehouse.
I really appreciate your suggestion, but at the moment I am not familiar
with jpa language, of course I need to study a lot, but I kindly ask if you
could post a simple example,
Kindly Regards
Fabio

---

Create a left Stock on the invoicing Example
https://sourceforge.net/p/openxava/discussion/419690/thread/f5734677/?limit=25#6ffd

---

Sent from sourceforge.net because you indicated interest in
https://sourceforge.net/p/openxava/discussion/419690/

To unsubscribe from further messages, please visit
https://sourceforge.net/auth/subscriptions/

[fabio]

Hi Federico,
thanks for your suggestion about the lot management but actually I would keep the above tables, so for me should be better the calculation way, maybe by code or by report, could you please indicate a starting point?
Best Regards
Fabio

[fabio]

Hi Javier,
sorry for late,since I was not ready, I am going crazy to create the other entities:

package org.openxava.gestione.model;
import javax.persistence.;
import org.openxava.annotations.;
@Entity
@Table(name = "clienti")
public class Clienti {

@Id  @GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name="ID")
private int ID;

@Column(name="nome")
private String nome;

public int getID() {
    return ID;
}

public void setID(int iD) {
    ID = iD;
}

public String getNome() {
    return nome;
}

public void setNome(String nome) {
    this.nome = nome;
}

}

package org.openxava.gestione.model;

import java.math.;
import java.util.;

import javax.persistence.*;

import org.openxava.annotations.;
import org.openxava.model.;

@Entity
@Table(name = "ven_gen")
public class Invoice {

    @Id  @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name="id_ven_gen")
    private int ID;

    @Column(name="n_ven_gen")
    private String numerovend;

    @ManyToOne(fetch=FetchType.LAZY,optional=false)
     @JoinColumn(name="id_cliente")
    private Clienti clienti;

    @OneToMany(mappedBy = "parent", cascade = CascadeType.ALL)
    @ListProperties("articoli.nome,articoli.codice,quantity+,unitPrice+,amount[invoice.amountSum]")
    private Collection<Invoicedetail> details = new ArrayList<Invoicedetail>();

    public String getNumerovend() {
        return numerovend;
    }

    public void setNumerovend(String numerovend) {
        this.numerovend = numerovend;
    }

    public int getID() {
        return ID;
    }

    public Collection<Invoicedetail> getDetails() {
        return details;
    }

    public void setDetails(Collection<Invoicedetail> details) {
        this.details = details;
    }

    public void setID(int iD) {
        ID = iD;
    }

    public Clienti getClienti() {
        return clienti;
    }

    public void setClienti(Clienti clienti) {
        this.clienti = clienti;
    }

}

package org.openxava.gestione.model;
import java.math.;
import javax.persistence.;
import org.openxava.annotations.*;
@Entity
@Table(name = "vendite")
public class Invoicedetail {

@ManyToOne
@JoinColumn(name="id_vend_gen")
private Invoice parent;

@Id  @GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name="ID")
private int ID;

@Column(name="qty")
private int quantity;

@Column(name="prezzo")
private BigDecimal unitPrice;

@ManyToOne(optional=false, fetch=FetchType.LAZY)
@JoinColumn(name="idarticoli")
private Articoli articoli;

@Depends("unitPrice, quantity") 
public BigDecimal getAmount() {
    return new BigDecimal(getQuantity()).multiply(getUnitPrice()); 
}

public Invoice getParent() {
    return parent;
}
public void setParent(Invoice parent) {
    this.parent = parent;
}

public int getQuantity() {
    return quantity;
}

public void setQuantity(int quantity) {
    this.quantity = quantity;
}

public BigDecimal getUnitPrice() {
    return unitPrice == null?new BigDecimal("0.00"):unitPrice;
}

public void setUnitPrice(BigDecimal unitPrice) {
    this.unitPrice = unitPrice;
}

public int getID() {
    return ID;
}

public void setID(int iD) {
    ID = iD;
}

public Articoli getArticoli() {
    return articoli;
}

public void setArticoli(Articoli articoli) {
    this.articoli = articoli;
}

}

About the invoice, I am not able yet to get the total amount also I think that the @ElementCollection is nicest but at now I am not able to do it..I still studing and trying a lot..as soon I will send the remaining entities and the schema,
thanks for your support!
Fabio

[Javier Paniza]

Hi Fabio:

I am not able yet to get the total amount

You have to create that as an calculated property in Invoice. That is explained in the course:
http://openxava.wikispaces.com/basic-business-logic_en

---

Help others in this forum as I help you.

[fabio]

Hi Javier,
herein the remaining entities:

Purchase:
@Entity
@Table(name = "acq_gen")
public class Purchase {

    @Id  @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name="id_acq_gen")
    private int ID;

    @Column(name="n_acqu_gen")
    private String numeroacq;

    @Column(name="stato_acq")
    private String stato;

    @Column(name="data_part")
    private Date datapart;

    @Column(name="data_arr")
    private Date dataarr;

    @ManyToOne(fetch=FetchType.LAZY,optional=false)
     @JoinColumn(name="id_forn")
    private Fornitori fornitori;

    @OneToMany(mappedBy = "parent1", cascade = CascadeType.ALL)
    @ListProperties("articoli.nome,articoli.codice,articoli.cartoni,quantity,unitPrice,amount€")
    private Collection<Purchasedetail> details;

    public int getID() {
        return ID;
    }

    public Date getDataarr() {
        return dataarr;
    }

    public void setDataarr(Date dataarr) {
        this.dataarr = dataarr;
    }

    public void setID(int iD) {
        ID = iD;
    }

    public String getNumeroacq() {
        return numeroacq;
    }

    public void setNumeroacq(String numeroacq) {
        this.numeroacq = numeroacq;
    }

    public String getStato() {
        return stato;
    }

    public Date getDatapart() {
        return datapart;
    }

    public void setDatapart(Date datapart) {
        this.datapart = datapart;
    }

    public void setStato(String stato) {
        this.stato = stato;
    }

    public Fornitori getFornitori() {
        return fornitori;
    }

    public void setFornitori(Fornitori fornitori) {
        this.fornitori = fornitori;
    }

    public Collection<Purchasedetail> getDetails() {
        return details;
    }

    public void setDetails(Collection<Purchasedetail> details) {
        this.details = details;
    }

}

Purchasedetail

@Entity
@Table(name = "acquisti")
public class Purchasedetail {

@ManyToOne
@JoinColumn(name="idacqu_gen")
private Purchase parent1;

public Purchase getParent1() {
    return parent1;
}

public void setParent1(Purchase parent1) {
    this.parent1 = parent1;
}

@Id  @GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name="ID")
private int ID;

@Column(name="qty")
private int quantity;

@Column(name="prezzo")
private BigDecimal unitPrice;

@ManyToOne(optional=false, fetch=FetchType.LAZY)
@JoinColumn(name="idarticolo")
private Articoli articoli;

@ManyToOne
@JoinColumn(name="id_mag")
@Required
private Magazzino magazzino;

@Depends("unitPrice, quantity") 
public BigDecimal getAmount€() {
    return new BigDecimal(getQuantity()).multiply(getUnitPrice()); 
}

public int getID() {
    return ID;
}

public void setID(int iD) {
    ID = iD;
}

public int getQuantity() {
    return quantity;
}

public void setQuantity(int quantity) {
    this.quantity = quantity;
}

public BigDecimal getUnitPrice() {
    return unitPrice;
}

public void setUnitPrice(BigDecimal unitPrice) {
    this.unitPrice = unitPrice;
}

public Articoli getArticoli() {
    return articoli;
}

public void setArticoli(Articoli articoli) {
    this.articoli = articoli;
}

public Magazzino getMagazzino() {
    return magazzino;
}

public void setMagazzino(Magazzino magazzino) {
    this.magazzino = magazzino;
}

}

Well, as following what I need to do:

1- List by product, purchase + sales:
From Purchasedetail/id_articolo (IN) + (positive)
From Invoicedetail/idarticoli (OUT) - (negative)
and than sum for the left stock/product
Ex. 1:
articoli.ID=1
data /Purchasedetail/ quantity +10 / warehouse1 (not considering now)
data /Purchasedetail/ quantity +10 / warehouse2 (not considering now)
data /Invoicedetail / quantity - 1 / warehouse1 (not considering now)
data /Invoicedetail / quantity - 2 / warehouse2 (not considering now)
total quantity ------------------------ 17 / warehouse general

2- list by product, warehouse movement :
From Movimenti_mag/id_art/id_from (+)
From Movimenti_mag/id_art/id_to (-)
Ex.2
Table: movimenti_mag
id_movimenti_mag / data / id_from /id_to /qty
1------------------------/ data/ warehouse1/ warehouse2/ 50pcs
QUERY or wherever by combining id_from/id_to into FROM_TO generating 2 lines
id_movimenti_mag / data / FROM_TO /qty
1------------------------/ data/ warehouse1/- 5pcs
1------------------------/ data/ warehouse2/+ 5pcs

3- Complete list by product by merging purchase + sales + warehouse movement
ID/ data / quantity(+-) /Warehouse
1/ data / +10 / warehouse1 (considering now)
1/ data / +10 / warehouse2 (considering now)
1/ data / -1 / warehouse1 (considering now)
1/ data / -2 / warehouse2 (considering now)
1/ data / -5 / warehouse1 (considering now)
1/ data / +5 / warehouse2 (considering now)
total quantity 17

4 Partial sum / warehouse 1
ID/ data / quantity(+-) /Warehouse
1/ data / +10 / warehouse1 /
1/ data / -1 / warehouse1 /
1/ data / -5 / warehouse1 /
Total -------------4

5 Scalar Quantity / warehouse 1
ID/ data / quantity(+-) /Warehouse/ Scalar quanitity
1/ data / +10 / warehouse1 / 10
1/ data / -1 / warehouse1 / 9
1/ data / -5 / warehouse1 / 4

Please let me know if everything is clear,
Looking forward your precious reply,
Many thanks & Best Regards
Fabio

[Javier Paniza]

Hi Fabio:

For the case 1 a way it's to define a collection in Articoli of Purchasedetail and other of Purchasedetail. Also add a getTotalQuantity() calculated property. Then show he three things in the view. No more. Something like this:

public Articoli {

    @OneToMany(mappedBy="articoli")
    private Collection<InvoiceDetail> invoiceDetails;

    @OneToMany(mappedBy="articoli")
    private Collection<PurchaseDetail> invoiceDetails;

    public BigDecimal getTotalQuantity() {
        // Here calculate and return the total quantity
    }

}

Another way could be to use inheritance for the detail:

public Detail {
}

public PurcharseDetail extends Detail {
}

public InvoiceDetail extends Detail {
}

In this case you only need a collection in Articoli and no calculate property

    public Articoli {

    @OneToMany(mappedBy="articoli")
    @ListProperties("parent.data, parent.customer.name, unitPrice, amount+")
    private Collection<Detail> details;

}

Thanks to the + after amount in @ListProperties you do not need write the getTotalQuantity() calculated property.

The above code is incomplete, not compiled and not tested, it's just to give you the idea.

Try to solve the fisrt point and with the experience adquired try to deal with the next by yourself. If you have any problem don't doubt in ask again.

---

Help others in this forum as I help you.

[fabio]

Hi Javier,
Many thanks for your helping,

public Articoli {

@OneToMany(mappedBy="articoli")
private Collection<InvoiceDetail> invoiceDetails;

@OneToMany(mappedBy="articoli")
private Collection<PurchaseDetail> invoiceDetails

I think the last invoiceDetails should be purchaseDetails, otherwise I am getting errors; Am I correct?
I am trying to use @View /@Annotation but I still not able to combine the two collection in one list.
By following http://openxava.wikispaces.com/view_en I could use also Elementcollection but I am getting a lot of errors; could you please be so kind to post me an example on how to combine the two list in one?

Regarding the inheritance, is very interesting: http://openxava.wikispaces.com/inheritance_en
if I understand well,I should create another abstract called detail where all the data from purchase and invoice are collected;
Please let me know If I am correct.

Hoping in your reply,
Kindly regards
Fabio

[Javier Paniza]

Hi Fabio:

I think the last invoiceDetails should be purchaseDetails, otherwise I am getting errors; Am I correct?

You're right

could you please be so kind to post me an example on how to combine the two list in one?

That is not possible. You can use inheritance or creatating a third calculated collection that join the both collection using code. Why not just to leave two collections? Maybe it's not needed to make an exacto copy of the old system but one with the same functionality. My advice is to show the application to the user with two collection and if he complains then do the additional work to join them.

---

Help others in this forum as I help you.

[fabio]

Hi Javier,
maybe I was not clear: I am the developer and the final user, so as explained at beginning I need to have at least the calculation of the left stock in the invoice form.
Of course I am not a programmer but if I solve this small question then I can continue to study openxava and in the future I can consider the Pro version that can works also on mobile.
Anyway, many thanks for your further explanation and for your great job; any additional suggestion by your side will be very appreciated.
Best Regards
Fabio

[Javier Paniza]

Hi Fabio:

Try to implement inheritance alteranative (for example)and if you have any problem just ask here.

---

Help others in this forum as I help you.

[fabio]

Hi Javier,
meanwhile I studying the inheritance, I solved the Total amount!
Herein the code I used for the Purchase Entity:

@Stereotype("MONEY")
public BigDecimal getBaseAmount() {
BigDecimal result = new BigDecimal("0.00");
for (Purchasedetail detail: getDetails()) {
result = result.add(detail.getAmount€());
}
return result;
}

For the PurchaseDetail:

@Depends("unitPrice, quantity")
public BigDecimal getAmount€() {
return new BigDecimal(getQuantity()).multiply(getUnitPrice());
}

Thanks for your suggestions!

Moreover, I am looking at lesson 5 and I am trying to calculate the partial cartons that are in the Articoli Entity while the quantity is in the details entity, at now I am able to show it in the purchase details (list), but I still not able to calculate it;
what shoud be the right way?

Many thanks in advance
Fabio