public final SentenceList getProductCatQBF() {
return new StaticSentence(
s,
new QBFBuilder(
"SELECT P.ID, P.REFERENCE, P.CODE, P.NAME, P.ISCOM, P.ISSCALE, P.PRICEBUY, P.PRICESELL, P.CATEGORY, P.TAXCAT, P.ATTRIBUTESET_ID, P.IMAGE, P.STOCKCOST, P.STOCKVOLUME, CASE WHEN C.PRODUCT IS NULL THEN "
+ s.DB.FALSE()
+ " ELSE "
+ s.DB.TRUE()
+ " END, C.CATORDER, P.ATTRIBUTES "
+ "FROM PRODUCTS P LEFT OUTER JOIN PRODUCTS_CAT C ON P.ID = C.PRODUCT "
+ "WHERE ?(QBF_FILTER) "
+ "ORDER BY P.REFERENCE",
new String[] {"P.NAME", "P.PRICEBUY", "P.PRICESELL", "P.CATEGORY", "P.CODE"}),
new SerializerWriteBasic(
new Datas[] {
Datas.OBJECT,
Datas.STRING,
Datas.OBJECT,
Datas.DOUBLE,
Datas.OBJECT,
Datas.DOUBLE,
Datas.OBJECT,
Datas.STRING,
Datas.OBJECT,
Datas.STRING
}),
productsRow.getSerializerRead());
}
public final SentenceExec getCatalogCategoryAdd() {
return new StaticSentence(
s,
"INSERT INTO PRODUCTS_CAT(PRODUCT, CATORDER) SELECT ID, "
+ s.DB.INTEGER_NULL()
+ " FROM PRODUCTS WHERE CATEGORY = ?",
SerializerWriteString.INSTANCE);
}
public CustomerInfoExt findCustomerExt(String card) throws BasicException {
return (CustomerInfoExt)
new PreparedSentence(
s,
"SELECT ID, TAXID, SEARCHKEY, NAME, CARD, TAXCATEGORY, NOTES, MAXDEBT, VISIBLE, CURDATE, CURDEBT"
+ ", FIRSTNAME, LASTNAME, EMAIL, PHONE, PHONE2, FAX"
+ ", ADDRESS, ADDRESS2, POSTAL, CITY, REGION, COUNTRY"
+ " FROM CUSTOMERS WHERE CARD = ? AND VISIBLE = "
+ s.DB.TRUE(),
SerializerWriteString.INSTANCE,
new CustomerExtRead())
.find(card);
}
public List<ProductInfoExt> getProductComments(String id) throws BasicException {
return new PreparedSentence(
s,
"SELECT P.ID, P.REFERENCE, P.CODE, P.NAME, P.ISCOM, P.ISSCALE, P.PRICEBUY, P.PRICESELL, P.TAXCAT, P.CATEGORY, P.ATTRIBUTESET_ID, P.IMAGE, P.ATTRIBUTES "
+ "FROM PRODUCTS P, PRODUCTS_CAT O, PRODUCTS_COM M WHERE P.ID = O.PRODUCT AND P.ID = M.PRODUCT2 AND M.PRODUCT = ? "
+ "AND P.ISCOM = "
+ s.DB.TRUE()
+ " "
+ "ORDER BY O.CATORDER, P.NAME",
SerializerWriteString.INSTANCE,
ProductInfoExt.getSerializerRead())
.list(id);
}
// Auxiliar list for a filter
public SentenceList getProductListAuxiliar() {
return new StaticSentence(
s,
new QBFBuilder(
"SELECT ID, REFERENCE, CODE, NAME, ISCOM, ISSCALE, PRICEBUY, PRICESELL, TAXCAT, CATEGORY, ATTRIBUTESET_ID, IMAGE, ATTRIBUTES "
+ "FROM PRODUCTS WHERE ISCOM = "
+ s.DB.TRUE()
+ " AND ?(QBF_FILTER) ORDER BY REFERENCE",
new String[] {"NAME", "PRICEBUY", "PRICESELL", "CATEGORY", "CODE"}),
new SerializerWriteBasic(
new Datas[] {
Datas.OBJECT,
Datas.STRING,
Datas.OBJECT,
Datas.DOUBLE,
Datas.OBJECT,
Datas.DOUBLE,
Datas.OBJECT,
Datas.STRING,
Datas.OBJECT,
Datas.STRING
}),
ProductInfoExt.getSerializerRead());
}
@Override
public void init(Session s) {
m_sInitScript = "/com/openbravo/pos/scripts/" + s.DB.getName();
m_dbVersion = s.DB.getName();
m_version =
new PreparedSentence(
s,
"SELECT VERSION FROM APPLICATIONS WHERE ID = ?",
SerializerWriteString.INSTANCE,
SerializerReadString.INSTANCE);
m_dummy = new StaticSentence(s, "SELECT * FROM PEOPLE WHERE 1 = 0");
final ThumbNailBuilder tnb = new ThumbNailBuilder(32, 32, "com/openbravo/images/sysadmin.png");
peopleread =
new SerializerRead() {
@Override
public Object readValues(DataRead dr) throws BasicException {
return new AppUser(
dr.getString(1),
dr.getString(2),
dr.getString(3),
dr.getString(4),
dr.getString(5),
new ImageIcon(tnb.getThumbNail(ImageUtils.readImage(dr.getBytes(6)))));
}
};
m_peoplevisible =
new StaticSentence(
s,
"SELECT ID, NAME, APPPASSWORD, CARD, ROLE, IMAGE FROM PEOPLE WHERE VISIBLE = "
+ s.DB.TRUE()
+ " ORDER BY NAME",
null,
peopleread);
m_peoplebycard =
new PreparedSentence(
s,
"SELECT ID, NAME, APPPASSWORD, CARD, ROLE, IMAGE FROM PEOPLE WHERE CARD = ? AND VISIBLE = "
+ s.DB.TRUE(),
SerializerWriteString.INSTANCE,
peopleread);
m_resourcebytes =
new PreparedSentence(
s,
"SELECT CONTENT FROM RESOURCES WHERE NAME = ?",
SerializerWriteString.INSTANCE,
SerializerReadBytes.INSTANCE);
Datas[] resourcedata = new Datas[] {Datas.STRING, Datas.STRING, Datas.INT, Datas.BYTES};
m_resourcebytesinsert =
new PreparedSentence(
s,
"INSERT INTO RESOURCES(ID, NAME, RESTYPE, CONTENT) VALUES (?, ?, ?, ?)",
new SerializerWriteBasic(resourcedata));
m_resourcebytesupdate =
new PreparedSentence(
s,
"UPDATE RESOURCES SET NAME = ?, RESTYPE = ?, CONTENT = ? WHERE NAME = ?",
new SerializerWriteBasicExt(resourcedata, new int[] {1, 2, 3, 1}));
m_rolepermissions =
new PreparedSentence(
s,
"SELECT PERMISSIONS FROM ROLES WHERE ID = ?",
SerializerWriteString.INSTANCE,
SerializerReadBytes.INSTANCE);
m_changepassword =
new StaticSentence(
s,
"UPDATE PEOPLE SET APPPASSWORD = ? WHERE ID = ?",
new SerializerWriteBasic(new Datas[] {Datas.STRING, Datas.STRING}));
m_sequencecash =
new StaticSentence(
s,
"SELECT MAX(HOSTSEQUENCE) FROM CLOSEDCASH WHERE HOST = ?",
SerializerWriteString.INSTANCE,
SerializerReadInteger.INSTANCE);
m_activecash =
new StaticSentence(
s,
"SELECT HOST, HOSTSEQUENCE, DATESTART, DATEEND, NOSALES FROM CLOSEDCASH WHERE MONEY = ?",
SerializerWriteString.INSTANCE,
new SerializerReadBasic(
new Datas[] {
Datas.STRING, Datas.INT, Datas.TIMESTAMP, Datas.TIMESTAMP, Datas.INT
}));
m_insertcash =
new StaticSentence(
s,
"INSERT INTO CLOSEDCASH(MONEY, HOST, HOSTSEQUENCE, DATESTART, DATEEND) "
+ "VALUES (?, ?, ?, ?, ?)",
new SerializerWriteBasic(
new Datas[] {
Datas.STRING, Datas.STRING, Datas.INT, Datas.TIMESTAMP, Datas.TIMESTAMP
}));
m_draweropened =
new StaticSentence(
s,
"INSERT INTO DRAWEROPENED ( NAME, TICKETID) " + "VALUES (?, ?)",
new SerializerWriteBasic(new Datas[] {Datas.STRING, Datas.STRING}));
m_locationfind =
new StaticSentence(
s,
"SELECT NAME FROM LOCATIONS WHERE ID = ?",
SerializerWriteString.INSTANCE,
SerializerReadString.INSTANCE);
resetResourcesCache();
}
public final Integer getNextTicketPaymentIndex() throws BasicException {
return (Integer) s.DB.getSequenceSentence(s, "TICKETSNUM_PAYMENT").find();
}
public final Integer getNextTicketRefundIndex() throws BasicException {
return (Integer) s.DB.getSequenceSentence(s, "TICKETSNUM_REFUND").find();
}