private MAttributeSetInstance saveSelection0() {
log.info("");
final MAttributeSet as = asiTemplate.getMAttributeSet();
Check.assumeNotNull(as, "as not null");
// Create a new ASI which is copying the existing one
final MAttributeSetInstance asi =
new MAttributeSetInstance(getCtx(), 0, ITrx.TRXNAME_ThreadInherited);
InterfaceWrapperHelper.copyValues(
asiTemplate, asi, false); // honorIsCalculated=false => copy everything
asi.setM_AttributeSet(as); // make sure we have the right AttributeSet model set
//
boolean changed = false;
final Set<String> mandatory = new LinkedHashSet<>();
// Lot
if (!m_productWindow && as.isLot()) {
String text = fieldLotString.getText();
asi.setLot(text);
if (as.isLotMandatory() && (text == null || text.length() == 0)) {
mandatory.add(msgBL.translate(getCtx(), "Lot"));
}
changed = true;
} // Lot
// Serial No
if (!m_productWindow && as.isSerNo()) {
final String serNo = fieldSerNo.getText();
asi.setSerNo(serNo);
if (as.isSerNoMandatory() && Check.isEmpty(serNo, true)) {
mandatory.add(msgBL.translate(getCtx(), "SerNo"));
}
changed = true;
} // SerNo
//
// Guarantee Date (if required)
if (fieldGuaranteeDateDisplayed) {
final Timestamp guaranteeDate = fieldGuaranteeDate.getValue();
asi.setGuaranteeDate(guaranteeDate);
if (as.isGuaranteeDate() && as.isGuaranteeDateMandatory() && guaranteeDate == null) {
mandatory.add(msgBL.translate(getCtx(), I_M_AttributeSetInstance.COLUMNNAME_GuaranteeDate));
}
changed = true;
}
//
// New Instance
if (changed || asi.getM_AttributeSetInstance_ID() <= 0) {
InterfaceWrapperHelper.save(asi);
changed = true;
}
final int attributeSetInstanceId = asi.getM_AttributeSetInstance_ID();
//
// Save Instance Attributes
for (final MAttribute attribute : m_attributes) {
final CEditor editor = attributeId2editor.get(attribute.getM_Attribute_ID());
if (MAttribute.ATTRIBUTEVALUETYPE_List.equals(attribute.getAttributeValueType())) {
@SuppressWarnings("unchecked")
final CComboBox<I_M_AttributeValue> editorCombo = (CComboBox<I_M_AttributeValue>) editor;
final I_M_AttributeValue attributeValue = editorCombo.getSelectedItem();
if (attribute.isMandatory() && attributeValue == null) {
mandatory.add(attribute.getName());
}
attribute.setMAttributeInstance(attributeSetInstanceId, attributeValue);
} else if (MAttribute.ATTRIBUTEVALUETYPE_Number.equals(attribute.getAttributeValueType())) {
final VNumber editorNumber = (VNumber) editor;
final BigDecimal value = (BigDecimal) editorNumber.getValue();
if (attribute.isMandatory() && value == null) {
mandatory.add(attribute.getName());
}
attribute.setMAttributeInstance(attributeSetInstanceId, value);
} else if (MAttribute.ATTRIBUTEVALUETYPE_Date.equals(attribute.getAttributeValueType())) {
final VDate editorDate = (VDate) editor;
final Timestamp value = editorDate.getValue();
if (attribute.isMandatory() && value == null) {
mandatory.add(attribute.getName());
}
attribute.setMAttributeInstance(attributeSetInstanceId, value);
} else {
final VString editorString = (VString) editor;
final String value = editorString.getText();
if (attribute.isMandatory() && Check.isEmpty(value, false)) {
mandatory.add(attribute.getName());
}
attribute.setMAttributeInstance(attributeSetInstanceId, value);
}
changed = true;
} // for all attributes
//
// Throw exception if there are some mandatory fields which were not set
if (!mandatory.isEmpty()) {
throw new AdempiereException("@FillMandatory@ " + StringUtils.toString(mandatory, ", "));
}
// Save Model
if (changed) {
asi.setMAttributeSet(as); // NOTE: this is workaround for the case when M_AttributeSet_ID=0
attributeSetInstanceBL.setDescription(asi);
InterfaceWrapperHelper.save(asi);
}
return asi;
}
private void tableInit() {
m_sql = new StringBuffer();
m_sql2 = new StringBuffer();
// Mostrar todos los ACC y AAP
if (tableInit_option == 0) {
m_sql.append(
"select cb.value ||'-'|| cb.name vendor, co.name country, extract(month from cn.CREATED) month,"
+ " extract (year from cn.CREATED) year, NVL(round(sum(cn.XX_UNITPURCHASEPRICEBS),2),0) total,"
+ " dep.value || '-' || dep.name department, cat.value || '-' || cat.name category,"
+ " o.XX_VMR_DEPARTMENT_ID, o.XX_VMR_CATEGORY_ID, o.C_BPARTNER_ID vendorID"
+ " from XX_CREDITNOTIFYRETURN cn, C_BPARTNER cb, C_COUNTRY co, C_ORDER o, XX_VMR_DEPARTMENT dep, XX_VMR_CATEGORY cat"
+ " where o.C_BPARTNER_ID=cb.C_BPARTNER_ID and co.C_COUNTRY_ID=o.C_COUNTRY_ID and"
+ " cn.XX_NOTIFICATIONTYPE='ACC'and cn.C_ORDER_ID=o.C_ORDER_ID and"
+ " dep.XX_VMR_DEPARTMENT_ID=o.XX_VMR_DEPARTMENT_ID and o.XX_VMR_CATEGORY_ID=cat.XX_VMR_CATEGORY_ID and o.AD_CLIENT_ID="
+ ctx.getAD_Client_ID()
+ " AND o.ISSOTRX = 'N'");
m_groupBy =
" group by cb.value ||'-'|| cb.name, co.name, extract(month from cn.CREATED), extract (year from cn.CREATED), dep.value || '-' || dep.name,"
+ " cat.value || '-' || cat.name,o.XX_VMR_DEPARTMENT_ID, o.XX_VMR_CATEGORY_ID, o.C_BPARTNER_ID";
m_sql2.append(
"select cb.value ||'-'|| cb.name vendor, co.name country, det.XX_MONTH month,"
+ " det.XX_YEAR year, round(cn.XX_UNITPURCHASEPRICEBS,2) total, o.C_BPARTNER_ID vendorID,"
+ " o.XX_VMR_DEPARTMENT_ID, o.XX_VMR_CATEGORY_ID, cn.XX_CREDITNOTIFYRETURN_ID creditNotID, cn.XX_NOTIFICATIONTYPE tipo"
+ " from XX_CREDITNOTIFYRETURN cn, C_BPARTNER cb, C_COUNTRY co, C_ORDER o, XX_VMR_DEPARTMENT dep, XX_VMR_CATEGORY cat, XX_VCN_DETAILADVICE det"
+ " where o.C_BPARTNER_ID=cb.C_BPARTNER_ID and co.C_COUNTRY_ID=o.C_COUNTRY_ID and cn.XX_NOTIFICATIONTYPE<>'ACC'"
+ " and cn.C_ORDER_ID=o.C_ORDER_ID and dep.XX_VMR_DEPARTMENT_ID=o.XX_VMR_DEPARTMENT_ID"
+ " and o.XX_VMR_CATEGORY_ID=cat.XX_VMR_CATEGORY_ID and det.XX_CREDITNOTIFYRETURN_ID=cn.XX_CREDITNOTIFYRETURN_ID and o.AD_CLIENT_ID="
+ ctx.getAD_Client_ID()
+ " AND o.ISSOTRX = 'N'");
m_groupBy2 =
" group by cb.value ||'-'|| cb.name, co.name, det.XX_MONTH, det.XX_YEAR, cn.XX_UNITPURCHASEPRICEBS,"
+ " o.XX_VMR_DEPARTMENT_ID, o.XX_VMR_CATEGORY_ID, o.C_BPARTNER_ID, cn.XX_CREDITNOTIFYRETURN_ID, cn.XX_NOTIFICATIONTYPE";
// Búsqueda por mes
if (monthCombo.getSelectedIndex() != 0 && monthCombo.getSelectedItem() != null) {
m_sql
.append(" AND ")
.append("extract(month from cn.created)=")
.append(monthCombo.getSelectedIndex());
m_sql2.append(" AND ").append("det.XX_MONTH=").append(monthCombo.getSelectedIndex());
}
// Búsqueda por año
if (yearField.getValue() != null) {
m_sql
.append(" AND ")
.append("extract(year from cn.created)=")
.append(((BigDecimal) yearField.getValue()).intValue());
m_sql2
.append(" AND ")
.append("det.XX_YEAR=")
.append(((BigDecimal) yearField.getValue()).intValue());
}
// Búsqueda por proveedor
if (comboBPartner.getSelectedIndex() != 0 && comboBPartner.getSelectedItem() != null) {
if (((KeyNamePair) comboBPartner.getSelectedItem()).getKey() != 0) {
int clave_vendor = ((KeyNamePair) comboBPartner.getSelectedItem()).getKey();
m_sql.append(" AND ").append("o.C_BPartner_ID=").append(clave_vendor);
m_sql2.append(" AND ").append("o.C_BPartner_ID=").append(clave_vendor);
}
}
// agrego la categoria
if (categoryCombo.getValue() != null) {
KeyNamePair cat = (KeyNamePair) categoryCombo.getValue();
if (cat.getKey() != -1) {
m_sql.append(" AND o.XX_VMR_CATEGORY_ID = ").append(cat.getKey()).append(" ");
m_sql2.append(" AND o.XX_VMR_CATEGORY_ID = ").append(cat.getKey()).append(" ");
}
}
// agrego el departamento al query
if (departmentCombo.getValue() != null) {
KeyNamePair dep = (KeyNamePair) departmentCombo.getValue();
if (dep.getKey() != -1) {
m_sql.append(" AND o.XX_VMR_DEPARTMENT_ID = ").append(dep.getKey()).append(" ");
m_sql2.append(" AND o.XX_VMR_DEPARTMENT_ID = ").append(dep.getKey()).append(" ");
}
}
}
String SQL = m_sql.toString() + m_groupBy;
int i = 0;
xTableACC.setRowCount(i);
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
pstmt = DB.prepareStatement(SQL, null);
rs = pstmt.executeQuery();
while (rs.next()) {
xTableACC.setRowCount(i + 1);
// Proveedor
xTableACC.setValueAt(rs.getString("vendor"), i, 0); // 1
// País
xTableACC.setValueAt(rs.getString("country"), i, 1);
// Departamento
xTableACC.setValueAt(rs.getString("department"), i, 2);
// Categoría
xTableACC.setValueAt(rs.getString("category"), i, 3);
// Mes
xTableACC.setValueAt(rs.getInt("month"), i, 4);
// Año
xTableACC.setValueAt(rs.getInt("year"), i, 5);
// Compras hechas
xTableACC.setValueAt(rs.getBigDecimal("total"), i, 6);
// Se calculan los avisos cerrados(CER) de el mismo mes, año, proveedor, depto y categoría
String SQL2 =
"select nvl(round(sum(cn.XX_AMOUNT),2),0) totalDesc"
+ " from XX_CREDITNOTIFYRETURN cn, C_BPARTNER cb, C_COUNTRY co, C_ORDER o, XX_VMR_DEPARTMENT dep, XX_VMR_CATEGORY cat"
+ " where o.C_BPARTNER_ID=cb.C_BPARTNER_ID and co.C_COUNTRY_ID=o.C_COUNTRY_ID"
+ " and cn.XX_NOTIFICATIONTYPE='ACC' and cn.C_ORDER_ID=o.C_ORDER_ID"
+ " and dep.XX_VMR_DEPARTMENT_ID=o.XX_VMR_DEPARTMENT_ID and o.XX_VMR_CATEGORY_ID=cat.XX_VMR_CATEGORY_ID"
+ " and XX_STATUS='CER' and extract(month from cn.CREATED)="
+ rs.getInt("month")
+ " and extract (year from cn.CREATED)="
+ rs.getInt("year")
+ " and o.C_BPARTNER_ID="
+ rs.getInt("vendorID")
+ " and o.AD_CLIENT_ID="
+ ctx.getAD_Client_ID()
+ " AND o.ISSOTRX = 'N'";
PreparedStatement pstmt2 = null;
ResultSet rs2 = null;
try {
pstmt2 = DB.prepareStatement(SQL2, null);
rs2 = pstmt2.executeQuery();
if (rs2.next()) xTableACC.setValueAt(rs2.getBigDecimal("totalDesc"), i, 7);
else xTableACC.setValueAt(0, i, 7);
} catch (SQLException e) {
System.out.print(e.getMessage());
} finally {
DB.closeResultSet(rs2);
DB.closeStatement(pstmt2);
}
// Se calculan los avisos pendientes(ACT) de el mismo mes, año, proveedor, depto y categoría
String SQL3 =
"select nvl(round(sum(cn.XX_AMOUNT),2),0) totalDesc"
+ " from XX_CREDITNOTIFYRETURN cn, C_BPARTNER cb, C_COUNTRY co, C_ORDER o, XX_VMR_DEPARTMENT dep, XX_VMR_CATEGORY cat"
+ " where o.C_BPARTNER_ID=cb.C_BPARTNER_ID and co.C_COUNTRY_ID=o.C_COUNTRY_ID"
+ " and cn.XX_NOTIFICATIONTYPE='ACC' and cn.C_ORDER_ID=o.C_ORDER_ID"
+ " and dep.XX_VMR_DEPARTMENT_ID=o.XX_VMR_DEPARTMENT_ID and o.XX_VMR_CATEGORY_ID=cat.XX_VMR_CATEGORY_ID"
+ " and XX_STATUS='ACT' and extract(month from cn.CREATED)="
+ rs.getInt("month")
+ " and extract (year from cn.CREATED)="
+ rs.getInt("year")
+ " and o.C_BPARTNER_ID="
+ rs.getInt("vendorID")
+ " and o.AD_CLIENT_ID="
+ ctx.getAD_Client_ID()
+ " AND o.ISSOTRX = 'N'";
PreparedStatement pstmt3 = null;
ResultSet rs3 = null;
try {
pstmt3 = DB.prepareStatement(SQL3, null);
rs3 = pstmt3.executeQuery();
if (rs3.next()) xTableACC.setValueAt(rs3.getBigDecimal("totalDesc"), i, 8);
else xTableACC.setValueAt(0, i, 8);
} catch (SQLException e) {
System.out.print(e.getMessage());
} finally {
DB.closeResultSet(rs3);
DB.closeStatement(pstmt3);
}
i++;
}
} catch (SQLException e) {
e.getMessage();
} finally {
DB.closeResultSet(rs);
DB.closeStatement(pstmt);
}
String sqlAAP = m_sql2.toString() + m_groupBy2;
i = 0;
xTableAAP.setRowCount(i);
pstmt = null;
rs = null;
try {
pstmt = DB.prepareStatement(sqlAAP, null);
rs = pstmt.executeQuery();
while (rs.next()) {
xTableAAP.setRowCount(i + 1);
// Proveedor
xTableAAP.setValueAt(
new KeyNamePair(rs.getInt("vendorID"), rs.getString("vendor")), i, 0); // 1
// País
xTableAAP.setValueAt(
new KeyNamePair(rs.getInt("creditNotID"), rs.getString("country")), i, 1);
// Mes
xTableAAP.setValueAt(rs.getInt("month"), i, 2);
// Año
xTableAAP.setValueAt(rs.getInt("year"), i, 3);
// Tipo
xTableAAP.setValueAt(rs.getString("tipo"), i, 4);
// Compras hechas
xTableAAP.setValueAt(rs.getBigDecimal("total"), i, 5);
// Se calculan los avisos cerrados(CER) de el mismo mes, año, proveedor, depto y categoría
String SQL2 =
"select nvl(round(sum(cn.XX_AMOUNT),2),0) totalDesc"
+ " from XX_CREDITNOTIFYRETURN cn, C_BPARTNER cb, C_COUNTRY co, C_ORDER o, XX_VMR_DEPARTMENT dep, XX_VMR_CATEGORY cat"
+ " where o.C_BPARTNER_ID=cb.C_BPARTNER_ID and co.C_COUNTRY_ID=o.C_COUNTRY_ID"
+ " and cn.XX_NOTIFICATIONTYPE<>'ACC' and cn.C_ORDER_ID=o.C_ORDER_ID"
+ " and dep.XX_VMR_DEPARTMENT_ID=o.XX_VMR_DEPARTMENT_ID and o.XX_VMR_CATEGORY_ID=cat.XX_VMR_CATEGORY_ID"
+ " and XX_STATUS='CER' and cn.XX_CREDITNOTIFYRETURN_ID="
+ rs.getInt("creditNotID")
+ " and o.C_BPARTNER_ID="
+ rs.getInt("vendorID")
+ " and o.AD_CLIENT_ID="
+ ctx.getAD_Client_ID()
+ " AND o.ISSOTRX = 'N'";
PreparedStatement pstmt2 = null;
ResultSet rs2 = null;
try {
pstmt2 = DB.prepareStatement(SQL2, null);
rs2 = pstmt2.executeQuery();
if (rs2.next()) xTableAAP.setValueAt(rs2.getBigDecimal("totalDesc"), i, 6);
else xTableAAP.setValueAt(0, i, 6);
} catch (SQLException e) {
System.out.print(e.getMessage());
} finally {
DB.closeResultSet(rs2);
DB.closeStatement(pstmt2);
}
// Se calculan los avisos pendientes(ACT) de el mismo mes, año, proveedor, depto y categoría
String SQL3 =
"select nvl(round(sum(cn.XX_AMOUNT),2),0) totalDesc"
+ " from XX_CREDITNOTIFYRETURN cn, C_BPARTNER cb, C_COUNTRY co, C_ORDER o, XX_VMR_DEPARTMENT dep, XX_VMR_CATEGORY cat"
+ " where o.C_BPARTNER_ID=cb.C_BPARTNER_ID and co.C_COUNTRY_ID=o.C_COUNTRY_ID"
+ " and cn.XX_NOTIFICATIONTYPE<>'ACC' and cn.C_ORDER_ID=o.C_ORDER_ID"
+ " and dep.XX_VMR_DEPARTMENT_ID=o.XX_VMR_DEPARTMENT_ID and o.XX_VMR_CATEGORY_ID=cat.XX_VMR_CATEGORY_ID"
+ " and XX_STATUS='ACT' and cn.XX_CREDITNOTIFYRETURN_ID="
+ rs.getInt("creditNotID")
+ " and o.C_BPARTNER_ID="
+ rs.getInt("vendorID")
+ " and o.AD_CLIENT_ID="
+ ctx.getAD_Client_ID()
+ " AND o.ISSOTRX = 'N'";
PreparedStatement pstmt3 = null;
ResultSet rs3 = null;
try {
pstmt3 = DB.prepareStatement(SQL3, null);
rs3 = pstmt3.executeQuery();
if (rs3.next()) xTableAAP.setValueAt(rs3.getBigDecimal("totalDesc"), i, 7);
else xTableAAP.setValueAt(0, i, 7);
} catch (SQLException e) {
System.out.print(e.getMessage());
} finally {
DB.closeResultSet(rs3);
DB.closeStatement(pstmt3);
}
i++;
}
} catch (SQLException e) {
e.getMessage();
} finally {
DB.closeResultSet(rs);
DB.closeStatement(pstmt);
}
} // tableInit
