Example 1 with MLocation
use of org.compiere.model.MLocation in project adempiere by adempiere.
the class ImportOrder method doIt.
// prepare
/**
* Perform process.
* @return Message
* @throws Exception
*/
protected String doIt() throws java.lang.Exception {
StringBuffer sql = null;
int no = 0;
String clientCheck = " AND AD_Client_ID=" + m_AD_Client_ID;
// Delete Old Imported
if (m_deleteOldImported) {
sql = new StringBuffer("DELETE I_Order " + "WHERE I_IsImported='Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.fine("Delete Old Impored =" + no);
}
// Set Client, Org, IsActive, Created/Updated
sql = new StringBuffer("UPDATE I_Order " + "SET AD_Client_ID = COALESCE (AD_Client_ID,").append(m_AD_Client_ID).append(")," + " AD_Org_ID = COALESCE (AD_Org_ID,").append(m_AD_Org_ID).append(")," + " IsActive = COALESCE (IsActive, 'Y')," + " Created = COALESCE (Created, SysDate)," + " CreatedBy = COALESCE (CreatedBy, 0)," + " Updated = COALESCE (Updated, SysDate)," + " UpdatedBy = COALESCE (UpdatedBy, 0)," + " I_ErrorMsg = ' '," + " I_IsImported = 'N' " + "WHERE I_IsImported<>'Y' OR I_IsImported IS NULL");
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.info("Reset=" + no);
sql = new StringBuffer("UPDATE I_Order o " + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Invalid Org, '" + "WHERE (AD_Org_ID IS NULL OR AD_Org_ID=0" + " OR EXISTS (SELECT * FROM AD_Org oo WHERE o.AD_Org_ID=oo.AD_Org_ID AND (oo.IsSummary='Y' OR oo.IsActive='N')))" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
if (no != 0)
log.warning("Invalid Org=" + no);
// Document Type - PO - SO
sql = new StringBuffer(// PO Document Type Name
"UPDATE I_Order o " + "SET C_DocType_ID=(SELECT C_DocType_ID FROM C_DocType d WHERE d.Name=o.DocTypeName" + " AND d.DocBaseType='POO' AND o.AD_Client_ID=d.AD_Client_ID) " + "WHERE C_DocType_ID IS NULL AND IsSOTrx='N' AND DocTypeName IS NOT NULL AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.fine("Set PO DocType=" + no);
sql = new StringBuffer(// SO Document Type Name
"UPDATE I_Order o " + "SET C_DocType_ID=(SELECT C_DocType_ID FROM C_DocType d WHERE d.Name=o.DocTypeName" + " AND d.DocBaseType='SOO' AND o.AD_Client_ID=d.AD_Client_ID) " + "WHERE C_DocType_ID IS NULL AND IsSOTrx='Y' AND DocTypeName IS NOT NULL AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.fine("Set SO DocType=" + no);
sql = new StringBuffer("UPDATE I_Order o " + "SET C_DocType_ID=(SELECT C_DocType_ID FROM C_DocType d WHERE d.Name=o.DocTypeName" + " AND d.DocBaseType IN ('SOO','POO') AND o.AD_Client_ID=d.AD_Client_ID) " + //+ "WHERE C_DocType_ID IS NULL AND IsSOTrx IS NULL AND DocTypeName IS NOT NULL AND I_IsImported<>'Y'").append (clientCheck);
"WHERE C_DocType_ID IS NULL AND DocTypeName IS NOT NULL AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.fine("Set DocType=" + no);
sql = new StringBuffer(// Error Invalid Doc Type Name
"UPDATE I_Order " + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Invalid DocTypeName, ' " + "WHERE C_DocType_ID IS NULL AND DocTypeName IS NOT NULL" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
if (no != 0)
log.warning("Invalid DocTypeName=" + no);
// DocType Default
sql = new StringBuffer(// Default PO
"UPDATE I_Order o " + "SET C_DocType_ID=(SELECT MAX(C_DocType_ID) FROM C_DocType d WHERE d.IsDefault='Y'" + " AND d.DocBaseType='POO' AND o.AD_Client_ID=d.AD_Client_ID) " + "WHERE C_DocType_ID IS NULL AND IsSOTrx='N' AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.fine("Set PO Default DocType=" + no);
sql = new StringBuffer(// Default SO
"UPDATE I_Order o " + "SET C_DocType_ID=(SELECT MAX(C_DocType_ID) FROM C_DocType d WHERE d.IsDefault='Y'" + " AND d.DocBaseType='SOO' AND o.AD_Client_ID=d.AD_Client_ID) " + "WHERE C_DocType_ID IS NULL AND IsSOTrx='Y' AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.fine("Set SO Default DocType=" + no);
sql = new StringBuffer("UPDATE I_Order o " + "SET C_DocType_ID=(SELECT MAX(C_DocType_ID) FROM C_DocType d WHERE d.IsDefault='Y'" + " AND d.DocBaseType IN('SOO','POO') AND o.AD_Client_ID=d.AD_Client_ID) " + "WHERE C_DocType_ID IS NULL AND IsSOTrx IS NULL AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.fine("Set Default DocType=" + no);
sql = new StringBuffer(// No DocType
"UPDATE I_Order " + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=No DocType, ' " + "WHERE C_DocType_ID IS NULL" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
if (no != 0)
log.warning("No DocType=" + no);
// Set IsSOTrx
sql = new StringBuffer("UPDATE I_Order o SET IsSOTrx='Y' " + "WHERE EXISTS (SELECT * FROM C_DocType d WHERE o.C_DocType_ID=d.C_DocType_ID AND d.DocBaseType='SOO' AND o.AD_Client_ID=d.AD_Client_ID)" + " AND C_DocType_ID IS NOT NULL" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.fine("Set IsSOTrx=Y=" + no);
sql = new StringBuffer("UPDATE I_Order o SET IsSOTrx='N' " + "WHERE EXISTS (SELECT * FROM C_DocType d WHERE o.C_DocType_ID=d.C_DocType_ID AND d.DocBaseType='POO' AND o.AD_Client_ID=d.AD_Client_ID)" + " AND C_DocType_ID IS NOT NULL" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.fine("Set IsSOTrx=N=" + no);
// Price List
sql = new StringBuffer("UPDATE I_Order o " + "SET M_PriceList_ID=(SELECT MAX(M_PriceList_ID) FROM M_PriceList p WHERE p.IsDefault='Y'" + " AND p.C_Currency_ID=o.C_Currency_ID AND p.IsSOPriceList=o.IsSOTrx AND o.AD_Client_ID=p.AD_Client_ID) " + "WHERE M_PriceList_ID IS NULL AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.fine("Set Default Currency PriceList=" + no);
sql = new StringBuffer("UPDATE I_Order o " + "SET M_PriceList_ID=(SELECT MAX(M_PriceList_ID) FROM M_PriceList p WHERE p.IsDefault='Y'" + " AND p.IsSOPriceList=o.IsSOTrx AND o.AD_Client_ID=p.AD_Client_ID) " + "WHERE M_PriceList_ID IS NULL AND C_Currency_ID IS NULL AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.fine("Set Default PriceList=" + no);
sql = new StringBuffer("UPDATE I_Order o " + "SET M_PriceList_ID=(SELECT MAX(M_PriceList_ID) FROM M_PriceList p " + " WHERE p.C_Currency_ID=o.C_Currency_ID AND p.IsSOPriceList=o.IsSOTrx AND o.AD_Client_ID=p.AD_Client_ID) " + "WHERE M_PriceList_ID IS NULL AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.fine("Set Currency PriceList=" + no);
sql = new StringBuffer("UPDATE I_Order o " + "SET M_PriceList_ID=(SELECT MAX(M_PriceList_ID) FROM M_PriceList p " + " WHERE p.IsSOPriceList=o.IsSOTrx AND o.AD_Client_ID=p.AD_Client_ID) " + "WHERE M_PriceList_ID IS NULL AND C_Currency_ID IS NULL AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.fine("Set PriceList=" + no);
//
sql = new StringBuffer("UPDATE I_Order " + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=No PriceList, ' " + "WHERE M_PriceList_ID IS NULL" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
if (no != 0)
log.warning("No PriceList=" + no);
// @Trifon - Import Order Source
sql = new StringBuffer("UPDATE I_Order o " + "SET C_OrderSource_ID=(SELECT C_OrderSource_ID FROM C_OrderSource p" + " WHERE o.C_OrderSourceValue=p.Value AND o.AD_Client_ID=p.AD_Client_ID) " + "WHERE C_OrderSource_ID IS NULL AND C_OrderSourceValue IS NOT NULL AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.fine("Set Order Source=" + no);
// Set proper error message
sql = new StringBuffer("UPDATE I_Order " + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Not Found Order Source, ' " + "WHERE C_OrderSource_ID IS NULL AND C_OrderSourceValue IS NOT NULL AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
if (no != 0)
log.warning("No OrderSource=" + no);
// Payment Term
sql = new StringBuffer("UPDATE I_Order o " + "SET C_PaymentTerm_ID=(SELECT C_PaymentTerm_ID FROM C_PaymentTerm p" + " WHERE o.PaymentTermValue=p.Value AND o.AD_Client_ID=p.AD_Client_ID) " + "WHERE C_PaymentTerm_ID IS NULL AND PaymentTermValue IS NOT NULL AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.fine("Set PaymentTerm=" + no);
sql = new StringBuffer("UPDATE I_Order o " + "SET C_PaymentTerm_ID=(SELECT MAX(C_PaymentTerm_ID) FROM C_PaymentTerm p" + " WHERE p.IsDefault='Y' AND o.AD_Client_ID=p.AD_Client_ID) " + "WHERE C_PaymentTerm_ID IS NULL AND o.PaymentTermValue IS NULL AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.fine("Set Default PaymentTerm=" + no);
//
sql = new StringBuffer("UPDATE I_Order " + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=No PaymentTerm, ' " + "WHERE C_PaymentTerm_ID IS NULL" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
if (no != 0)
log.warning("No PaymentTerm=" + no);
// Warehouse
sql = new StringBuffer("UPDATE I_Order o " + "SET M_Warehouse_ID=(SELECT MAX(M_Warehouse_ID) FROM M_Warehouse w" + " WHERE o.AD_Client_ID=w.AD_Client_ID AND o.AD_Org_ID=w.AD_Org_ID) " + "WHERE M_Warehouse_ID IS NULL AND I_IsImported<>'Y'").append(clientCheck);
// Warehouse for Org
no = DB.executeUpdate(sql.toString(), get_TrxName());
if (no != 0)
log.fine("Set Warehouse=" + no);
sql = new StringBuffer("UPDATE I_Order o " + "SET M_Warehouse_ID=(SELECT M_Warehouse_ID FROM M_Warehouse w" + " WHERE o.AD_Client_ID=w.AD_Client_ID) " + "WHERE M_Warehouse_ID IS NULL" + " AND EXISTS (SELECT AD_Client_ID FROM M_Warehouse w WHERE w.AD_Client_ID=o.AD_Client_ID GROUP BY AD_Client_ID HAVING COUNT(*)=1)" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
if (no != 0)
log.fine("Set Only Client Warehouse=" + no);
//
sql = new StringBuffer("UPDATE I_Order " + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=No Warehouse, ' " + "WHERE M_Warehouse_ID IS NULL" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
if (no != 0)
log.warning("No Warehouse=" + no);
// BP from EMail
sql = new StringBuffer("UPDATE I_Order o " + "SET (C_BPartner_ID,AD_User_ID)=(SELECT C_BPartner_ID,AD_User_ID FROM AD_User u" + " WHERE o.EMail=u.EMail AND o.AD_Client_ID=u.AD_Client_ID AND u.C_BPartner_ID IS NOT NULL) " + "WHERE C_BPartner_ID IS NULL AND EMail IS NOT NULL" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.fine("Set BP from EMail=" + no);
// BP from ContactName
sql = new StringBuffer("UPDATE I_Order o " + "SET (C_BPartner_ID,AD_User_ID)=(SELECT C_BPartner_ID,AD_User_ID FROM AD_User u" + " WHERE o.ContactName=u.Name AND o.AD_Client_ID=u.AD_Client_ID AND u.C_BPartner_ID IS NOT NULL) " + "WHERE C_BPartner_ID IS NULL AND ContactName IS NOT NULL" + " AND EXISTS (SELECT Name FROM AD_User u WHERE o.ContactName=u.Name AND o.AD_Client_ID=u.AD_Client_ID AND u.C_BPartner_ID IS NOT NULL GROUP BY Name HAVING COUNT(*)=1)" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.fine("Set BP from ContactName=" + no);
// BP from Value
sql = new StringBuffer("UPDATE I_Order o " + "SET C_BPartner_ID=(SELECT MAX(C_BPartner_ID) FROM C_BPartner bp" + " WHERE o.BPartnerValue=bp.Value AND o.AD_Client_ID=bp.AD_Client_ID) " + "WHERE C_BPartner_ID IS NULL AND BPartnerValue IS NOT NULL" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.fine("Set BP from Value=" + no);
// Default BP
sql = new StringBuffer("UPDATE I_Order o " + "SET C_BPartner_ID=(SELECT C_BPartnerCashTrx_ID FROM AD_ClientInfo c" + " WHERE o.AD_Client_ID=c.AD_Client_ID) " + "WHERE C_BPartner_ID IS NULL AND BPartnerValue IS NULL AND Name IS NULL" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.fine("Set Default BP=" + no);
// Existing Location ? Exact Match
sql = new StringBuffer("UPDATE I_Order o " + "SET (BillTo_ID,C_BPartner_Location_ID)=(SELECT C_BPartner_Location_ID,C_BPartner_Location_ID" + " FROM C_BPartner_Location bpl INNER JOIN C_Location l ON (bpl.C_Location_ID=l.C_Location_ID)" + " WHERE o.C_BPartner_ID=bpl.C_BPartner_ID AND bpl.AD_Client_ID=o.AD_Client_ID" + " AND DUMP(o.Address1)=DUMP(l.Address1) AND DUMP(o.Address2)=DUMP(l.Address2)" + " AND DUMP(o.City)=DUMP(l.City) AND DUMP(o.Postal)=DUMP(l.Postal)" + " AND o.C_Region_ID=l.C_Region_ID AND o.C_Country_ID=l.C_Country_ID) " + "WHERE C_BPartner_ID IS NOT NULL AND C_BPartner_Location_ID IS NULL" + " AND I_IsImported='N'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.fine("Found Location=" + no);
// Set Bill Location from BPartner
sql = new StringBuffer("UPDATE I_Order o " + "SET BillTo_ID=(SELECT MAX(C_BPartner_Location_ID) FROM C_BPartner_Location l" + " WHERE l.C_BPartner_ID=o.C_BPartner_ID AND o.AD_Client_ID=l.AD_Client_ID" + " AND ((l.IsBillTo='Y' AND o.IsSOTrx='Y') OR (l.IsPayFrom='Y' AND o.IsSOTrx='N'))" + ") " + "WHERE C_BPartner_ID IS NOT NULL AND BillTo_ID IS NULL" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.fine("Set BP BillTo from BP=" + no);
// Set Location from BPartner
sql = new StringBuffer("UPDATE I_Order o " + "SET C_BPartner_Location_ID=(SELECT MAX(C_BPartner_Location_ID) FROM C_BPartner_Location l" + " WHERE l.C_BPartner_ID=o.C_BPartner_ID AND o.AD_Client_ID=l.AD_Client_ID" + " AND ((l.IsShipTo='Y' AND o.IsSOTrx='Y') OR o.IsSOTrx='N')" + ") " + "WHERE C_BPartner_ID IS NOT NULL AND C_BPartner_Location_ID IS NULL" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.fine("Set BP Location from BP=" + no);
//
sql = new StringBuffer("UPDATE I_Order " + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=No BP Location, ' " + "WHERE C_BPartner_ID IS NOT NULL AND (BillTo_ID IS NULL OR C_BPartner_Location_ID IS NULL)" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
if (no != 0)
log.warning("No BP Location=" + no);
// Set Country
/**
sql = new StringBuffer ("UPDATE I_Order o "
+ "SET CountryCode=(SELECT MAX(CountryCode) FROM C_Country c WHERE c.IsDefault='Y'"
+ " AND c.AD_Client_ID IN (0, o.AD_Client_ID)) "
+ "WHERE C_BPartner_ID IS NULL AND CountryCode IS NULL AND C_Country_ID IS NULL"
+ " AND I_IsImported<>'Y'").append (clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.fine("Set Country Default=" + no);
**/
sql = new StringBuffer("UPDATE I_Order o " + "SET C_Country_ID=(SELECT C_Country_ID FROM C_Country c" + " WHERE o.CountryCode=c.CountryCode AND c.AD_Client_ID IN (0, o.AD_Client_ID)) " + "WHERE C_BPartner_ID IS NULL AND C_Country_ID IS NULL AND CountryCode IS NOT NULL" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.fine("Set Country=" + no);
//
sql = new StringBuffer("UPDATE I_Order " + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Invalid Country, ' " + "WHERE C_BPartner_ID IS NULL AND C_Country_ID IS NULL" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
if (no != 0)
log.warning("Invalid Country=" + no);
// Set Region
sql = new StringBuffer("UPDATE I_Order o " + "Set RegionName=(SELECT MAX(Name) FROM C_Region r" + " WHERE r.IsDefault='Y' AND r.C_Country_ID=o.C_Country_ID" + " AND r.AD_Client_ID IN (0, o.AD_Client_ID)) " + "WHERE C_BPartner_ID IS NULL AND C_Region_ID IS NULL AND RegionName IS NULL" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.fine("Set Region Default=" + no);
//
sql = new StringBuffer("UPDATE I_Order o " + "Set C_Region_ID=(SELECT C_Region_ID FROM C_Region r" + " WHERE r.Name=o.RegionName AND r.C_Country_ID=o.C_Country_ID" + " AND r.AD_Client_ID IN (0, o.AD_Client_ID)) " + "WHERE C_BPartner_ID IS NULL AND C_Region_ID IS NULL AND RegionName IS NOT NULL" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.fine("Set Region=" + no);
//
sql = new StringBuffer("UPDATE I_Order o " + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Invalid Region, ' " + "WHERE C_BPartner_ID IS NULL AND C_Region_ID IS NULL " + " AND EXISTS (SELECT * FROM C_Country c" + " WHERE c.C_Country_ID=o.C_Country_ID AND c.HasRegion='Y')" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
if (no != 0)
log.warning("Invalid Region=" + no);
// Product
sql = new StringBuffer("UPDATE I_Order o " + "SET M_Product_ID=(SELECT MAX(M_Product_ID) FROM M_Product p" + " WHERE o.ProductValue=p.Value AND o.AD_Client_ID=p.AD_Client_ID) " + "WHERE M_Product_ID IS NULL AND ProductValue IS NOT NULL" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.fine("Set Product from Value=" + no);
sql = new StringBuffer("UPDATE I_Order o " + "SET M_Product_ID=(SELECT MAX(M_Product_ID) FROM M_Product p" + " WHERE o.UPC=p.UPC AND o.AD_Client_ID=p.AD_Client_ID) " + "WHERE M_Product_ID IS NULL AND UPC IS NOT NULL" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.fine("Set Product from UPC=" + no);
sql = new StringBuffer("UPDATE I_Order o " + "SET M_Product_ID=(SELECT MAX(M_Product_ID) FROM M_Product p" + " WHERE o.SKU=p.SKU AND o.AD_Client_ID=p.AD_Client_ID) " + "WHERE M_Product_ID IS NULL AND SKU IS NOT NULL" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.fine("Set Product fom SKU=" + no);
sql = new StringBuffer("UPDATE I_Order " + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Invalid Product, ' " + "WHERE M_Product_ID IS NULL AND (ProductValue IS NOT NULL OR UPC IS NOT NULL OR SKU IS NOT NULL)" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
if (no != 0)
log.warning("Invalid Product=" + no);
// Charge
sql = new StringBuffer("UPDATE I_Order o " + "SET C_Charge_ID=(SELECT C_Charge_ID FROM C_Charge c" + " WHERE o.ChargeName=c.Name AND o.AD_Client_ID=c.AD_Client_ID) " + "WHERE C_Charge_ID IS NULL AND ChargeName IS NOT NULL AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.fine("Set Charge=" + no);
sql = new StringBuffer("UPDATE I_Order " + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Invalid Charge, ' " + "WHERE C_Charge_ID IS NULL AND (ChargeName IS NOT NULL)" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
if (no != 0)
log.warning("Invalid Charge=" + no);
//
sql = new StringBuffer("UPDATE I_Order " + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Product and Charge, ' " + "WHERE M_Product_ID IS NOT NULL AND C_Charge_ID IS NOT NULL " + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
if (no != 0)
log.warning("Invalid Product and Charge exclusive=" + no);
// Tax
sql = new StringBuffer("UPDATE I_Order o " + "SET C_Tax_ID=(SELECT MAX(C_Tax_ID) FROM C_Tax t" + " WHERE o.TaxIndicator=t.TaxIndicator AND o.AD_Client_ID=t.AD_Client_ID) " + "WHERE C_Tax_ID IS NULL AND TaxIndicator IS NOT NULL" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
log.fine("Set Tax=" + no);
sql = new StringBuffer("UPDATE I_Order " + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Invalid Tax, ' " + "WHERE C_Tax_ID IS NULL AND TaxIndicator IS NOT NULL" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
if (no != 0)
log.warning("Invalid Tax=" + no);
commitEx();
// -- New BPartner ---------------------------------------------------
// Go through Order Records w/o C_BPartner_ID
sql = new StringBuffer("SELECT * FROM I_Order " + "WHERE I_IsImported='N' AND C_BPartner_ID IS NULL").append(clientCheck);
try {
PreparedStatement pstmt = DB.prepareStatement(sql.toString(), get_TrxName());
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
X_I_Order imp = new X_I_Order(getCtx(), rs, get_TrxName());
if (imp.getBPartnerValue() == null) {
if (imp.getEMail() != null)
imp.setBPartnerValue(imp.getEMail());
else if (imp.getName() != null)
imp.setBPartnerValue(imp.getName());
else
continue;
}
if (imp.getName() == null) {
if (imp.getContactName() != null)
imp.setName(imp.getContactName());
else
imp.setName(imp.getBPartnerValue());
}
// BPartner
MBPartner bp = MBPartner.get(getCtx(), imp.getBPartnerValue());
if (bp == null) {
bp = new MBPartner(getCtx(), -1, get_TrxName());
bp.setClientOrg(imp.getAD_Client_ID(), imp.getAD_Org_ID());
bp.setValue(imp.getBPartnerValue());
bp.setName(imp.getName());
if (!bp.save())
continue;
}
imp.setC_BPartner_ID(bp.getC_BPartner_ID());
// BP Location
MBPartnerLocation bpl = null;
MBPartnerLocation[] bpls = bp.getLocations(true);
for (int i = 0; bpl == null && i < bpls.length; i++) {
if (imp.getC_BPartner_Location_ID() == bpls[i].getC_BPartner_Location_ID())
bpl = bpls[i];
else // Same Location ID
if (imp.getC_Location_ID() == bpls[i].getC_Location_ID())
bpl = bpls[i];
else // Same Location Info
if (imp.getC_Location_ID() == 0) {
MLocation loc = bpls[i].getLocation(false);
if (loc.equals(imp.getC_Country_ID(), imp.getC_Region_ID(), imp.getPostal(), "", imp.getCity(), imp.getAddress1(), imp.getAddress2()))
bpl = bpls[i];
}
}
if (bpl == null) {
// New Location
MLocation loc = new MLocation(getCtx(), 0, get_TrxName());
loc.setAddress1(imp.getAddress1());
loc.setAddress2(imp.getAddress2());
loc.setCity(imp.getCity());
loc.setPostal(imp.getPostal());
if (imp.getC_Region_ID() != 0)
loc.setC_Region_ID(imp.getC_Region_ID());
loc.setC_Country_ID(imp.getC_Country_ID());
if (!loc.save())
continue;
//
bpl = new MBPartnerLocation(bp);
bpl.setC_Location_ID(loc.getC_Location_ID());
if (!bpl.save())
continue;
}
imp.setC_Location_ID(bpl.getC_Location_ID());
imp.setBillTo_ID(bpl.getC_BPartner_Location_ID());
imp.setC_BPartner_Location_ID(bpl.getC_BPartner_Location_ID());
// User/Contact
if (imp.getContactName() != null || imp.getEMail() != null || imp.getPhone() != null) {
MUser[] users = bp.getContacts(true);
MUser user = null;
for (int i = 0; user == null && i < users.length; i++) {
String name = users[i].getName();
if (name.equals(imp.getContactName()) || name.equals(imp.getName())) {
user = users[i];
imp.setAD_User_ID(user.getAD_User_ID());
}
}
if (user == null) {
user = new MUser(bp);
if (imp.getContactName() == null)
user.setName(imp.getName());
else
user.setName(imp.getContactName());
user.setEMail(imp.getEMail());
user.setPhone(imp.getPhone());
if (user.save())
imp.setAD_User_ID(user.getAD_User_ID());
}
}
imp.save();
}
// for all new BPartners
rs.close();
pstmt.close();
//
} catch (SQLException e) {
log.log(Level.SEVERE, "BP - " + sql.toString(), e);
}
sql = new StringBuffer("UPDATE I_Order " + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=No BPartner, ' " + "WHERE C_BPartner_ID IS NULL" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
if (no != 0)
log.warning("No BPartner=" + no);
commitEx();
// -- New Orders -----------------------------------------------------
int noInsert = 0;
int noInsertLine = 0;
// Go through Order Records w/o
sql = new StringBuffer("SELECT * FROM I_Order " + "WHERE I_IsImported='N'").append(clientCheck).append(" ORDER BY C_BPartner_ID, BillTo_ID, C_BPartner_Location_ID, I_Order_ID");
try {
PreparedStatement pstmt = DB.prepareStatement(sql.toString(), get_TrxName());
ResultSet rs = pstmt.executeQuery();
//
int oldC_BPartner_ID = 0;
int oldBillTo_ID = 0;
int oldC_BPartner_Location_ID = 0;
String oldDocumentNo = "";
//
MOrder order = null;
int lineNo = 0;
while (rs.next()) {
X_I_Order imp = new X_I_Order(getCtx(), rs, get_TrxName());
String cmpDocumentNo = imp.getDocumentNo();
if (cmpDocumentNo == null)
cmpDocumentNo = "";
// New Order
if (oldC_BPartner_ID != imp.getC_BPartner_ID() || oldC_BPartner_Location_ID != imp.getC_BPartner_Location_ID() || oldBillTo_ID != imp.getBillTo_ID() || !oldDocumentNo.equals(cmpDocumentNo)) {
if (order != null) {
if (m_docAction != null && m_docAction.length() > 0) {
order.setDocAction(m_docAction);
order.processIt(m_docAction);
}
order.saveEx();
}
oldC_BPartner_ID = imp.getC_BPartner_ID();
oldC_BPartner_Location_ID = imp.getC_BPartner_Location_ID();
oldBillTo_ID = imp.getBillTo_ID();
oldDocumentNo = imp.getDocumentNo();
if (oldDocumentNo == null)
oldDocumentNo = "";
//
order = new MOrder(getCtx(), 0, get_TrxName());
order.setClientOrg(imp.getAD_Client_ID(), imp.getAD_Org_ID());
order.setC_DocTypeTarget_ID(imp.getC_DocType_ID());
order.setIsSOTrx(imp.isSOTrx());
if (imp.getDeliveryRule() != null) {
order.setDeliveryRule(imp.getDeliveryRule());
}
if (imp.getDocumentNo() != null)
order.setDocumentNo(imp.getDocumentNo());
// Ship Partner
order.setC_BPartner_ID(imp.getC_BPartner_ID());
order.setC_BPartner_Location_ID(imp.getC_BPartner_Location_ID());
if (imp.getAD_User_ID() != 0)
order.setAD_User_ID(imp.getAD_User_ID());
// Bill Partner
order.setBill_BPartner_ID(imp.getC_BPartner_ID());
order.setBill_Location_ID(imp.getBillTo_ID());
//
if (imp.getDescription() != null)
order.setDescription(imp.getDescription());
order.setC_PaymentTerm_ID(imp.getC_PaymentTerm_ID());
order.setM_PriceList_ID(imp.getM_PriceList_ID());
order.setM_Warehouse_ID(imp.getM_Warehouse_ID());
if (imp.getM_Shipper_ID() != 0)
order.setM_Shipper_ID(imp.getM_Shipper_ID());
// SalesRep from Import or the person running the import
if (imp.getSalesRep_ID() != 0)
order.setSalesRep_ID(imp.getSalesRep_ID());
if (order.getSalesRep_ID() == 0)
order.setSalesRep_ID(getAD_User_ID());
//
if (imp.getAD_OrgTrx_ID() != 0)
order.setAD_OrgTrx_ID(imp.getAD_OrgTrx_ID());
if (imp.getC_Activity_ID() != 0)
order.setC_Activity_ID(imp.getC_Activity_ID());
if (imp.getC_Campaign_ID() != 0)
order.setC_Campaign_ID(imp.getC_Campaign_ID());
if (imp.getC_Project_ID() != 0)
order.setC_Project_ID(imp.getC_Project_ID());
//
if (imp.getDateOrdered() != null)
order.setDateOrdered(imp.getDateOrdered());
if (imp.getDateAcct() != null)
order.setDateAcct(imp.getDateAcct());
// Set Order Source
if (imp.getC_OrderSource() != null)
order.setC_OrderSource_ID(imp.getC_OrderSource_ID());
//
order.saveEx();
noInsert++;
lineNo = 10;
}
imp.setC_Order_ID(order.getC_Order_ID());
// New OrderLine
MOrderLine line = new MOrderLine(order);
line.setLine(lineNo);
lineNo += 10;
if (imp.getM_Product_ID() != 0)
line.setM_Product_ID(imp.getM_Product_ID(), true);
if (imp.getC_Charge_ID() != 0)
line.setC_Charge_ID(imp.getC_Charge_ID());
line.setQty(imp.getQtyOrdered());
line.setPrice();
if (imp.getPriceActual().compareTo(Env.ZERO) != 0)
line.setPrice(imp.getPriceActual());
if (imp.getC_Tax_ID() != 0)
line.setC_Tax_ID(imp.getC_Tax_ID());
else {
line.setTax();
imp.setC_Tax_ID(line.getC_Tax_ID());
}
if (imp.getFreightAmt() != null)
line.setFreightAmt(imp.getFreightAmt());
if (imp.getLineDescription() != null)
line.setDescription(imp.getLineDescription());
line.saveEx();
imp.setC_OrderLine_ID(line.getC_OrderLine_ID());
imp.setI_IsImported(true);
imp.setProcessed(true);
//
if (imp.save())
noInsertLine++;
}
if (order != null) {
if (m_docAction != null && m_docAction.length() > 0) {
order.setDocAction(m_docAction);
order.processIt(m_docAction);
}
order.saveEx();
}
rs.close();
pstmt.close();
} catch (Exception e) {
log.log(Level.SEVERE, "Order - " + sql.toString(), e);
}
// Set Error to indicator to not imported
sql = new StringBuffer("UPDATE I_Order " + "SET I_IsImported='N', Updated=SysDate " + "WHERE I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdate(sql.toString(), get_TrxName());
addLog(0, null, new BigDecimal(no), "@Errors@");
//
addLog(0, null, new BigDecimal(noInsert), "@C_Order_ID@: @Inserted@");
addLog(0, null, new BigDecimal(noInsertLine), "@C_OrderLine_ID@: @Inserted@");
return "#" + noInsert + "/" + noInsertLine;
}
Also used :
SQLException(java.sql.SQLException)
PreparedStatement(java.sql.PreparedStatement)
MBPartner(org.compiere.model.MBPartner)
SQLException(java.sql.SQLException)
BigDecimal(java.math.BigDecimal)
MBPartnerLocation(org.compiere.model.MBPartnerLocation)
MOrder(org.compiere.model.MOrder)
X_I_Order(org.compiere.model.X_I_Order)
ResultSet(java.sql.ResultSet)
MOrderLine(org.compiere.model.MOrderLine)
MLocation(org.compiere.model.MLocation)
MUser(org.compiere.model.MUser)
Example 2 with MLocation
use of org.compiere.model.MLocation in project adempiere by adempiere.
the class ImportBPartner method doIt.
// prepare
/**
* Perform process.
* @return Message
* @throws Exception
*/
protected String doIt() throws java.lang.Exception {
StringBuffer sql = null;
int no = 0;
String clientCheck = getWhereClause();
// Delete Old Imported
if (m_deleteOldImported) {
sql = new StringBuffer("DELETE I_BPartner " + "WHERE I_IsImported='Y'").append(clientCheck);
no = DB.executeUpdateEx(sql.toString(), get_TrxName());
log.fine("Delete Old Impored =" + no);
}
// Set Client, Org, IsActive, Created/Updated
sql = new StringBuffer("UPDATE I_BPartner " + "SET AD_Client_ID = COALESCE (AD_Client_ID, ").append(m_AD_Client_ID).append(")," + " AD_Org_ID = COALESCE (AD_Org_ID, 0)," + " IsActive = COALESCE (IsActive, 'Y')," + " Created = COALESCE (Created, SysDate)," + " CreatedBy = COALESCE (CreatedBy, 0)," + " Updated = COALESCE (Updated, SysDate)," + " UpdatedBy = COALESCE (UpdatedBy, 0)," + " I_ErrorMsg = ' '," + " I_IsImported = 'N' " + "WHERE I_IsImported<>'Y' OR I_IsImported IS NULL");
no = DB.executeUpdateEx(sql.toString(), get_TrxName());
log.fine("Reset=" + no);
ModelValidationEngine.get().fireImportValidate(this, null, null, ImportValidator.TIMING_BEFORE_VALIDATE);
// Set BP_Group
sql = new StringBuffer("UPDATE I_BPartner i " + "SET GroupValue=(SELECT MAX(Value) FROM C_BP_Group g WHERE g.IsDefault='Y'" + " AND g.AD_Client_ID=i.AD_Client_ID) ");
sql.append("WHERE GroupValue IS NULL AND C_BP_Group_ID IS NULL" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdateEx(sql.toString(), get_TrxName());
log.fine("Set Group Default=" + no);
//
sql = new StringBuffer("UPDATE I_BPartner i " + "SET C_BP_Group_ID=(SELECT C_BP_Group_ID FROM C_BP_Group g" + " WHERE i.GroupValue=g.Value AND g.AD_Client_ID=i.AD_Client_ID) " + "WHERE C_BP_Group_ID IS NULL" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdateEx(sql.toString(), get_TrxName());
log.fine("Set Group=" + no);
//
sql = new StringBuffer("UPDATE I_BPartner " + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Invalid Group, ' " + "WHERE C_BP_Group_ID IS NULL" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdateEx(sql.toString(), get_TrxName());
log.config("Invalid Group=" + no);
// Set Country
/**
sql = new StringBuffer ("UPDATE I_BPartner i "
+ "SET CountryCode=(SELECT CountryCode FROM C_Country c WHERE c.IsDefault='Y'"
+ " AND c.AD_Client_ID IN (0, i.AD_Client_ID) AND ROWNUM=1) "
+ "WHERE CountryCode IS NULL AND C_Country_ID IS NULL"
+ " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdateEx(sql.toString(), get_TrxName());
log.fine("Set Country Default=" + no);
**/
//
sql = new StringBuffer("UPDATE I_BPartner i " + "SET C_Country_ID=(SELECT C_Country_ID FROM C_Country c" + " WHERE i.CountryCode=c.CountryCode AND c.AD_Client_ID IN (0, i.AD_Client_ID)) " + "WHERE C_Country_ID IS NULL" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdateEx(sql.toString(), get_TrxName());
log.fine("Set Country=" + no);
//
sql = new StringBuffer("UPDATE I_BPartner " + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Invalid Country, ' " + "WHERE C_Country_ID IS NULL AND (City IS NOT NULL OR Address1 IS NOT NULL)" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdateEx(sql.toString(), get_TrxName());
log.config("Invalid Country=" + no);
// Set Region
sql = new StringBuffer("UPDATE I_BPartner i " + "Set RegionName=(SELECT MAX(Name) FROM C_Region r" + " WHERE r.IsDefault='Y' AND r.C_Country_ID=i.C_Country_ID" + " AND r.AD_Client_ID IN (0, i.AD_Client_ID)) ");
sql.append("WHERE RegionName IS NULL AND C_Region_ID IS NULL" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdateEx(sql.toString(), get_TrxName());
log.fine("Set Region Default=" + no);
//
sql = new StringBuffer("UPDATE I_BPartner i " + "Set C_Region_ID=(SELECT C_Region_ID FROM C_Region r" + " WHERE r.Name=i.RegionName AND r.C_Country_ID=i.C_Country_ID" + " AND r.AD_Client_ID IN (0, i.AD_Client_ID)) " + "WHERE C_Region_ID IS NULL" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdateEx(sql.toString(), get_TrxName());
log.fine("Set Region=" + no);
//
sql = new StringBuffer("UPDATE I_BPartner i " + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Invalid Region, ' " + "WHERE C_Region_ID IS NULL " + " AND EXISTS (SELECT * FROM C_Country c" + " WHERE c.C_Country_ID=i.C_Country_ID AND c.HasRegion='Y')" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdateEx(sql.toString(), get_TrxName());
log.config("Invalid Region=" + no);
// Set Greeting
sql = new StringBuffer("UPDATE I_BPartner i " + "SET C_Greeting_ID=(SELECT C_Greeting_ID FROM C_Greeting g" + " WHERE i.BPContactGreeting=g.Name AND g.AD_Client_ID IN (0, i.AD_Client_ID)) " + "WHERE C_Greeting_ID IS NULL AND BPContactGreeting IS NOT NULL" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdateEx(sql.toString(), get_TrxName());
log.fine("Set Greeting=" + no);
//
sql = new StringBuffer("UPDATE I_BPartner i " + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Invalid Greeting, ' " + "WHERE C_Greeting_ID IS NULL AND BPContactGreeting IS NOT NULL" + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdateEx(sql.toString(), get_TrxName());
log.config("Invalid Greeting=" + no);
// Existing User. Lookup by AD_User.Email - BPartner
sql = new StringBuffer("UPDATE I_BPartner i " + "SET C_BPartner_ID=" + "(SELECT C_BPartner_ID FROM AD_User u " + "WHERE i.EMail=u.EMail AND u.AD_Client_ID=i.AD_Client_ID) " + "WHERE i.EMail IS NOT NULL AND I_IsImported='N'").append(clientCheck);
no = DB.executeUpdateEx(sql.toString(), get_TrxName());
log.fine("Found EMail User=" + no);
// Existing User. Lookup by AD_User.Email - AD_User
sql = new StringBuffer("UPDATE I_BPartner i " + "SET AD_User_ID=" + "(SELECT AD_User_ID FROM AD_User u " + "WHERE i.EMail=u.EMail AND u.AD_Client_ID=i.AD_Client_ID) " + "WHERE i.EMail IS NOT NULL AND I_IsImported='N'").append(clientCheck);
no = DB.executeUpdateEx(sql.toString(), get_TrxName());
log.fine("Found EMail User=" + no);
// Existing BPartner ? Match Value
sql = new StringBuffer("UPDATE I_BPartner i " + "SET C_BPartner_ID=(SELECT C_BPartner_ID FROM C_BPartner p" + " WHERE i.Value=p.Value AND p.AD_Client_ID=i.AD_Client_ID) " + "WHERE C_BPartner_ID IS NULL AND Value IS NOT NULL" + " AND I_IsImported='N'").append(clientCheck);
no = DB.executeUpdateEx(sql.toString(), get_TrxName());
log.fine("Found BPartner=" + no);
// Existing Contact ? Match Name
sql = new StringBuffer("UPDATE I_BPartner i " + "SET AD_User_ID=(SELECT AD_User_ID FROM AD_User c" + " WHERE i.ContactName=c.Name AND i.C_BPartner_ID=c.C_BPartner_ID AND c.AD_Client_ID=i.AD_Client_ID) " + "WHERE C_BPartner_ID IS NOT NULL AND AD_User_ID IS NULL AND ContactName IS NOT NULL" + " AND I_IsImported='N'").append(clientCheck);
no = DB.executeUpdateEx(sql.toString(), get_TrxName());
log.fine("Found Contact=" + no);
// Existing Location ? Exact Match
sql = new StringBuffer("UPDATE I_BPartner i " + "SET C_BPartner_Location_ID=(SELECT C_BPartner_Location_ID" + " FROM C_BPartner_Location bpl INNER JOIN C_Location l ON (bpl.C_Location_ID=l.C_Location_ID)" + " WHERE i.C_BPartner_ID=bpl.C_BPartner_ID AND bpl.AD_Client_ID=i.AD_Client_ID" + " AND (i.Address1=l.Address1 OR (i.Address1 IS NULL AND l.Address1 IS NULL))" + " AND (i.Address2=l.Address2 OR (i.Address2 IS NULL AND l.Address2 IS NULL))" + " AND (i.City=l.City OR (i.City IS NULL AND l.City IS NULL))" + " AND (i.Postal=l.Postal OR (i.Postal IS NULL AND l.Postal IS NULL))" + " AND (i.Postal_Add=l.Postal_Add OR (l.Postal_Add IS NULL AND l.Postal_Add IS NULL))" + " AND i.C_Region_ID=l.C_Region_ID AND i.C_Country_ID=l.C_Country_ID) " + "WHERE C_BPartner_ID IS NOT NULL AND C_BPartner_Location_ID IS NULL" + " AND I_IsImported='N'").append(clientCheck);
no = DB.executeUpdateEx(sql.toString(), get_TrxName());
log.fine("Found Location=" + no);
// Interest Area
sql = new StringBuffer("UPDATE I_BPartner i " + "SET R_InterestArea_ID=(SELECT R_InterestArea_ID FROM R_InterestArea ia " + "WHERE i.InterestAreaName=ia.Name AND ia.AD_Client_ID=i.AD_Client_ID) " + "WHERE R_InterestArea_ID IS NULL AND InterestAreaName IS NOT NULL" + " AND I_IsImported='N'").append(clientCheck);
no = DB.executeUpdateEx(sql.toString(), get_TrxName());
log.fine("Set Interest Area=" + no);
// Value is mandatory error
sql = new StringBuffer("UPDATE I_BPartner " + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Value is mandatory, ' " + "WHERE Value IS NULL " + " AND I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdateEx(sql.toString(), get_TrxName());
log.config("Value is mandatory=" + no);
ModelValidationEngine.get().fireImportValidate(this, null, null, ImportValidator.TIMING_AFTER_VALIDATE);
commitEx();
if (p_IsValidateOnly) {
return "Validated";
}
// -------------------------------------------------------------------
int noInsert = 0;
int noUpdate = 0;
// Go through Records
sql = new StringBuffer("SELECT * FROM I_BPartner " + "WHERE I_IsImported='N'").append(clientCheck);
// gody: 20070113 - Order so the same values are consecutive.
sql.append(" ORDER BY Value, I_BPartner_ID");
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
pstmt = DB.prepareStatement(sql.toString(), get_TrxName());
rs = pstmt.executeQuery();
// Remember Previous BP Value BP is only first one, others are contacts.
// All contacts share BP location.
// bp and bpl declarations before loop, we need them for data.
String Old_BPValue = "";
MBPartner bp = null;
MBPartnerLocation bpl = null;
while (rs.next()) {
// Remember Value - only first occurance of the value is BP
String New_BPValue = rs.getString("Value");
X_I_BPartner impBP = new X_I_BPartner(getCtx(), rs, get_TrxName());
log.fine("I_BPartner_ID=" + impBP.getI_BPartner_ID() + ", C_BPartner_ID=" + impBP.getC_BPartner_ID() + ", C_BPartner_Location_ID=" + impBP.getC_BPartner_Location_ID() + ", AD_User_ID=" + impBP.getAD_User_ID());
if (!New_BPValue.equals(Old_BPValue)) {
// **** Create/Update BPartner ****
bp = null;
if (// Insert new BPartner
impBP.getC_BPartner_ID() == 0) {
bp = new MBPartner(impBP);
ModelValidationEngine.get().fireImportValidate(this, impBP, bp, ImportValidator.TIMING_AFTER_IMPORT);
setTypeOfBPartner(impBP, bp);
if (bp.save()) {
impBP.setC_BPartner_ID(bp.getC_BPartner_ID());
log.finest("Insert BPartner - " + bp.getC_BPartner_ID());
noInsert++;
} else {
sql = new StringBuffer("UPDATE I_BPartner i " + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||").append("'Cannot Insert BPartner, ' ").append("WHERE I_BPartner_ID=").append(impBP.getI_BPartner_ID());
DB.executeUpdateEx(sql.toString(), get_TrxName());
continue;
}
} else // Update existing BPartner
{
bp = new MBPartner(getCtx(), impBP.getC_BPartner_ID(), get_TrxName());
// bp.setValue(impBP.getValue());
if (impBP.getName() != null) {
bp.setName(impBP.getName());
bp.setName2(impBP.getName2());
}
if (impBP.getDUNS() != null)
bp.setDUNS(impBP.getDUNS());
if (impBP.getTaxID() != null)
bp.setTaxID(impBP.getTaxID());
if (impBP.getNAICS() != null)
bp.setNAICS(impBP.getNAICS());
if (impBP.getDescription() != null)
bp.setDescription(impBP.getDescription());
if (impBP.getC_BP_Group_ID() != 0)
bp.setC_BP_Group_ID(impBP.getC_BP_Group_ID());
// Employee values
if (impBP.getBirthday() != null)
bp.setBirthday(impBP.getBirthday());
if (impBP.getFathersName() != null)
bp.setFathersName(impBP.getFathersName());
if (impBP.getBloodGroup() != null)
bp.setBloodGroup(impBP.getBloodGroup());
if (impBP.getPlaceOfBirth() != null)
bp.setPlaceOfBirth(impBP.getPlaceOfBirth());
if (impBP.getGender() != null)
bp.setGender(impBP.getGender());
//
ModelValidationEngine.get().fireImportValidate(this, impBP, bp, ImportValidator.TIMING_AFTER_IMPORT);
setTypeOfBPartner(impBP, bp);
//
if (bp.save()) {
log.finest("Update BPartner - " + bp.getC_BPartner_ID());
noUpdate++;
} else {
sql = new StringBuffer("UPDATE I_BPartner i " + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||").append("'Cannot Update BPartner, ' ").append("WHERE I_BPartner_ID=").append(impBP.getI_BPartner_ID());
DB.executeUpdateEx(sql.toString(), get_TrxName());
continue;
}
}
// **** Create/Update BPartner Location ****
bpl = null;
if (// Update Location
impBP.getC_BPartner_Location_ID() != 0) {
bpl = new MBPartnerLocation(getCtx(), impBP.getC_BPartner_Location_ID(), get_TrxName());
MLocation location = new MLocation(getCtx(), bpl.getC_Location_ID(), get_TrxName());
location.setC_Country_ID(impBP.getC_Country_ID());
location.setC_Region_ID(impBP.getC_Region_ID());
location.setCity(impBP.getCity());
location.setAddress1(impBP.getAddress1());
location.setAddress2(impBP.getAddress2());
location.setPostal(impBP.getPostal());
location.setPostal_Add(impBP.getPostal_Add());
if (!location.save())
log.warning("Location not updated");
else
bpl.setC_Location_ID(location.getC_Location_ID());
if (impBP.getPhone() != null)
bpl.setPhone(impBP.getPhone());
if (impBP.getPhone2() != null)
bpl.setPhone2(impBP.getPhone2());
if (impBP.getFax() != null)
bpl.setFax(impBP.getFax());
ModelValidationEngine.get().fireImportValidate(this, impBP, bpl, ImportValidator.TIMING_AFTER_IMPORT);
bpl.saveEx();
} else // New Location
if (impBP.getC_Country_ID() != 0 && impBP.getAddress1() != null && impBP.getCity() != null) {
MLocation location = new MLocation(getCtx(), impBP.getC_Country_ID(), impBP.getC_Region_ID(), impBP.getCity(), get_TrxName());
location.setAddress1(impBP.getAddress1());
location.setAddress2(impBP.getAddress2());
location.setPostal(impBP.getPostal());
location.setPostal_Add(impBP.getPostal_Add());
if (location.save())
log.finest("Insert Location - " + location.getC_Location_ID());
else {
rollback();
noInsert--;
sql = new StringBuffer("UPDATE I_BPartner i " + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||").append("'Cannot Insert Location, ' ").append("WHERE I_BPartner_ID=").append(impBP.getI_BPartner_ID());
DB.executeUpdateEx(sql.toString(), get_TrxName());
continue;
}
//
bpl = new MBPartnerLocation(bp);
bpl.setC_Location_ID(location.getC_Location_ID());
bpl.setPhone(impBP.getPhone());
bpl.setPhone2(impBP.getPhone2());
bpl.setFax(impBP.getFax());
ModelValidationEngine.get().fireImportValidate(this, impBP, bpl, ImportValidator.TIMING_AFTER_IMPORT);
if (bpl.save()) {
log.finest("Insert BP Location - " + bpl.getC_BPartner_Location_ID());
impBP.setC_BPartner_Location_ID(bpl.getC_BPartner_Location_ID());
} else {
rollback();
noInsert--;
sql = new StringBuffer("UPDATE I_BPartner i " + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||").append("'Cannot Insert BPLocation, ' ").append("WHERE I_BPartner_ID=").append(impBP.getI_BPartner_ID());
DB.executeUpdateEx(sql.toString(), get_TrxName());
continue;
}
}
}
Old_BPValue = New_BPValue;
// **** Create/Update Contact ****
MUser user = null;
if (impBP.getAD_User_ID() != 0) {
user = new MUser(getCtx(), impBP.getAD_User_ID(), get_TrxName());
if (user.getC_BPartner_ID() == 0)
user.setC_BPartner_ID(bp.getC_BPartner_ID());
else if (user.getC_BPartner_ID() != bp.getC_BPartner_ID()) {
rollback();
noInsert--;
sql = new StringBuffer("UPDATE I_BPartner i " + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||").append("'BP of User <> BP, ' ").append("WHERE I_BPartner_ID=").append(impBP.getI_BPartner_ID());
DB.executeUpdateEx(sql.toString(), get_TrxName());
continue;
}
if (impBP.getC_Greeting_ID() != 0)
user.setC_Greeting_ID(impBP.getC_Greeting_ID());
String name = impBP.getContactName();
if (name == null || name.length() == 0)
name = impBP.getEMail();
user.setName(name);
if (impBP.getTitle() != null)
user.setTitle(impBP.getTitle());
if (impBP.getContactDescription() != null)
user.setDescription(impBP.getContactDescription());
if (impBP.getComments() != null)
user.setComments(impBP.getComments());
if (impBP.getPhone() != null)
user.setPhone(impBP.getPhone());
if (impBP.getPhone2() != null)
user.setPhone2(impBP.getPhone2());
if (impBP.getFax() != null)
user.setFax(impBP.getFax());
if (impBP.getEMail() != null)
user.setEMail(impBP.getEMail());
if (impBP.getBirthday() != null)
user.setBirthday(impBP.getBirthday());
if (bpl != null)
user.setC_BPartner_Location_ID(bpl.getC_BPartner_Location_ID());
ModelValidationEngine.get().fireImportValidate(this, impBP, user, ImportValidator.TIMING_AFTER_IMPORT);
if (user.save()) {
log.finest("Update BP Contact - " + user.getAD_User_ID());
} else {
rollback();
noInsert--;
sql = new StringBuffer("UPDATE I_BPartner i " + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||").append("'Cannot Update BP Contact, ' ").append("WHERE I_BPartner_ID=").append(impBP.getI_BPartner_ID());
DB.executeUpdateEx(sql.toString(), get_TrxName());
continue;
}
} else // New Contact
if (impBP.getContactName() != null || impBP.getEMail() != null) {
user = new MUser(bp);
if (impBP.getC_Greeting_ID() != 0)
user.setC_Greeting_ID(impBP.getC_Greeting_ID());
String name = impBP.getContactName();
if (name == null || name.length() == 0)
name = impBP.getEMail();
user.setName(name);
user.setTitle(impBP.getTitle());
user.setDescription(impBP.getContactDescription());
user.setComments(impBP.getComments());
user.setPhone(impBP.getPhone());
user.setPhone2(impBP.getPhone2());
user.setFax(impBP.getFax());
user.setEMail(impBP.getEMail());
user.setBirthday(impBP.getBirthday());
if (bpl != null)
user.setC_BPartner_Location_ID(bpl.getC_BPartner_Location_ID());
ModelValidationEngine.get().fireImportValidate(this, impBP, user, ImportValidator.TIMING_AFTER_IMPORT);
if (user.save()) {
log.finest("Insert BP Contact - " + user.getAD_User_ID());
impBP.setAD_User_ID(user.getAD_User_ID());
} else {
rollback();
noInsert--;
sql = new StringBuffer("UPDATE I_BPartner i " + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||").append("'Cannot Insert BPContact, ' ").append("WHERE I_BPartner_ID=").append(impBP.getI_BPartner_ID());
DB.executeUpdateEx(sql.toString(), get_TrxName());
continue;
}
}
// Interest Area
if (impBP.getR_InterestArea_ID() != 0 && user != null) {
MContactInterest ci = MContactInterest.get(getCtx(), impBP.getR_InterestArea_ID(), user.getAD_User_ID(), true, get_TrxName());
// don't subscribe or re-activate
ci.saveEx();
}
//
impBP.setI_IsImported(true);
impBP.setProcessed(true);
impBP.setProcessing(false);
impBP.saveEx();
commitEx();
}
// for all I_Product
DB.close(rs, pstmt);
} catch (SQLException e) {
rollback();
//log.log(Level.SEVERE, "", e);
throw new DBException(e, sql.toString());
} finally {
DB.close(rs, pstmt);
rs = null;
pstmt = null;
// Set Error to indicator to not imported
sql = new StringBuffer("UPDATE I_BPartner " + "SET I_IsImported='N', Updated=SysDate " + "WHERE I_IsImported<>'Y'").append(clientCheck);
no = DB.executeUpdateEx(sql.toString(), get_TrxName());
addLog(0, null, new BigDecimal(no), "@Errors@");
addLog(0, null, new BigDecimal(noInsert), "@C_BPartner_ID@: @Inserted@");
addLog(0, null, new BigDecimal(noUpdate), "@C_BPartner_ID@: @Updated@");
}
return "";
}
Also used :
DBException(org.adempiere.exceptions.DBException)
SQLException(java.sql.SQLException)
PreparedStatement(java.sql.PreparedStatement)
MBPartner(org.compiere.model.MBPartner)
X_I_BPartner(org.compiere.model.X_I_BPartner)
BigDecimal(java.math.BigDecimal)
MBPartnerLocation(org.compiere.model.MBPartnerLocation)
MContactInterest(org.compiere.model.MContactInterest)
ResultSet(java.sql.ResultSet)
MLocation(org.compiere.model.MLocation)
MUser(org.compiere.model.MUser)
Example 3 with MLocation
use of org.compiere.model.MLocation in project adempiere by adempiere.
the class ADServiceImpl method getLocation.
public LocationDocument getLocation(LocationDocument req) throws XFireFault {
authenticate(webServiceName, "getLocation");
LocationDocument ret = LocationDocument.Factory.newInstance();
Location loc = ret.addNewLocation();
MLocation location = new MLocation(m_cs.getM_ctx(), req.getLocation().getCLocationID(), null);
loc.setAddress1(location.getAddress1());
loc.setAddress2(location.getAddress2());
loc.setCity(location.getCity());
loc.setPostalCode(location.getPostal());
loc.setCCountryID(location.getC_Country_ID());
loc.setCRegionID(location.getC_Region_ID());
loc.setCountries(this.getCountry(location));
return ret;
}
Also used :
MLocation(org.compiere.model.MLocation)
LocationDocument(pl.x3E.adInterface.LocationDocument)
MLocation(org.compiere.model.MLocation)
Location(pl.x3E.adInterface.Location)
Example 4 with MLocation
use of org.compiere.model.MLocation in project adempiere by adempiere.
the class InventoryUtil method getCreateWarehouse.
/**
* Helper Method : Create Warehouse
*/
public static MWarehouse getCreateWarehouse(int AD_Org_ID, String value) {
if (AD_Org_ID <= 0)
AD_Org_ID = getFirst_Org_ID();
Properties ctx = Env.getCtx();
final String whereClause = "AD_Org_ID=? AND Value=?";
MWarehouse wh = new Query(ctx, I_M_Warehouse.Table_Name, whereClause, null).setParameters(AD_Org_ID, value).setClient_ID().firstOnly();
if (wh != null)
return wh;
wh = new MWarehouse(ctx, 0, null);
wh.setAD_Org_ID(AD_Org_ID);
wh.setValue(value);
wh.setName(value);
MLocation loc = new MLocation(ctx, 0, null);
loc.saveEx();
wh.setC_Location_ID(loc.get_ID());
wh.saveEx();
return wh;
}
Also used :
Query(org.compiere.model.Query)
Properties(java.util.Properties)
MLocation(org.compiere.model.MLocation)
MWarehouse(org.compiere.model.MWarehouse)
Example 5 with MLocation
use of org.compiere.model.MLocation in project adempiere by adempiere.
the class WLocation method doGet.
/**
* Process the HTTP Get request - initial Start
* Needs to have parameters FormName and ColumnName
*
* @param request request
* @param response response
* @throws ServletException
* @throws IOException
*/
public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
log.fine("");
HttpSession sess = request.getSession();
WWindowStatus ws = WWindowStatus.get(request);
if (ws == null) {
MobileUtil.createTimeoutPage(request, response, this, null);
return;
}
// Get Mandatory Parameters
String columnName = MobileUtil.getParameter(request, "ColumnName");
log.info("ColumnName=" + columnName + " - " + ws.toString());
//
GridField mField = ws.curTab.getField(columnName);
log.config("ColumnName=" + columnName + ", MField=" + mField);
if (mField == null || columnName == null || columnName.equals("")) {
MobileUtil.createErrorPage(request, response, this, Msg.getMsg(ws.ctx, "ParameterMissing"));
return;
}
MLocation location = null;
Object value = mField.getValue();
if (value != null && value instanceof Integer)
location = new MLocation(ws.ctx, ((Integer) value).intValue(), null);
else
location = new MLocation(ws.ctx, 0, null);
//String targetBase = "parent.WWindow." + WWindow.FORM_NAME + "." + columnName;
String targetBase = "opener.WWindow." + WWindow.FORM_NAME + "." + columnName;
String action = request.getRequestURI();
// Create Document
MobileDoc doc = MobileDoc.createPopup(mField.getHeader());
boolean hasDependents = ws.curTab.hasDependants(columnName);
boolean hasCallout = mField.getCallout().length() > 0;
// Reset
button reset = new button();
// translate
reset.addElement("Reset");
String script = targetBase + "D.value='';" + targetBase + "F.value='';closePopup();";
if (hasDependents || hasCallout)
script += "startUpdate(" + targetBase + "F);";
reset.setOnClick(script);
//
div div = new div();
div.setClass("toolbar");
h1 header = new h1();
header.setID("pageTitle");
div.addElement(header);
a anchor = new a();
anchor.setID("backButton");
anchor.setClass("button");
div.addElement(anchor);
doc.getBody().addElement(fillForm(ws, action, location, targetBase, hasDependents || hasCallout)).addElement(reset).addElement(div);
//
// log.trace(log.l6_Database, doc.toString());
MobileUtil.createResponse(request, response, this, null, doc, true);
}
Also used :
org.apache.ecs.xhtml.a(org.apache.ecs.xhtml.a)
HttpSession(javax.servlet.http.HttpSession)
org.apache.ecs.xhtml.h1(org.apache.ecs.xhtml.h1)
GridField(org.compiere.model.GridField)
org.apache.ecs.xhtml.button(org.apache.ecs.xhtml.button)
org.apache.ecs.xhtml.div(org.apache.ecs.xhtml.div)
MLocation(org.compiere.model.MLocation)
Aggregations
MLocation (org.compiere.model.MLocation)28
MBPartner (org.compiere.model.MBPartner)13
MBPartnerLocation (org.compiere.model.MBPartnerLocation)10
Properties (java.util.Properties)8
MUser (org.compiere.model.MUser)7
PreparedStatement (java.sql.PreparedStatement)6
ResultSet (java.sql.ResultSet)6
HttpSession (javax.servlet.http.HttpSession)6
BigDecimal (java.math.BigDecimal)5
SQLException (java.sql.SQLException)5
Query (org.compiere.model.Query)5
org.apache.ecs.xhtml.button (org.apache.ecs.xhtml.button)4
MInvoice (org.compiere.model.MInvoice)4
MInvoiceLine (org.compiere.model.MInvoiceLine)4
MClient (org.compiere.model.MClient)3
MDocType (org.compiere.model.MDocType)3
Timestamp (java.sql.Timestamp)2
org.apache.ecs.xhtml.b (org.apache.ecs.xhtml.b)2
org.apache.ecs.xhtml.form (org.apache.ecs.xhtml.form)2
org.apache.ecs.xhtml.p (org.apache.ecs.xhtml.p)2
