📄 importinvoice.java
字号:
+ " 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_Invoice 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_Invoice o "
+ "SET M_Product_ID=(SELECT M_Product_ID FROM M_Product p"
+ " WHERE o.ProductValue=p.Value AND o.AD_Client_ID=p.AD_Client_ID AND ROWNUM=1) "
+ "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_Invoice o "
+ "SET M_Product_ID=(SELECT M_Product_ID FROM M_Product p"
+ " WHERE o.UPC=p.UPC AND o.AD_Client_ID=p.AD_Client_ID AND ROWNUM=1) "
+ "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_Invoice o "
+ "SET M_Product_ID=(SELECT M_Product_ID FROM M_Product p"
+ " WHERE o.SKU=p.SKU AND o.AD_Client_ID=p.AD_Client_ID AND ROWNUM=1) "
+ "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_Invoice "
+ "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);
// Tax
sql = new StringBuffer ("UPDATE I_Invoice o "
+ "SET C_Tax_ID=(SELECT C_Tax_ID FROM C_Tax t"
+ " WHERE o.TaxIndicator=t.TaxIndicator AND o.AD_Client_ID=t.AD_Client_ID AND ROWNUM=1) "
+ "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_Invoice "
+ "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);
commit();
// -- New BPartner ---------------------------------------------------
// Go through Invoice Records w/o C_BPartner_ID
sql = new StringBuffer ("SELECT * FROM I_Invoice "
+ "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_Invoice imp = new X_I_Invoice (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];
// Same Location ID
else if (imp.getC_Location_ID() == bpls[i].getC_Location_ID())
bpl = bpls[i];
// Same Location Info
else if (imp.getC_Location_ID() == 0)
{
MLocation loc = bpl.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 (imp.getC_Location_ID ());
if (!bpl.save ())
continue;
}
imp.setC_Location_ID (bpl.getC_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, "CreateBP", e);
}
sql = new StringBuffer ("UPDATE I_Invoice "
+ "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);
commit();
// -- New Invoices -----------------------------------------------------
int noInsert = 0;
int noInsertLine = 0;
// Go through Invoice Records w/o
sql = new StringBuffer ("SELECT * FROM I_Invoice "
+ "WHERE I_IsImported='N'").append (clientCheck)
.append(" ORDER BY C_BPartner_ID, C_BPartner_Location_ID, I_Invoice_ID");
try
{
PreparedStatement pstmt = DB.prepareStatement (sql.toString(), get_TrxName());
ResultSet rs = pstmt.executeQuery ();
// Group Change
int oldC_BPartner_ID = 0;
int oldC_BPartner_Location_ID = 0;
String oldDocumentNo = "";
//
MInvoice invoice = null;
int lineNo = 0;
while (rs.next ())
{
X_I_Invoice imp = new X_I_Invoice (getCtx (), rs, null);
String cmpDocumentNo = imp.getDocumentNo();
if (cmpDocumentNo == null)
cmpDocumentNo = "";
// New Invoice
if (oldC_BPartner_ID != imp.getC_BPartner_ID()
|| oldC_BPartner_Location_ID != imp.getC_BPartner_Location_ID()
|| !oldDocumentNo.equals(cmpDocumentNo) )
{
if (invoice != null)
{
invoice.processIt(m_docAction);
invoice.save();
}
// Group Change
oldC_BPartner_ID = imp.getC_BPartner_ID();
oldC_BPartner_Location_ID = imp.getC_BPartner_Location_ID();
oldDocumentNo = imp.getDocumentNo();
if (oldDocumentNo == null)
oldDocumentNo = "";
//
invoice = new MInvoice (getCtx(), 0, null);
invoice.setClientOrg (imp.getAD_Client_ID(), imp.getAD_Org_ID());
invoice.setC_DocTypeTarget_ID(imp.getC_DocType_ID());
invoice.setIsSOTrx(imp.isSOTrx());
if (imp.getDocumentNo() != null)
invoice.setDocumentNo(imp.getDocumentNo());
//
invoice.setC_BPartner_ID(imp.getC_BPartner_ID());
invoice.setC_BPartner_Location_ID(imp.getC_BPartner_Location_ID());
if (imp.getAD_User_ID() != 0)
invoice.setAD_User_ID(imp.getAD_User_ID());
//
if (imp.getDescription() != null)
invoice.setDescription(imp.getDescription());
invoice.setC_PaymentTerm_ID(imp.getC_PaymentTerm_ID());
invoice.setM_PriceList_ID(imp.getM_PriceList_ID());
// SalesRep from Import or the person running the import
if (imp.getSalesRep_ID() != 0)
invoice.setSalesRep_ID(imp.getSalesRep_ID());
if (invoice.getSalesRep_ID() == 0)
invoice.setSalesRep_ID(getAD_User_ID());
//
if (imp.getAD_OrgTrx_ID() != 0)
invoice.setAD_OrgTrx_ID(imp.getAD_OrgTrx_ID());
if (imp.getC_Activity_ID() != 0)
invoice.setC_Activity_ID(imp.getC_Activity_ID());
if (imp.getC_Campaign_ID() != 0)
invoice.setC_Campaign_ID(imp.getC_Campaign_ID());
if (imp.getC_Project_ID() != 0)
invoice.setC_Project_ID(imp.getC_Project_ID());
//
if (imp.getDateInvoiced() != null)
invoice.setDateInvoiced(imp.getDateInvoiced());
if (imp.getDateAcct() != null)
invoice.setDateAcct(imp.getDateAcct());
//
invoice.save();
noInsert++;
lineNo = 10;
}
imp.setC_Invoice_ID (invoice.getC_Invoice_ID());
// New InvoiceLine
MInvoiceLine line = new MInvoiceLine (invoice);
if (imp.getLineDescription() != null)
line.setDescription(imp.getLineDescription());
line.setLine(lineNo);
lineNo += 10;
if (imp.getM_Product_ID() != 0)
line.setM_Product_ID(imp.getM_Product_ID(), true);
line.setQty(imp.getQtyOrdered());
line.setPrice();
BigDecimal price = imp.getPriceActual();
if (price != null && Env.ZERO.compareTo(price) != 0)
line.setPrice(price);
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());
}
BigDecimal taxAmt = imp.getTaxAmt();
if (taxAmt != null && Env.ZERO.compareTo(taxAmt) != 0)
line.setTaxAmt(taxAmt);
line.save();
//
imp.setC_InvoiceLine_ID(line.getC_InvoiceLine_ID());
imp.setI_IsImported(true);
imp.setProcessed(true);
//
if (imp.save())
noInsertLine++;
}
if (invoice != null)
{
invoice.processIt (m_docAction);
invoice.save();
}
rs.close();
pstmt.close();
}
catch (Exception e)
{
log.log(Level.SEVERE, "CreateInvoice", e);
}
// Set Error to indicator to not imported
sql = new StringBuffer ("UPDATE I_Invoice "
+ "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_Invoice_ID@: @Inserted@");
addLog (0, null, new BigDecimal (noInsertLine), "@C_InvoiceLine_ID@: @Inserted@");
return "";
} // doIt
} // ImportInvoice
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -