Skip to content

Instantly share code, notes, and snippets.

@smithh032772

smithh032772/ORDERS_order_template_tables.sql

Created November 22, 2012 16:43
Show Gist options
  • Save smithh032772/4132060 to your computer and use it in GitHub Desktop.
Save smithh032772/4132060 to your computer and use it in GitHub Desktop.
Download ZIP
ORDERS and order_template tables, foreign key, entity and EJB/facade beans
Raw
Orders.java
/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/
package jpa.entities;
import java.io.Serializable;
import java.util.Collection;
import java.util.Date;
import javax.persistence.*;
import javax.validation.constraints.NotNull;
import javax.validation.constraints.Size;
import javax.xml.bind.annotation.XmlRootElement;
import javax.xml.bind.annotation.XmlTransient;
/**
*
* @author Administrator
*/
@Entity
@Table(name = "ORDERS")
@XmlRootElement
@NamedQueries({
@NamedQuery(name = "Orders.findAll", query = "SELECT o FROM Orders o"),
@NamedQuery(name = "Orders.findByOrderId", query = "SELECT o FROM Orders o WHERE o.orderId = :orderId"),
@NamedQuery(name = "Orders.findByTripDateTime", query = "SELECT o FROM Orders o WHERE o.tripDateTime = :tripDateTime"),
@NamedQuery(name = "Orders.findByReportDateTime", query = "SELECT o FROM Orders o WHERE o.reportDateTime = :reportDateTime"),
@NamedQuery(name = "Orders.findByReturnDateTime", query = "SELECT o FROM Orders o WHERE o.returnDateTime = :returnDateTime"),
@NamedQuery(name = "Orders.findByCustomerId", query = "SELECT o FROM Orders o WHERE o.customerId = :customerId"),
@NamedQuery(name = "Orders.findByTotalCost", query = "SELECT o FROM Orders o WHERE o.totalCost = :totalCost"),
@NamedQuery(name = "Orders.findByDiscountRate", query = "SELECT o FROM Orders o WHERE o.discountRate = :discountRate"),
@NamedQuery(name = "Orders.findByDiscount", query = "SELECT o FROM Orders o WHERE o.discount = :discount"),
@NamedQuery(name = "Orders.findByBalance", query = "SELECT o FROM Orders o WHERE o.balance = :balance"),
@NamedQuery(name = "Orders.findByDepositPaid", query = "SELECT o FROM Orders o WHERE o.depositPaid = :depositPaid"),
@NamedQuery(name = "Orders.findByDepositPaidDate", query = "SELECT o FROM Orders o WHERE o.depositPaidDate = :depositPaidDate"),
@NamedQuery(name = "Orders.findByPaidInFull", query = "SELECT o FROM Orders o WHERE o.paidInFull = :paidInFull"),
@NamedQuery(name = "Orders.findByPaidInFullDate", query = "SELECT o FROM Orders o WHERE o.paidInFullDate = :paidInFullDate"),
@NamedQuery(name = "Orders.findByCancelled", query = "SELECT o FROM Orders o WHERE o.cancelled = :cancelled"),
@NamedQuery(name = "Orders.findByCancelledDate", query = "SELECT o FROM Orders o WHERE o.cancelledDate = :cancelledDate"),
@NamedQuery(name = "Orders.findByConfirmed", query = "SELECT o FROM Orders o WHERE o.confirmed = :confirmed"),
@NamedQuery(name = "Orders.findByConfirmedDate", query = "SELECT o FROM Orders o WHERE o.confirmedDate = :confirmedDate"),
@NamedQuery(name = "Orders.findByContractSent", query = "SELECT o FROM Orders o WHERE o.contractSent = :contractSent"),
@NamedQuery(name = "Orders.findByContractSentDate", query = "SELECT o FROM Orders o WHERE o.contractSentDate = :contractSentDate"),
@NamedQuery(name = "Orders.findByInvoiceSent", query = "SELECT o FROM Orders o WHERE o.invoiceSent = :invoiceSent"),
@NamedQuery(name = "Orders.findByInvoiceSentDate", query = "SELECT o FROM Orders o WHERE o.invoiceSentDate = :invoiceSentDate"),
@NamedQuery(name = "Orders.findByQuoteSent", query = "SELECT o FROM Orders o WHERE o.quoteSent = :quoteSent"),
@NamedQuery(name = "Orders.findByQuoteSentDate", query = "SELECT o FROM Orders o WHERE o.quoteSentDate = :quoteSentDate"),
@NamedQuery(name = "Orders.findByPassengers", query = "SELECT o FROM Orders o WHERE o.passengers = :passengers"),
@NamedQuery(name = "Orders.findByCreatedBy", query = "SELECT o FROM Orders o WHERE o.createdBy = :createdBy"),
@NamedQuery(name = "Orders.findByCreatedDt", query = "SELECT o FROM Orders o WHERE o.createdDt = :createdDt"),
@NamedQuery(name = "Orders.findByChangedDt", query = "SELECT o FROM Orders o WHERE o.changedDt = :changedDt"),
// 2011-08-02 added removeAll
@NamedQuery(name = "Orders.removeAll", query = "DELETE FROM Orders o")})
public class Orders implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Basic(optional = false)
@NotNull
@Column(name = "ORDER_ID")
private Integer orderId;
@Basic(optional = false)
@NotNull
@Column(name = "TRIP_DATE_TIME")
@Temporal(TemporalType.TIMESTAMP)
private Date tripDateTime;
@Column(name = "REPORT_DATE_TIME")
@Temporal(TemporalType.TIMESTAMP)
private Date reportDateTime;
@Column(name = "RETURN_DATE_TIME")
@Temporal(TemporalType.TIMESTAMP)
private Date returnDateTime;
@JoinColumn(name = "CUSTOMER_ID", referencedColumnName = "CUSTOMER_ID")
@ManyToOne
private Customer customerId;
@JoinColumn(name = "TEMPLATE_ID", referencedColumnName = "TEMPLATE_ID")
@ManyToOne
private OrderTemplate templateId;
// @Max(value=?) @Min(value=?)//if you know range of your decimal fields consider using these annotations to enforce field validation
@Column(name = "TOTAL_COST")
private Double totalCost;
@Column(name = "DEPOSIT")
private Double deposit;
@Column(name = "DISCOUNT_RATE")
private Double discountRate;
@Column(name = "DISCOUNT")
private Double discount;
@Column(name = "BALANCE")
private Double balance;
@Column(name = "DEPOSIT_PAID")
private Character depositPaid;
@Column(name = "DEPOSIT_PAID_DATE")
@Temporal(TemporalType.DATE)
private Date depositPaidDate;
@Column(name = "PAID_IN_FULL")
private Character paidInFull;
@Column(name = "PAID_IN_FULL_DATE")
@Temporal(TemporalType.DATE)
private Date paidInFullDate;
@Column(name = "CANCELLED")
private Character cancelled;
@Column(name = "CANCELLED_DATE")
@Temporal(TemporalType.DATE)
private Date cancelledDate;
@Column(name = "CONFIRMED")
private Character confirmed;
@Column(name = "CONFIRMED_DATE")
@Temporal(TemporalType.DATE)
private Date confirmedDate;
@Column(name = "CONTRACT_SENT")
private Character contractSent;
@Column(name = "CONTRACT_SENT_DATE")
@Temporal(TemporalType.DATE)
private Date contractSentDate;
@Column(name = "INVOICE_SENT")
private Character invoiceSent;
@Column(name = "INVOICE_SENT_DATE")
@Temporal(TemporalType.DATE)
private Date invoiceSentDate;
@Column(name = "QUOTE_SENT")
private Character quoteSent;
@Column(name = "QUOTE_SENT_DATE")
@Temporal(TemporalType.DATE)
private Date quoteSentDate;
@Column(name = "PASSENGERS")
private Short passengers;
@Size(max = 25)
@Column(name = "CREATED_BY")
private String createdBy;
@Column(name = "INTRASTATE")
private Character intrastate;
@Column(name = "CREATED_DT")
@Temporal(TemporalType.TIMESTAMP)
private Date createdDt;
@Column(name = "CHANGED_DT")
@Temporal(TemporalType.TIMESTAMP)
private Date changedDt;
@ManyToMany(mappedBy = "orders")
private Collection<PointOfContact> pointOfContacts;
@ManyToMany(mappedBy = "orders")
private Collection<Flight> flights;
@ManyToMany(mappedBy = "orders")
private Collection<MealStop> mealStops;
@ManyToMany(mappedBy = "orders")
private Collection<Attraction> attractions;
@ManyToMany(mappedBy = "orders")
private Collection<OrderCostDetails> orderCostDetails;
@ManyToMany(mappedBy = "orders")
private Collection<Hotel> hotels;
@ManyToMany(mappedBy = "orders")
private Collection<LocalBus> localBuses;
@ManyToMany(mappedBy = "orders")
private Collection<Destination> destinations;
@ManyToMany(mappedBy = "orders")
private Collection<CreditCard> creditCards;
@ManyToMany(mappedBy = "orders")
private Collection<Origin> origins;
@JoinColumn(name = "ROUTES_ID", referencedColumnName = "ROUTES_ID")
@ManyToOne
private Routes routesId;
@JoinColumn(name = "NOTES_ID", referencedColumnName = "NOTES_ID")
@ManyToOne
private OrderNotes notesId;
@JoinColumn(name = "DETAILS_ID", referencedColumnName = "DETAILS_ID")
@ManyToOne
private OrderDetails detailsId;
@JoinColumn(name = "BANK_FOR_DEPOSIT_ID", referencedColumnName = "BANK_ID")
@ManyToOne
private Bank bankForDepositId;
@JoinColumn(name = "BANK_FOR_BALANCE_ID", referencedColumnName = "BANK_ID")
@ManyToOne
private Bank bankForBalanceId;
@JoinColumn(name = "METHOD_OF_PAYMENT_FOR_DEPOSIT_ID", referencedColumnName = "METHOD_OF_PAYMENT_ID")
@ManyToOne
private MethodOfPayment methodOfPaymentForDepositId;
@JoinColumn(name = "METHOD_OF_PAYMENT_FOR_BALANCE_ID", referencedColumnName = "METHOD_OF_PAYMENT_ID")
@ManyToOne
private MethodOfPayment methodOfPaymentForBalanceId;
@OneToMany(cascade = CascadeType.ALL, mappedBy = "orders")
private Collection<OrderCustomerLeader> orderCustomerLeaders;
@OneToMany(cascade = CascadeType.ALL, mappedBy = "orders")
private Collection<OrderCustomerPointOfContact> orderCustomerPointOfContacts;
@ManyToMany(mappedBy = "orders")
private Collection<AuditTrail> auditTrails;
public Orders() {
}
public Orders(Integer orderId) {
this.orderId = orderId;
}
public Orders(Integer orderId, Date tripDateTime) {
this.orderId = orderId;
this.tripDateTime = tripDateTime;
}
public Integer getOrderId() {
return orderId;
}
public void setOrderId(Integer orderId) {
this.orderId = orderId;
}
public Date getTripDateTime() {
return tripDateTime;
}
public void setTripDateTime(Date tripDateTime) {
this.tripDateTime = tripDateTime;
}
public Date getReportDateTime() {
return reportDateTime;
}
public void setReportDateTime(Date reportDateTime) {
this.reportDateTime = reportDateTime;
}
public Date getReturnDateTime() {
return returnDateTime;
}
public void setReturnDateTime(Date returnDateTime) {
this.returnDateTime = returnDateTime;
}
public Customer getCustomerId() {
return customerId;
}
public void setCustomerId(Customer customerId) {
this.customerId = customerId;
}
public OrderTemplate getTemplateId() {
return templateId;
}
public void setTemplateId(OrderTemplate templateId) {
this.templateId = templateId;
}
public Double getTotalCost() {
return totalCost;
}
public void setTotalCost(Double totalCost) {
this.totalCost = totalCost;
}
public Double getDeposit() {
return deposit;
}
public void setDeposit(Double deposit) {
this.deposit = deposit;
}
public Double getDiscountRate() {
return discountRate;
}
public void setDiscountRate(Double discountRate) {
this.discountRate = discountRate;
}
public Double getDiscount() {
return discount;
}
public void setDiscount(Double discount) {
this.discount = discount;
}
public Double getBalance() {
return balance;
}
public void setBalance(Double balance) {
this.balance = balance;
}
public Character getDepositPaid() {
return depositPaid;
}
public void setDepositPaid(Character depositPaid) {
this.depositPaid = depositPaid;
}
public Date getDepositPaidDate() {
return depositPaidDate;
}
public void setDepositPaidDate(Date depositPaidDate) {
this.depositPaidDate = depositPaidDate;
}
public Character getPaidInFull() {
return paidInFull;
}
public void setPaidInFull(Character paidInFull) {
this.paidInFull = paidInFull;
}
public Date getPaidInFullDate() {
return paidInFullDate;
}
public void setPaidInFullDate(Date paidInFullDate) {
this.paidInFullDate = paidInFullDate;
}
public Character getCancelled() {
return cancelled;
}
public void setCancelled(Character cancelled) {
this.cancelled = cancelled;
}
public Date getCancelledDate() {
return cancelledDate;
}
public void setCancelledDate(Date cancelledDate) {
this.cancelledDate = cancelledDate;
}
public Character getConfirmed() {
return confirmed;
}
public void setConfirmed(Character confirmed) {
this.confirmed = confirmed;
}
public Date getConfirmedDate() {
return confirmedDate;
}
public void setConfirmedDate(Date confirmedDate) {
this.confirmedDate = confirmedDate;
}
public Character getContractSent() {
return contractSent;
}
public void setContractSent(Character contractSent) {
this.contractSent = contractSent;
}
public Date getContractSentDate() {
return contractSentDate;
}
public void setContractSentDate(Date contractSentDate) {
this.contractSentDate = contractSentDate;
}
public Character getInvoiceSent() {
return invoiceSent;
}
public void setInvoiceSent(Character invoiceSent) {
this.invoiceSent = invoiceSent;
}
public Date getInvoiceSentDate() {
return invoiceSentDate;
}
public void setInvoiceSentDate(Date invoiceSentDate) {
this.invoiceSentDate = invoiceSentDate;
}
public Character getQuoteSent() {
return quoteSent;
}
public void setQuoteSent(Character quoteSent) {
this.quoteSent = quoteSent;
}
public Date getQuoteSentDate() {
return quoteSentDate;
}
public void setQuoteSentDate(Date quoteSentDate) {
this.quoteSentDate = quoteSentDate;
}
public Short getPassengers() {
return passengers;
}
public void setPassengers(Short passengers) {
this.passengers = passengers;
}
public String getCreatedBy() {
return createdBy;
}
public void setCreatedBy(String createdBy) {
this.createdBy = createdBy;
}
public Character getIntrastate() {
return intrastate;
}
public void setIntrastate(Character intrastate) {
this.intrastate = intrastate;
}
public Date getCreatedDt() {
return createdDt;
}
public void setCreatedDt(Date createdDt) {
this.createdDt = createdDt;
}
public Date getChangedDt() {
return changedDt;
}
public void setChangedDt(Date changedDt) {
this.changedDt = changedDt;
}
public Routes getRoutesId() {
return routesId;
}
public void setRoutesId(Routes routesId) {
this.routesId = routesId;
}
public OrderNotes getNotesId() {
return notesId;
}
public void setNotesId(OrderNotes notesId) {
this.notesId = notesId;
}
public OrderDetails getDetailsId() {
return detailsId;
}
public void setDetailsId(OrderDetails detailsId) {
this.detailsId = detailsId;
}
public Bank getBankForDepositId() {
return bankForDepositId;
}
public void setBankForDepositId(Bank bankForDepositId) {
this.bankForDepositId = bankForDepositId;
}
public Bank getBankForBalanceId() {
return bankForBalanceId;
}
public void setBankForBalanceId(Bank bankForBalanceId) {
this.bankForBalanceId = bankForBalanceId;
}
public MethodOfPayment getMethodOfPaymentForDepositId() {
return methodOfPaymentForDepositId;
}
public void setMethodOfPaymentForDepositId(MethodOfPayment methodOfPaymentForDepositId) {
this.methodOfPaymentForDepositId = methodOfPaymentForDepositId;
}
public MethodOfPayment getMethodOfPaymentForBalanceId() {
return methodOfPaymentForBalanceId;
}
public void setMethodOfPaymentForBalanceId(MethodOfPayment methodOfPaymentForBalanceId) {
this.methodOfPaymentForBalanceId = methodOfPaymentForBalanceId;
}
@XmlTransient
public Collection<PointOfContact> getPointOfContacts() {
return pointOfContacts;
}
public void setPointOfContacts(Collection<PointOfContact> pointOfContacts) {
this.pointOfContacts = pointOfContacts;
}
public void addPointOfContact(PointOfContact pointOfContact) {
pointOfContacts.add(pointOfContact);
}
public void removePointOfContact(PointOfContact pointOfContact) {
pointOfContacts.remove(pointOfContact);
}
@XmlTransient
public Collection<Flight> getFlights() {
return flights;
}
public void setFlights(Collection<Flight> flights) {
this.flights = flights;
}
public void addFlight(Flight flight) {
flights.add(flight);
}
public void removeFlight(Flight flight) {
flights.remove(flight);
}
@XmlTransient
public Collection<MealStop> getMealStops() {
return mealStops;
}
public void setMealStops(Collection<MealStop> mealStops) {
this.mealStops = mealStops;
}
public void addMealStop(MealStop mealStop) {
mealStops.add(mealStop);
}
public void removeMealStop(MealStop mealStop) {
mealStops.remove(mealStop);
}
@XmlTransient
public Collection<Attraction> getAttractions() {
return attractions;
}
public void setAttractions(Collection<Attraction> attractions) {
this.attractions = attractions;
}
public void addAttraction(Attraction attraction) {
attractions.add(attraction);
}
public void removeAttraction(Attraction attraction) {
attractions.remove(attraction);
}
@XmlTransient
public Collection<OrderCostDetails> getOrderCostDetails() {
return orderCostDetails;
}
public void setOrderCostDetails(Collection<OrderCostDetails> orderCostDetails) {
this.orderCostDetails = orderCostDetails;
}
public void addOrderCostDetail(OrderCostDetails orderCostDetail) {
orderCostDetails.add(orderCostDetail);
}
public void removeOrderCostDetail(OrderCostDetails orderCostDetail) {
orderCostDetails.remove(orderCostDetail);
}
@XmlTransient
public Collection<Hotel> getHotels() {
return hotels;
}
public void setHotels(Collection<Hotel> hotels) {
this.hotels = hotels;
}
public void addHotel(Hotel hotel) {
hotels.add(hotel);
}
public void removeHotel(Hotel hotel) {
hotels.remove(hotel);
}
@XmlTransient
public Collection<LocalBus> getLocalBuses() {
return localBuses;
}
public void setLocalBuses(Collection<LocalBus> localBuses) {
this.localBuses = localBuses;
}
public void addLocalBus(LocalBus localBus) {
localBuses.add(localBus);
}
public void removeLocalBus(LocalBus localBus) {
localBuses.remove(localBus);
}
@XmlTransient
public Collection<Destination> getDestinations() {
return destinations;
}
public void setDestinations(Collection<Destination> destinations) {
this.destinations = destinations;
}
public void addDestination(Destination destination) {
destinations.add(destination);
}
public void removeDestination(Destination destination) {
destinations.remove(destination);
}
@XmlTransient
public Collection<CreditCard> getCreditCards() {
return creditCards;
}
public void setCreditCards(Collection<CreditCard> creditCards) {
this.creditCards = creditCards;
}
public void addCreditCard(CreditCard creditCard) {
creditCards.add(creditCard);
}
public void removeCreditCard(CreditCard creditCard) {
creditCards.remove(creditCard);
}
@XmlTransient
public Collection<Origin> getOrigins() {
return origins;
}
public void setOrigins(Collection<Origin> origins) {
this.origins = origins;
}
public void addOrigin(Origin origin) {
origins.add(origin);
}
public void removeOrigin(Origin origin) {
origins.remove(origin);
}
@XmlTransient
public Collection<OrderCustomerLeader> getOrderCustomerLeaders() {
return orderCustomerLeaders;
}
public void setOrderCustomerLeaders(Collection<OrderCustomerLeader> orderCustomerLeaders) {
this.orderCustomerLeaders = orderCustomerLeaders;
}
public void addOrderCustomerLeader(OrderCustomerLeader orderCustomerLeader) {
orderCustomerLeaders.add(orderCustomerLeader);
}
public void removeOrderCustomerLeader(OrderCustomerLeader orderCustomerLeader) {
orderCustomerLeaders.remove(orderCustomerLeader);
}
@XmlTransient
public Collection<OrderCustomerPointOfContact> getOrderCustomerPointOfContacts() {
return orderCustomerPointOfContacts;
}
public void setOrderCustomerPointOfContacts(Collection<OrderCustomerPointOfContact> orderCustomerPointOfContacts) {
this.orderCustomerPointOfContacts = orderCustomerPointOfContacts;
}
public void addOrderCustomerPointOfContact(OrderCustomerPointOfContact orderCustomerPointOfContact) {
orderCustomerPointOfContacts.add(orderCustomerPointOfContact);
}
public void removeOrderCustomerPointOfContact(OrderCustomerPointOfContact orderCustomerPointOfContact) {
orderCustomerPointOfContacts.remove(orderCustomerPointOfContact);
}
@XmlTransient
public Collection<AuditTrail> getAuditTrails() {
return auditTrails;
}
public void setAuditTrails(Collection<AuditTrail> auditTrails) {
this.auditTrails = auditTrails;
}
public void addAuditTrail(AuditTrail auditTrail) {
auditTrails.add(auditTrail);
}
public void removeAuditTrail(AuditTrail auditTrail) {
auditTrails.remove(auditTrail);
}
@Override
public int hashCode() {
int hash = 0;
hash += (orderId != null ? orderId.hashCode() : 0);
return hash;
}
@Override
public boolean equals(Object object) {
// TODO: Warning - this method won't work in the case the id fields are not set
if (!(object instanceof Orders)) {
return false;
}
Orders other = (Orders) object;
if ((this.orderId == null && other.orderId != null) || (this.orderId != null && !this.orderId.equals(other.orderId))) {
return false;
}
return true;
}
@Override
public String toString() {
return "jpa.entities.Orders[ orderId=" + orderId + " ]";
}
}
Raw
ORDERS_order_template_tables.sql
CREATE TABLE orders (
order_id INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY,
trip_date_time TIMESTAMP NOT NULL,
report_date_time TIMESTAMP,
return_date_time TIMESTAMP,
details_id INTEGER,
customer_id INTEGER,
routes_id INTEGER,
template_id INTEGER,
total_cost DOUBLE,
deposit DOUBLE,
discount_rate DOUBLE,
discount DOUBLE,
balance DOUBLE,
bank_for_deposit_id INTEGER,
bank_for_balance_id INTEGER,
method_of_payment_for_deposit_id INTEGER,
method_of_payment_for_balance_id INTEGER,
deposit_paid CHAR(1) DEFAULT 'N',
deposit_paid_date DATE,
paid_in_full CHAR(1) DEFAULT 'N',
paid_in_full_date DATE,
cancelled CHAR(1) DEFAULT 'N',
cancelled_date DATE,
confirmed CHAR(1) DEFAULT 'N',
confirmed_date DATE,
contract_sent CHAR(1) DEFAULT 'N',
contract_sent_date DATE,
invoice_sent CHAR(1) DEFAULT 'N',
invoice_sent_date DATE,
quote_sent CHAR(1) DEFAULT 'N',
quote_sent_date DATE,
passengers SMALLINT,
created_by VARCHAR(25),
intrastate CHAR(1) DEFAULT 'N',
notes_id INTEGER,
created_dt TIMESTAMP,
changed_dt TIMESTAMP,
CONSTRAINT orders_pk PRIMARY KEY ( order_id )
);
CREATE TABLE order_template (
template_id INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY,
template_name VARCHAR(128) NOT NULL,
description_tx LONG VARCHAR,
CONSTRAINT order_template_pk PRIMARY KEY ( template_id )
);
ALTER TABLE orders ADD CONSTRAINT orders_fk_order_template FOREIGN KEY ( template_id ) REFERENCES order_template ( template_id );
Raw
OrdersFacade.java
/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/
package jpa.session;
import java.util.List;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import jpa.entities.AuditTrail;
import jpa.entities.Driver;
import jpa.entities.OrderCostDetails;
import jpa.entities.OrderDriver;
import jpa.entities.Orders;
import jpa.entities.OrderVehicle;
/**
*
* @author Administrator
*/
@Stateless
public class OrdersFacade extends AbstractFacade<Orders> {
@PersistenceContext(unitName = "mcmsPU")
private EntityManager em;
protected EntityManager getEntityManager() {
return em;
}
public OrdersFacade() {
super(Orders.class);
}
public List<Orders> filterBy(Boolean browseAll, Boolean browseBalanceDue, Boolean browseConfirmed, Boolean browseConfirmedTripsInVA, Boolean browseContractNotSent,
Boolean browseDefinite, Boolean browseInvoiceNotSent, Boolean browseNeedPricing, Boolean browseNoCustomer,
Boolean browsePaidAll, Boolean browsePaidInFull, Boolean browseQuote, Boolean browseQuoteNotSent,
Boolean browseTentative, Integer customerId, String customer, String customerPointOfContact, String destination, String origin,
String matchModeCustomer, String matchModeCustomerPointOfContact, String matchModeDestination, String matchModeOrigin,
Integer orderId, Character paidInFull, Character depositPaid,
String tripDateFrom, String tripDateTo) throws Exception {
List<Orders> ordersList = null;
Query q;
/*
* The percent (%) wildcard character represents zero or more characters.
* address.phone LIKE '12%3'
* TRUE: '123', '12993'
* FALSE: '1234'
* http://download.oracle.com/javaee/6/tutorial/doc/bnbuf.html#bnbvg
*
* contains = LIKE '%MOTOR%'
* startsWith = LIKE 'MOTOR%'
* endsWith = LIKE '%MOTOR'
*/
String queryStr;
/*
* BETWEEN Expressions
* A BETWEEN expression determines whether an arithmetic expression falls within a range of values.
*
* These two expressions are equivalent:
* p.age BETWEEN 15 AND 19
* p.age >= 15 AND p.age <= 19
*
* tripDateTime is TIMESTAMP where TIME values are BETWEEN 00:00:00 AND 23:59:59
*
* search google.com for the following:
* jpa between datetime 23:59 00:00
*
* http://stackoverflow.com/questions/2543510/date-query-with-hibernate-on-timestamp-column-in-postgresql
*
*/
String from = tripDateFrom + " 00:00:00";
String to = tripDateTo + " 23:59:59";
customer = customer.replaceAll("'", "''");
customerPointOfContact = customerPointOfContact.replaceAll("'", "''");
destination = destination.replaceAll("'", "''");
origin = origin.replaceAll("'", "''");
/*
queryStr = "o.tripDateTime BETWEEN '" + from + "' AND '" + to + "' " +
*/
queryStr = "((o.tripDateTime BETWEEN '" + from + "' AND '" + to + "') OR " +
"(o.returnDateTime BETWEEN '" + from + "' AND '" + to + "') OR " +
"('" + from + "' BETWEEN o.tripDateTime AND o.returnDateTime) OR " +
"('" + to + "' BETWEEN o.tripDateTime AND o.returnDateTime)) " +
"AND NOT EXISTS (SELECT ot FROM OrderTemplate ot WHERE ot.templateId = o.templateId.templateId)";
if (!browseAll) {
if (browseConfirmed || browseDefinite) {
if (queryStr.length() > 0) queryStr += " AND ";
queryStr += "(o.confirmed = 'Y' AND o.cancelled = 'N')";
}
else if (browseConfirmedTripsInVA) {
if (queryStr.length() > 0) queryStr += " AND ";
queryStr += "(o.confirmed = 'Y' AND o.cancelled = 'N') AND o.intrastate = 'Y'";
}
else if (!browsePaidAll && (browseBalanceDue || browsePaidInFull)) {
if (queryStr.length() > 0) queryStr += " AND ";
//queryStr += "(o.quoteSent = 'Y' AND (o.contractSent = 'Y' OR o.invoiceSent = 'Y'))";
queryStr += "(o.quoteSent = 'Y' OR o.contractSent = 'Y' OR o.invoiceSent = 'Y' OR (o.confirmed = 'Y' AND o.cancelled = 'N'))";
if (browseBalanceDue) {
queryStr += " AND (o.paidInFull = 'N')";
}
else if (browsePaidInFull) {
queryStr += " AND (o.paidInFull = 'Y')";
}
}
else {
/*
* so confirmed = 'N' for the following browse options
*/
if (queryStr.length() > 0) queryStr += " AND ";
queryStr += "(o.confirmed = 'N' OR (o.confirmed = 'Y' AND o.cancelled = 'N'))";
if (browseQuote) {
if (queryStr.length() > 0) queryStr += " AND ";
queryStr += "(o.quoteSent = 'N' AND o.contractSent = 'N' AND o.invoiceSent = 'N')";
}
else if (browseTentative) {
if (queryStr.length() > 0) queryStr += " AND ";
queryStr += "(o.quoteSent = 'Y' AND (o.contractSent = 'Y' OR o.invoiceSent = 'Y'))";
}
else if (browseContractNotSent) {
if (queryStr.length() > 0) queryStr += " AND ";
queryStr += "o.contractSent = 'N'";
}
else if (browseInvoiceNotSent) {
if (queryStr.length() > 0) queryStr += " AND ";
queryStr += "o.invoiceSent = 'N'";
}
else if (browseNeedPricing) {
if (queryStr.length() > 0) queryStr += " AND ";
queryStr += "o.totalCost = 0";
}
else if (browseNoCustomer) {
if (queryStr.length() > 0) queryStr += " AND ";
//queryStr += "o.customerId.customerName = 'NO CUSTOMER'";
queryStr += "o.customerId.customerId = 1000";
}
else if (browseQuoteNotSent) {
if (queryStr.length() > 0) queryStr += " AND ";
queryStr += "o.quoteSent = 'N'";
}
}
}
if (!browseNoCustomer && (customerId != 0 || customer.trim().length() > 0)) {
if (queryStr.length() > 0) queryStr += " AND ";
if (customerId != 0 && customer.trim().length() > 0) queryStr += "(";
if (customerId != 0) queryStr += "o.customerId.customerId = " + customerId.toString();
if (customerId != 0 && customer.trim().length() > 0) queryStr += " OR ";
if (customer.trim().length() > 0) {
switch (matchModeCustomer) {
case "contains":
queryStr += "LOWER(o.customerId.customerName) LIKE LOWER('%" + customer + "%')";
break;
case "exact":
queryStr += "LOWER(o.customerId.customerName) = LOWER('" + customer + "')";
break;
case "startsWith":
queryStr += "LOWER(o.customerId.customerName) LIKE LOWER('" + customer + "%')";
break;
case "endsWith":
queryStr += "LOWER(o.customerId.customerName) LIKE LOWER('%" + customer + "')";
break;
}
}
if (customerId != 0 && customer.trim().length() > 0) queryStr += ")";
}
if (customerPointOfContact.length() > 0) {
if (queryStr.length() > 0) queryStr += " AND ";
switch (matchModeCustomerPointOfContact) {
case "contains":
queryStr += "EXISTS (SELECT ocp FROM o.orderCustomerPointOfContacts ocp WHERE LOWER(ocp.pointOfContact.pointOfContactName) LIKE LOWER('%" + customerPointOfContact + "%'))";
break;
case "exact":
queryStr += "EXISTS (SELECT ocp FROM o.orderCustomerPointOfContacts ocp WHERE LOWER(ocp.pointOfContact.pointOfContactName) = LOWER('" + customerPointOfContact + "'))";
break;
case "startsWith":
queryStr += "EXISTS (SELECT ocp FROM o.orderCustomerPointOfContacts ocp WHERE LOWER(ocp.pointOfContact.pointOfContactName) LIKE LOWER('" + customerPointOfContact + "%'))";
break;
case "endsWith":
queryStr += "EXISTS (SELECT ocp FROM o.orderCustomerPointOfContacts ocp WHERE LOWER(ocp.pointOfContact.pointOfContactName) LIKE LOWER('%" + customerPointOfContact + "'))";
break;
}
}
if (destination.length() > 0) {
if (queryStr.length() > 0) queryStr += " AND ";
switch (matchModeDestination) {
case "contains":
queryStr += "EXISTS (SELECT d FROM o.destinations d WHERE LOWER(d.destinationTx) LIKE LOWER('%" + destination + "%')" +
" OR EXISTS (SELECT dAddr1 FROM d.addresses dAddr1 WHERE LOWER(dAddr1.city) LIKE LOWER('%" + destination + "%') OR LOWER(dAddr1.state) LIKE LOWER('%" + destination + "%'))" +
" OR EXISTS (SELECT dAddr2 FROM d.addresses dAddr2 IN (dAddr2.addressLines) dAddrLine WHERE LOWER(dAddrLine.addressLineTx) LIKE LOWER('%" + destination + "%') ))";
break;
case "exact":
queryStr += "EXISTS (SELECT d FROM o.destinations d WHERE LOWER(d.destinationTx) = LOWER('" + destination + "')" +
" OR EXISTS (SELECT dAddr1 FROM d.addresses dAddr1 WHERE LOWER(dAddr1.city) = LOWER('" + destination + "') OR LOWER(dAddr1.state) = LOWER('" + destination + "'))" +
" OR EXISTS (SELECT dAddr2 FROM d.addresses dAddr2 IN (dAddr2.addressLines) dAddrLine WHERE LOWER(dAddrLine.addressLineTx) = LOWER('" + destination + "') ))";
break;
case "startsWith":
queryStr += "EXISTS (SELECT d FROM o.destinations d WHERE LOWER(d.destinationTx) LIKE LOWER('" + destination + "%')" +
" OR EXISTS (SELECT dAddr1 FROM d.addresses dAddr1 WHERE LOWER(dAddr1.city) LIKE LOWER('" + destination + "%') OR LOWER(dAddr1.state) LIKE LOWER('" + destination + "%'))" +
" OR EXISTS (SELECT dAddr2 FROM d.addresses dAddr2 IN (dAddr2.addressLines) dAddrLine WHERE LOWER(dAddrLine.addressLineTx) LIKE LOWER('" + destination + "%') ))";
break;
case "endsWith":
queryStr += "EXISTS (SELECT d FROM o.destinations d WHERE LOWER(d.destinationTx) LIKE LOWER('%" + destination + "')" +
" OR EXISTS (SELECT dAddr1 FROM d.addresses dAddr1 WHERE LOWER(dAddr1.city) LIKE LOWER('%" + destination + "') OR LOWER(dAddr1.state) LIKE LOWER('%" + destination + "'))" +
" OR EXISTS (SELECT dAddr2 FROM d.addresses dAddr2 IN (dAddr2.addressLines) dAddrLine WHERE LOWER(dAddrLine.addressLineTx) LIKE LOWER('%" + destination + "') ))";
break;
}
}
if (origin.length() > 0) {
if (queryStr.length() > 0) queryStr += " AND ";
switch (matchModeOrigin) {
case "contains":
queryStr += "EXISTS (SELECT orig FROM o.origins orig WHERE LOWER(orig.originTx) LIKE LOWER('%" + origin + "%')" +
" OR EXISTS (SELECT origAddr1 FROM orig.addresses origAddr1 WHERE LOWER(origAddr1.city) LIKE LOWER('%" + origin + "%') OR LOWER(origAddr1.state) LIKE LOWER('%" + origin + "%'))" +
" OR EXISTS (SELECT origAddr2 FROM orig.addresses origAddr2 IN (origAddr2.addressLines) origAddrLine WHERE LOWER(origAddrLine.addressLineTx) LIKE LOWER('%" + origin + "%') ))";
break;
case "exact":
queryStr += "EXISTS (SELECT orig FROM o.origins orig WHERE LOWER(orig.originTx) = LOWER('" + origin + "')" +
" OR EXISTS (SELECT origAddr1 FROM orig.addresses origAddr1 WHERE LOWER(origAddr1.city) = LOWER('" + origin + "') OR LOWER(origAddr1.state) = LOWER('" + origin + "'))" +
" OR EXISTS (SELECT origAddr2 FROM orig.addresses origAddr2 IN (origAddr2.addressLines) origAddrLine WHERE LOWER(origAddrLine.addressLineTx) = LOWER('" + origin + "') ))";
break;
case "startsWith":
queryStr += "EXISTS (SELECT orig FROM o.origins orig WHERE LOWER(orig.originTx) LIKE LOWER('" + origin + "%')" +
" OR EXISTS (SELECT origAddr1 FROM orig.addresses origAddr1 WHERE LOWER(origAddr1.city) LIKE LOWER('" + origin + "%') OR LOWER(origAddr1.state) LIKE LOWER('" + origin + "%'))" +
" OR EXISTS (SELECT origAddr2 FROM orig.addresses origAddr2 IN (origAddr2.addressLines) origAddrLine WHERE LOWER(origAddrLine.addressLineTx) LIKE LOWER('" + origin + "%') ))";
break;
case "endsWith":
queryStr += "EXISTS (SELECT orig FROM o.origins orig WHERE LOWER(orig.originTx) LIKE LOWER('%" + origin + "')" +
" OR EXISTS (SELECT origAddr1 FROM orig.addresses origAddr1 WHERE LOWER(origAddr1.city) LIKE LOWER('%" + origin + "') OR LOWER(origAddr1.state) LIKE LOWER('%" + origin + "'))" +
" OR EXISTS (SELECT origAddr2 FROM orig.addresses origAddr2 IN (origAddr2.addressLines) origAddrLine WHERE LOWER(origAddrLine.addressLineTx) LIKE LOWER('%" + origin + "') ))";
break;
}
}
if (orderId > 0) {
if (queryStr.length() > 0) queryStr += " AND ";
queryStr += "o.orderId = " + orderId;
}
if (paidInFull.equals('Y') || paidInFull.equals('N')) {
if (queryStr.length() > 0) queryStr += " AND ";
queryStr += "o.paidInFull = '" + paidInFull + "'";
}
if (depositPaid.equals('Y') || depositPaid.equals('N')) {
if (queryStr.length() > 0) queryStr += " AND ";
queryStr += "o.depositPaid = '" + depositPaid + "'";
}
queryStr = "SELECT o FROM Orders o WHERE " + queryStr + " ORDER BY o.reportDateTime, o.tripDateTime";
try {
q = getEntityManager().createQuery(queryStr);
ordersList = q.getResultList();
} catch(Exception ex) {
throw ex;
}
return ordersList;
}
// override AbstractFacade.findAll() and used NamedQuery
@Override
public List<Orders> findAll() {
return getEntityManager().createNamedQuery("Orders.findAll").getResultList();
}
public List<Orders> findAllBookedTrips(String tripDateFrom, String tripDateTo,
Integer selectOneShowBookedTrips) throws Exception {
List<Orders> ordersList = null;
Query q;
String queryStr;
String from = tripDateFrom + " 00:00:00";
String to = tripDateTo + " 23:59:59";
queryStr = "((o.tripDateTime BETWEEN '" + from + "' AND '" + to + "') OR " +
"(o.returnDateTime BETWEEN '" + from + "' AND '" + to + "') OR " +
"('" + from + "' BETWEEN o.tripDateTime AND o.returnDateTime) OR " +
"('" + to + "' BETWEEN o.tripDateTime AND o.returnDateTime)) " +
(selectOneShowBookedTrips == 1 ? "AND (o.confirmed = 'Y' AND o.cancelled = 'N') " : "") +
"AND NOT EXISTS (SELECT ot FROM OrderTemplate ot WHERE ot.templateId = o.templateId.templateId)";
queryStr = "SELECT o FROM Orders o WHERE " + queryStr + " ORDER BY o.reportDateTime, o.tripDateTime";
try {
q = getEntityManager().createQuery(queryStr);
ordersList = q.getResultList();
} catch(Exception ex) {
throw ex;
}
return ordersList;
}
public List<Orders> findAllConfirmed(String tripDateFrom, String tripDateTo,
Boolean forGoogleCalendar) throws Exception {
List<Orders> ordersList = null;
Query q;
String queryStr;
String from = tripDateFrom + " 00:00:00";
String to = tripDateTo + " 23:59:59";
String column;
if (forGoogleCalendar) {
column = "reportDateTime";
}
else {
column = "tripDateTime";
}
queryStr = "(o." + column + " BETWEEN '" + from + "' AND '" + to + "') " +
"AND (o.confirmed = 'Y' AND o.cancelled = 'N') " +
"AND NOT EXISTS (SELECT ot FROM OrderTemplate ot WHERE ot.templateId = o.templateId.templateId)";
queryStr = "SELECT o FROM Orders o WHERE " + queryStr + " ORDER BY o.reportDateTime, o.tripDateTime";
try {
q = getEntityManager().createQuery(queryStr);
ordersList = q.getResultList();
} catch(Exception ex) {
throw ex;
}
return ordersList;
}
/*
* 1. OBSOLETE method
* 2. I think this method was developed for sorting ORDERS to dynamically
* generate list of ORDER numbers for the argument date range, but this
* method is similar to the solution in legacy MCMS (MS-DOS dBase IV version),
* as legacy MCMS only supported ONE SERVICE and vehicle size (or CHARGES ID)
* 3. findAllConfirmed() is currently/actively used in MCMS web app
*/
public List<OrderCostDetails> findAllConfirmedPerService(String tripDateFrom, String tripDateTo) throws Exception {
List<OrderCostDetails> orderCostDetailsList = null;
Query q;
String queryStr;
String from = tripDateFrom + " 00:00:00";
String to = tripDateTo + " 23:59:59";
queryStr = "(o.tripDateTime BETWEEN '" + from + "' AND '" + to + "') " +
"AND (o.confirmed = 'Y' AND o.cancelled = 'N') " +
"AND NOT EXISTS (SELECT ot FROM OrderTemplate ot WHERE ot.templateId = o.templateId.templateId)";
queryStr = "SELECT ocd FROM OrderCostDetails ocd, Orders o WHERE (ocd.orders = o) AND " +
queryStr + " ORDER BY ocd.serviceAbbr, o.reportDateTime, o.tripDateTime";
try {
q = getEntityManager().createQuery(queryStr);
orderCostDetailsList = q.getResultList();
} catch(Exception ex) {
throw ex;
}
return orderCostDetailsList;
}
public List<Orders> findAllForMonth(String tripDateFrom, String tripDateTo) throws Exception {
List<Orders> ordersList = null;
Query q;
String queryStr;
String from = tripDateFrom + " 00:00:00";
String to = tripDateTo + " 23:59:59";
queryStr = "(o.tripDateTime BETWEEN '" + from + "' AND '" + to + "') " +
"AND NOT EXISTS (SELECT ot FROM OrderTemplate ot WHERE ot.templateId = o.templateId.templateId)";
queryStr = "SELECT o FROM Orders o WHERE " + queryStr + " ORDER BY o.reportDateTime, o.tripDateTime";
try {
q = getEntityManager().createQuery(queryStr);
ordersList = q.getResultList();
} catch(Exception ex) {
throw ex;
}
return ordersList;
}
public List<Orders> findAllForRollingStock(String tripDateFrom, String tripDateTo) throws Exception {
List<Orders> ordersList = null;
Query q;
String queryStr;
String from = tripDateFrom + " 00:00:00";
String to = tripDateTo + " 23:59:59";
queryStr = "o.tripDateTime BETWEEN '" + from + "' AND '" + to + "' AND "
+ "o.confirmed = 'Y' AND o.intrastate = 'Y' AND "
+ "NOT EXISTS (SELECT ot FROM OrderTemplate ot WHERE ot.templateId = o.templateId.templateId)";
queryStr = "SELECT o FROM Orders o WHERE " + queryStr +
" ORDER BY o.reportDateTime, o.tripDateTime";
try {
q = getEntityManager().createQuery(queryStr);
ordersList = q.getResultList();
} catch(Exception ex) {
throw ex;
}
return ordersList;
}
public List<Orders> findAllTemplates() throws Exception {
List<Orders> ordersList = null;
Query q;
String queryStr;
queryStr = "SELECT o FROM Orders o "
+ "WHERE EXISTS (SELECT ot FROM OrderTemplate ot WHERE ot.templateId = o.templateId.templateId)"
+ " ORDER BY o.templateId.templateName, o.reportDateTime, o.tripDateTime";
try {
q = getEntityManager().createQuery(queryStr);
ordersList = q.getResultList();
} catch(Exception ex) {
throw ex;
}
return ordersList;
}
public List<Orders> findAllTemplates(String templateName, String matchMode) throws Exception {
List<Orders> ordersList = null;
Query q;
String queryStr = "";
templateName = templateName.replaceAll("'", "''");
if (templateName.trim().length() > 0) {
//queryStr = "WHERE o.templateId IS NOT NULL AND ";
queryStr = " AND ";
switch (matchMode) {
case "contains":
queryStr += "LOWER(o.templateId.templateName) LIKE LOWER('%" + templateName + "%')";
break;
case "exact":
queryStr += "LOWER(o.templateId.templateName) = LOWER('" + templateName + "')";
break;
case "startsWith":
queryStr += "LOWER(o.templateId.templateName) LIKE LOWER('" + templateName + "%')";
break;
case "endsWith":
queryStr += "LOWER(o.templateId.templateName) LIKE LOWER('%" + templateName + "')";
break;
}
}
queryStr = "SELECT o FROM Orders o "
+ "WHERE EXISTS (SELECT ot FROM OrderTemplate ot WHERE ot.templateId = o.templateId.templateId)"
+ queryStr + " ORDER BY o.templateId.templateName, o.reportDateTime, o.tripDateTime";
try {
q = getEntityManager().createQuery(queryStr);
ordersList = q.getResultList();
} catch(Exception ex) {
throw ex;
}
return ordersList;
}
public List<Integer> findAllYears() throws Exception {
List<Integer> yearsList = null;
Query q;
String queryStr;
queryStr = "SELECT DISTINCT YEAR(trip_date_time) FROM orders";
try {
q = getEntityManager().createNativeQuery(queryStr);
yearsList = q.getResultList();
} catch(Exception ex) {
throw ex;
}
return yearsList;
}
// returns short instead of integer for rolling stock
public List<Short> findAllYearsShort() throws Exception {
List<Short> yearsList = null;
Query q;
String queryStr;
queryStr = "SELECT DISTINCT YEAR(trip_date_time) FROM orders";
try {
q = getEntityManager().createNativeQuery(queryStr);
yearsList = q.getResultList();
} catch(Exception ex) {
throw ex;
}
return yearsList;
}
public List<AuditTrail> filterAuditTrail(Integer id, String dateFrom, String dateTo) throws Exception {
List<AuditTrail> list = null;
Query q;
String queryStr;
String from = dateFrom + " 00:00:00";
String to = dateTo + " 23:59:59";
/*
* 1. will NOT select ORDER TEMPLATEs
* 2. IF NEED to select ORDER TEMPLATEs, then do following:
* a. In prepareViewOnly(), if browseAuditTrail, then browseTemplates = true
* b. In prepareList(), if (browseAuditTrail && view) browseTemplates = false
*/
queryStr = "(a.auditTrailDt BETWEEN '" + from + "' AND '" + to + "')" +
" AND NOT EXISTS (SELECT ot FROM OrderTemplate ot WHERE ot.templateId = o.templateId.templateId)";
if (id != 0) {
if (queryStr.length() > 0) queryStr += " AND ";
queryStr += "(o.orderId = " + id + ")";
}
queryStr = "SELECT a FROM AuditTrail a JOIN a.orders o WHERE " + queryStr
+ " ORDER BY a.auditTrailDt DESC";
try {
q = getEntityManager().createQuery(queryStr);
list = q.getResultList();
} catch(Exception ex) {
throw ex;
}
return list;
}
public List<Orders> filterDriverMoney(Integer driverId, Integer orderId,
String tripDateFrom, String tripDateTo) throws Exception {
List<Orders> ordersList = null;
Query q;
String queryStr;
String from = tripDateFrom + " 00:00:00";
String to = tripDateTo + " 23:59:59";
//queryStr = "(o.tripDateTime BETWEEN '" + from + "' AND '" + to + "') AND o.confirmed = 'Y'";
queryStr = "(ocDriver.beginDate BETWEEN '" + from + "' AND '" + to + "') AND o.confirmed = 'Y'" +
" AND d.subcontractor = 'N'" +
" AND NOT EXISTS (SELECT ot FROM OrderTemplate ot WHERE ot.templateId = o.templateId.templateId)";
if (driverId != 0) {
if (queryStr.length() > 0) {
queryStr += " AND ";
}
queryStr += "(d.driverId = " + driverId + ")";
}
if (orderId != 0) {
if (queryStr.length() > 0) {
queryStr += " AND ";
}
queryStr += "(o.orderId = " + orderId + ")";
}
//queryStr = "SELECT o FROM Orders o, OrderCostDetails ocd, OrderDriver ocDriver, Driver d WHERE (ocd.orders = o AND ocd.orderDrivers = ocDriver AND ocDriver.driver = d) AND " + queryStr + " ORDER BY d.driverName, o.tripDateTime, o.customerId.customerName";
queryStr = "SELECT DISTINCT o FROM OrderDriver ocDriver, Driver d, OrderCostDetails ocd, Orders o " +
"WHERE (ocDriver.driver = d AND ocd.orderDrivers = ocDriver AND ocd.orders = o) AND " +
queryStr + " ORDER BY o.reportDateTime, o.tripDateTime, o.customerId.customerName," +
" ocd.serviceAbbr, ocDriver.beginDate, ocDriver.endDate, d.driverName";
try {
q = getEntityManager().createQuery(queryStr);
ordersList = q.getResultList();
} catch(Exception ex) {
throw ex;
}
return ordersList;
}
public List<OrderDriver> filterPayroll(Integer driverId, Character filterPaidViaPayroll,
String tripDateFrom, String tripDateTo) throws Exception {
List<OrderDriver> orderDriverList = null;
Query q;
String queryStr;
String from = tripDateFrom + " 00:00:00";
String to = tripDateTo + " 23:59:59";
//queryStr = "(o.tripDateTime BETWEEN '" + from + "' AND '" + to + "') AND o.confirmed = 'Y'";
queryStr = "(ocDriver.beginDate BETWEEN '" + from + "' AND '" + to + "') AND o.confirmed = 'Y'" +
" AND d.subcontractor = 'N'" +
" AND NOT EXISTS (SELECT ot FROM OrderTemplate ot WHERE ot.templateId = o.templateId.templateId)";
if (driverId != 0) {
if (queryStr.length() > 0) queryStr += " AND ";
queryStr += "(d.driverId = " + driverId + ")";
}
// filterPaidViaPayroll: Y=Yes, N=No, S=Select One
if (!filterPaidViaPayroll.equals('S')) {
if (queryStr.length() > 0) queryStr += " AND ";
queryStr += "(ocDriver.paidViaPayroll = '" + filterPaidViaPayroll + "')";
}
//queryStr = "SELECT o FROM Orders o, OrderCostDetails ocd, OrderDriver ocDriver, Driver d WHERE (ocd.orders = o AND ocd.orderDrivers = ocDriver AND ocDriver.driver = d) AND " + queryStr + " ORDER BY d.driverName, o.tripDateTime, o.customerId.customerName";
queryStr = "SELECT ocDriver FROM OrderDriver ocDriver, Driver d, OrderCostDetails ocd, Orders o " +
"WHERE (ocDriver.driver = d AND ocd.orderDrivers = ocDriver AND ocd.orders = o) AND " +
queryStr + " ORDER BY d.driverName, ocDriver.beginDate, ocDriver.endDate, ocd.serviceAbbr," +
" o.customerId.customerName";
try {
q = getEntityManager().createQuery(queryStr);
orderDriverList = q.getResultList();
} catch(Exception ex) {
throw ex;
}
return orderDriverList;
}
public List<Driver> getDriversForPayrollAndDriverMoney(String tripDateFrom, String tripDateTo) throws Exception {
List<Driver> driverList = null;
Query q;
String queryStr;
String from = tripDateFrom + " 00:00:00";
String to = tripDateTo + " 23:59:59";
//queryStr = "(ocDriver.beginDate BETWEEN '" + from + "' AND '" + to + "')";
queryStr = "(ocDriver.beginDate BETWEEN '" + from + "' AND '" + to + "') AND o.confirmed = 'Y'" +
" AND d.subcontractor = 'N' AND NOT EXISTS (SELECT ot FROM OrderTemplate ot WHERE ot.templateId = o.templateId.templateId)";
//queryStr = "SELECT DISTINCT d FROM Driver d, OrderDriver ocDriver WHERE (ocDriver.driver = d) AND " + queryStr + " ORDER BY d.driverName";
queryStr = "SELECT DISTINCT d FROM Driver d, OrderDriver ocDriver, OrderCostDetails ocd, Orders o " +
"WHERE (ocDriver.driver = d AND ocd.orderDrivers = ocDriver AND ocd.orders = o) AND " +
queryStr + " ORDER BY d.driverName";
try {
q = getEntityManager().createQuery(queryStr);
driverList = q.getResultList();
} catch(Exception ex) {
throw ex;
}
return driverList;
}
public Orders getOrder(Integer orderId) throws Exception {
Orders order = null;
Query q;
String queryStr = null;
if (orderId != null) {
//System.out.println("OrdersFacade.getOrder(" + orderId.toString() + ")");
try {
queryStr = "SELECT o FROM Orders o WHERE o.orderId = " + orderId.toString();
q = getEntityManager().createQuery(queryStr);
if (q.getResultList().size() == 1) {
order = (Orders) q.getSingleResult();
}
} catch(Exception ex) {
throw ex;
}
}
else {
//System.out.println("OrdersFacade.getOrder(null)");
}
return order;
}
public List<OrderDriver> getOrderDriver(Integer driverId, String tripDateFrom, String tripDateTo) throws Exception {
List<OrderDriver> orderDriverList = null;
Query q;
String queryStr;
String from = tripDateFrom + " 00:00:00";
String to = tripDateTo + " 23:59:59";
queryStr = "(o.tripDateTime BETWEEN '" + from + "' AND '" + to + "') AND " +
"NOT EXISTS (SELECT ot FROM OrderTemplate ot WHERE ot.templateId = o.templateId.templateId)";
if (driverId != 0) {
if (queryStr.length() > 0) queryStr += " AND ";
queryStr += "(d.driverId = " + driverId + ")";
}
queryStr = "SELECT ocDriver FROM OrderDriver ocDriver, Driver d, OrderCostDetails ocd, Orders o WHERE (ocDriver.driver = d AND ocd.orderDrivers = ocDriver AND ocd.orders = o) AND " + queryStr + " ORDER BY d.driverName, ocDriver.beginDate, ocDriver.endDate";
try {
q = getEntityManager().createQuery(queryStr);
orderDriverList = q.getResultList();
} catch(Exception ex) {
throw ex;
}
return orderDriverList;
}
public List<Orders> getOrdersForAuditTrail(String dateFrom, String dateTo) throws Exception {
List<Orders> list = null;
Query q;
String queryStr;
String from = dateFrom + " 00:00:00";
String to = dateTo + " 23:59:59";
/*
* 1. will NOT select ORDER TEMPLATEs
* 2. IF NEED to select ORDER TEMPLATEs, then do following:
* a. In prepareViewOnly(), if browseAuditTrail, then browseTemplates = true
* b. In prepareList(), if (browseAuditTrail && view) browseTemplates = false
*/
queryStr = "(a.auditTrailDt BETWEEN '" + from + "' AND '" + to + "')" +
" AND NOT EXISTS (SELECT ot FROM OrderTemplate ot WHERE ot.templateId = o.templateId.templateId)";
queryStr = "SELECT DISTINCT o FROM AuditTrail a JOIN a.orders o WHERE " + queryStr
+ " ORDER BY o.reportDateTime, o.tripDateTime, o.customerId.customerName";
try {
q = getEntityManager().createQuery(queryStr);
list = q.getResultList();
} catch(Exception ex) {
throw ex;
}
return list;
}
public List<Orders> getOrdersForDriverMoney(String tripDateFrom, String tripDateTo) throws Exception {
List<Orders> list = null;
Query q;
String queryStr;
String from = tripDateFrom + " 00:00:00";
String to = tripDateTo + " 23:59:59";
//queryStr = "(ocDriver.beginDate BETWEEN '" + from + "' AND '" + to + "')";
queryStr = "(ocDriver.beginDate BETWEEN '" + from + "' AND '" + to + "') AND o.confirmed = 'Y'" +
" AND d.subcontractor = 'N' AND NOT EXISTS (SELECT ot FROM OrderTemplate ot WHERE ot.templateId = o.templateId.templateId)";
//queryStr = "SELECT DISTINCT d FROM Driver d, OrderDriver ocDriver WHERE (ocDriver.driver = d) AND " + queryStr + " ORDER BY d.driverName";
queryStr = "SELECT DISTINCT o FROM Driver d, OrderDriver ocDriver, OrderCostDetails ocd, Orders o " +
"WHERE (ocDriver.driver = d AND ocd.orderDrivers = ocDriver AND ocd.orders = o) AND " +
queryStr + " ORDER BY o.reportDateTime, o.tripDateTime, o.customerId.customerName";
try {
q = getEntityManager().createQuery(queryStr);
list = q.getResultList();
} catch(Exception ex) {
throw ex;
}
return list;
}
public List<OrderVehicle> getOrderVehicle(Integer vehicleId, String tripDateFrom, String tripDateTo) throws Exception {
List<OrderVehicle> orderVehicleList = null;
Query q;
String queryStr;
String from = tripDateFrom + " 00:00:00";
String to = tripDateTo + " 23:59:59";
queryStr = "(o.tripDateTime BETWEEN '" + from + "' AND '" + to + "') AND " +
"NOT EXISTS (SELECT ot FROM OrderTemplate ot WHERE ot.templateId = o.templateId.templateId)";
if (vehicleId != 0) {
if (queryStr.length() > 0) queryStr += " AND ";
queryStr += "(d.vehicleId = " + vehicleId + ")";
}
queryStr = "SELECT ocVehicle FROM OrderVehicle ocVehicle, Vehicle v, OrderCostDetails ocd, Orders o WHERE (ocVehicle.vehicle = v AND ocd.orderVehicles = ocVehicle AND ocd.orders = o) AND " + queryStr + " ORDER BY v.vehicleNumber, ocVehicle.beginDate, ocVehicle.endDate";
try {
q = getEntityManager().createQuery(queryStr);
orderVehicleList = q.getResultList();
} catch(Exception ex) {
throw ex;
}
return orderVehicleList;
}
}
Raw
OrderTemplate.java
/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/
package jpa.entities;
import java.io.Serializable;
import java.util.Collection;
import javax.persistence.*;
import javax.validation.constraints.NotNull;
import javax.validation.constraints.Size;
import javax.xml.bind.annotation.XmlRootElement;
import javax.xml.bind.annotation.XmlTransient;
/**
*
* @author Administrator
*/
@Entity
@Table(name = "ORDER_TEMPLATE")
@XmlRootElement
@NamedQueries({
@NamedQuery(name = "OrderTemplate.findAll", query = "SELECT o FROM OrderTemplate o"),
@NamedQuery(name = "OrderTemplate.findByTemplateId", query = "SELECT o FROM OrderTemplate o WHERE o.templateId = :templateId"),
@NamedQuery(name = "OrderTemplate.findByTemplateName", query = "SELECT o FROM OrderTemplate o WHERE o.templateName = :templateName")})
public class OrderTemplate implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Basic(optional = false)
@NotNull
@Column(name = "TEMPLATE_ID")
private Integer templateId;
@Basic(optional = false)
@NotNull
@Size(min = 1, max = 128)
@Column(name = "TEMPLATE_NAME")
private String templateName;
@Lob
@Size(max = 32700)
@Column(name = "DESCRIPTION_TX")
private String descriptionTx;
@OneToMany(mappedBy = "templateId")
private Collection<Orders> orders;
public OrderTemplate() {
}
public OrderTemplate(Integer templateId) {
this.templateId = templateId;
}
public OrderTemplate(Integer templateId, String templateName) {
this.templateId = templateId;
this.templateName = templateName;
}
public Integer getTemplateId() {
return templateId;
}
public void setTemplateId(Integer templateId) {
this.templateId = templateId;
}
public String getTemplateName() {
return templateName;
}
public void setTemplateName(String templateName) {
this.templateName = templateName;
}
public String getDescriptionTx() {
return descriptionTx;
}
public void setDescriptionTx(String descriptionTx) {
this.descriptionTx = descriptionTx;
}
@XmlTransient
public Collection<Orders> getOrders() {
return orders;
}
public void setOrders(Collection<Orders> orders) {
this.orders = orders;
}
public void addOrder(Orders order) {
orders.add(order);
}
public void removeOrder(Orders order) {
orders.remove(order);
}
@Override
public int hashCode() {
int hash = 0;
hash += (templateId != null ? templateId.hashCode() : 0);
return hash;
}
@Override
public boolean equals(Object object) {
// TODO: Warning - this method won't work in the case the id fields are not set
if (!(object instanceof OrderTemplate)) {
return false;
}
OrderTemplate other = (OrderTemplate) object;
if ((this.templateId == null && other.templateId != null) || (this.templateId != null && !this.templateId.equals(other.templateId))) {
return false;
}
return true;
}
@Override
public String toString() {
return "jpa.entities.OrderTemplate[ templateId=" + templateId + " ]";
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment