Search in sources :

Example 26 with MLocation

use of org.compiere.model.MLocation in project adempiere by adempiere.

the class WLocation method doPost.

//  doGet
/**
	 *  Process the HTTP Post request (update Address)
	 *
	 *  @param request request
	 *  @param response response
	 *  @throws ServletException
	 *  @throws IOException
	 */
public void doPost(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;
    }
    int C_Location_ID = MobileUtil.getParameterAsInt(request, P_C_LOCATION_ID);
    String targetBase = "opener.WWindow." + WWindow.FORM_NAME + ".C_Location_ID";
    //  Create Location
    MLocation location = new MLocation(ws.ctx, C_Location_ID, null);
    log.fine("doPost updating C_Location_ID=" + C_Location_ID + " - " + targetBase);
    location.setAddress1(MobileUtil.getParameter(request, P_ADDRESS1));
    location.setAddress2(MobileUtil.getParameter(request, P_ADDRESS2));
    location.setAddress3(MobileUtil.getParameter(request, P_ADDRESS3));
    location.setAddress4(MobileUtil.getParameter(request, P_ADDRESS4));
    location.setCity(MobileUtil.getParameter(request, P_CITY));
    location.setPostal(MobileUtil.getParameter(request, P_POSTAL));
    location.setC_Country_ID(MobileUtil.getParameterAsInt(request, P_C_COUNTRY_ID));
    location.setC_Region_ID(MobileUtil.getParameterAsInt(request, P_C_REGION_ID));
    System.out.println("location =========== " + location);
    //  Document
    MobileDoc doc = MobileDoc.createPopup("WLocation");
    //  Save Location
    location.saveEx();
    C_Location_ID = location.getC_Location_ID();
    if (C_Location_ID == 0)
        doc.getBody().addElement(new p(new b("ERROR - Location=0")));
    doc.getBody().addElement(new p().addElement(location.toString()));
    //  Update Target
    script script = new script(new StringBuffer().append(targetBase).append("D.value='").append(C_Location_ID).append("';").append(targetBase).append("F.value='").append(location.toString()).append("';closePopup();").toString());
    doc.getBody().addElement(script);
    log.fine("script=" + script.toString());
    //
    form myForm = null;
    myForm = new form();
    table table = new table();
    table.setID("WLocation");
    button button = new button();
    button.addElement("ok");
    StringBuffer script2 = new StringBuffer();
    String targetBase2 = "opener.document.WForm.C_Location_ID";
    script2.append(targetBase2).append("D.value='").append(C_Location_ID).append("';").append(targetBase2).append("F.value='").append(location.toString()).append("';submit();window.close();");
    button.setOnClick(script2.toString());
    table.addElement(button);
    myForm.addElement(table);
    doc.getBody().addElement(myForm);
    MobileUtil.createResponse(request, response, this, null, doc, true);
}
Also used : org.apache.ecs.xhtml.b(org.apache.ecs.xhtml.b) HttpSession(javax.servlet.http.HttpSession) org.apache.ecs.xhtml.script(org.apache.ecs.xhtml.script) org.apache.ecs.xhtml.p(org.apache.ecs.xhtml.p) org.apache.ecs.xhtml.button(org.apache.ecs.xhtml.button) org.apache.ecs.xhtml.form(org.apache.ecs.xhtml.form) MLocation(org.compiere.model.MLocation) org.apache.ecs.xhtml.table(org.apache.ecs.xhtml.table)

Example 27 with MLocation

use of org.compiere.model.MLocation in project adempiere by adempiere.

the class InfoBPartnerPanel method refresh.

/**
	 * 	Refresh Query
	 */
protected void refresh() {
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    int leadRowKey = 0;
    if (p_table != null || p_table.getRowCount() > 0)
        leadRowKey = p_table.getLeadRowKey();
    if (m_C_BPartner_ID != leadRowKey) {
        //  From the main table
        m_C_BPartner_ID = leadRowKey;
    }
    if (detailTabBox.getSelectedIndex() == 0) {
        //  Contact tab	
        log.finest(m_sqlContact);
        try {
            pstmt = DB.prepareStatement(m_sqlContact, null);
            pstmt.setInt(1, m_C_BPartner_ID);
            rs = pstmt.executeQuery();
            contactTbl.loadTable(rs);
            rs.close();
        } catch (Exception e) {
            log.log(Level.WARNING, m_sqlContact, e);
        } finally {
            DB.close(rs, pstmt);
            rs = null;
            pstmt = null;
        }
    } else {
        //  Address tab	
        log.finest(m_sqlAddress);
        try {
            pstmt = DB.prepareStatement(m_sqlAddress, null);
            pstmt.setInt(1, m_C_BPartner_ID);
            rs = pstmt.executeQuery();
            addressTbl.loadTable(rs);
            rs.close();
        } catch (Exception e) {
            log.log(Level.WARNING, m_sqlContact, e);
        } finally {
            DB.close(rs, pstmt);
            rs = null;
            pstmt = null;
        }
        String trxName = Trx.createTrxName();
        for (int row = 0; row < addressTbl.getRowCount(); row++) {
            int loc_id = 0;
            Object loc_data = addressTbl.getValueAt(row, addressTbl.getKeyColumnIndex());
            if (loc_data != null && loc_data instanceof IDColumn) {
                IDColumn dataColumn = (IDColumn) loc_data;
                loc_id = dataColumn.getRecord_ID();
            }
            MLocation loc = MLocation.getBPLocation(Env.getCtx(), loc_id, trxName);
            addressTbl.setValueAt(loc.toString(), row, ADDRESS_INDEX);
        }
        Trx.get(trxName, false).close();
        addressTbl.autoSize();
    }
}
Also used : IDColumn(org.compiere.minigrid.IDColumn) ResultSet(java.sql.ResultSet) PreparedStatement(java.sql.PreparedStatement) MLocation(org.compiere.model.MLocation) SQLException(java.sql.SQLException)

Example 28 with MLocation

use of org.compiere.model.MLocation in project adempiere by adempiere.

the class ImportInvoice method doIt.

//	prepare
/**
	 *  Perform process.
	 *  @return clear 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_Invoice " + "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_Invoice " + "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_Invoice 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("UPDATE I_Invoice o " + "SET C_DocType_ID=(SELECT C_DocType_ID FROM C_DocType d WHERE d.Name=o.DocTypeName" + " AND d.DocBaseType IN ('API','APC') 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());
    if (no != 0)
        log.fine("Set PO DocType=" + no);
    sql = new StringBuffer("UPDATE I_Invoice o " + "SET C_DocType_ID=(SELECT C_DocType_ID FROM C_DocType d WHERE d.Name=o.DocTypeName" + " AND d.DocBaseType IN ('ARI','ARC') 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());
    if (no != 0)
        log.fine("Set SO DocType=" + no);
    sql = new StringBuffer("UPDATE I_Invoice o " + "SET C_DocType_ID=(SELECT C_DocType_ID FROM C_DocType d WHERE d.Name=o.DocTypeName" + " AND d.DocBaseType IN ('API','ARI','APC','ARC') 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());
    if (no != 0)
        log.fine("Set DocType=" + no);
    sql = new StringBuffer("UPDATE I_Invoice " + "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("UPDATE I_Invoice o " + "SET C_DocType_ID=(SELECT MAX(C_DocType_ID) FROM C_DocType d WHERE d.IsDefault='Y'" + " AND d.DocBaseType='API' 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());
    if (no != 0)
        log.fine("Set PO Default DocType=" + no);
    sql = new StringBuffer("UPDATE I_Invoice o " + "SET C_DocType_ID=(SELECT MAX(C_DocType_ID) FROM C_DocType d WHERE d.IsDefault='Y'" + " AND d.DocBaseType='ARI' 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());
    if (no != 0)
        log.fine("Set SO Default DocType=" + no);
    sql = new StringBuffer("UPDATE I_Invoice o " + "SET C_DocType_ID=(SELECT MAX(C_DocType_ID) FROM C_DocType d WHERE d.IsDefault='Y'" + " AND d.DocBaseType IN('ARI','API') 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());
    if (no != 0)
        log.fine("Set Default DocType=" + no);
    sql = new StringBuffer("UPDATE I_Invoice " + "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_Invoice o SET IsSOTrx='Y' " + "WHERE EXISTS (SELECT * FROM C_DocType d WHERE o.C_DocType_ID=d.C_DocType_ID AND d.DocBaseType='ARI' 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_Invoice o SET IsSOTrx='N' " + "WHERE EXISTS (SELECT * FROM C_DocType d WHERE o.C_DocType_ID=d.C_DocType_ID AND d.DocBaseType='API' 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_Invoice 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_Invoice 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_Invoice 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_Invoice 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_Invoice " + "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);
    //	Payment Term
    sql = new StringBuffer("UPDATE I_Invoice 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_Invoice 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_Invoice " + "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);
    // globalqss - Add project and activity
    //	Project
    sql = new StringBuffer("UPDATE I_Invoice o " + "SET C_Project_ID=(SELECT C_Project_ID FROM C_Project p" + " WHERE o.ProjectValue=p.Value AND o.AD_Client_ID=p.AD_Client_ID) " + "WHERE C_Project_ID IS NULL AND ProjectValue IS NOT NULL AND I_IsImported<>'Y'").append(clientCheck);
    no = DB.executeUpdate(sql.toString(), get_TrxName());
    log.fine("Set Project=" + no);
    sql = new StringBuffer("UPDATE I_Invoice " + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Invalid Project, ' " + "WHERE C_Project_ID IS NULL AND (ProjectValue IS NOT NULL)" + " AND I_IsImported<>'Y'").append(clientCheck);
    no = DB.executeUpdate(sql.toString(), get_TrxName());
    if (no != 0)
        log.warning("Invalid Project=" + no);
    //	Activity
    sql = new StringBuffer("UPDATE I_Invoice o " + "SET C_Activity_ID=(SELECT C_Activity_ID FROM C_Activity p" + " WHERE o.ActivityValue=p.Value AND o.AD_Client_ID=p.AD_Client_ID) " + "WHERE C_Activity_ID IS NULL AND ActivityValue IS NOT NULL AND I_IsImported<>'Y'").append(clientCheck);
    no = DB.executeUpdate(sql.toString(), get_TrxName());
    log.fine("Set Activity=" + no);
    sql = new StringBuffer("UPDATE I_Invoice " + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Invalid Activity, ' " + "WHERE C_Activity_ID IS NULL AND (ActivityValue IS NOT NULL)" + " AND I_IsImported<>'Y'").append(clientCheck);
    no = DB.executeUpdate(sql.toString(), get_TrxName());
    if (no != 0)
        log.warning("Invalid Activity=" + no);
    // globalqss - add charge
    //	Charge
    sql = new StringBuffer("UPDATE I_Invoice o " + "SET C_Charge_ID=(SELECT C_Charge_ID FROM C_Charge p" + " WHERE o.ChargeName=p.Name AND o.AD_Client_ID=p.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_Invoice " + "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);
    //
    //	BP from EMail
    sql = new StringBuffer("UPDATE I_Invoice 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_Invoice 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_Invoice 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_Invoice 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_Invoice o " + "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 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 Location from BPartner
    sql = new StringBuffer("UPDATE I_Invoice 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.IsBillTo='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_Invoice " + "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=No BP Location, ' " + "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());
    if (no != 0)
        log.warning("No BP Location=" + no);
    //	Set Country
    /**
		sql = new StringBuffer ("UPDATE I_Invoice 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_Invoice 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_Invoice " + "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_Invoice 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_Invoice 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_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 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_Invoice 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_Invoice 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_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);
    // globalqss - charge and product are exclusive
    sql = new StringBuffer("UPDATE I_Invoice " + "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_Invoice 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_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);
    commitEx();
    //	-- 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];
                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(imp.getC_Location_ID() > 0 ? imp.getC_Location_ID() : loc.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);
    commitEx();
    //	-- 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.saveEx();
                }
                //	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());
                //ADEMPIERE-84
                if (imp.getInvoiceCollectionType() != null)
                    invoice.setInvoiceCollectionType(imp.getInvoiceCollectionType());
                if (imp.getDunningGrace() != null)
                    invoice.setDunningGrace(imp.getDunningGrace());
                if (imp.getC_DunningLevel_ID() != 0)
                    invoice.setC_DunningLevel_ID(imp.getC_DunningLevel_ID());
                //
                invoice.saveEx();
                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);
            // globalqss - import invoice with charges
            if (imp.getC_Charge_ID() != 0)
                line.setC_Charge_ID(imp.getC_Charge_ID());
            // globalqss - [2855673] - assign dimensions to lines also in case they're different 
            if (imp.getC_Activity_ID() != 0)
                line.setC_Activity_ID(imp.getC_Activity_ID());
            if (imp.getC_Campaign_ID() != 0)
                line.setC_Campaign_ID(imp.getC_Campaign_ID());
            if (imp.getC_Project_ID() != 0)
                line.setC_Project_ID(imp.getC_Project_ID());
            //
            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.saveEx();
            //
            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.saveEx();
        }
        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 "";
}
Also used : SQLException(java.sql.SQLException) MInvoiceLine(org.compiere.model.MInvoiceLine) MInvoice(org.compiere.model.MInvoice) PreparedStatement(java.sql.PreparedStatement) MBPartner(org.compiere.model.MBPartner) BigDecimal(java.math.BigDecimal) SQLException(java.sql.SQLException) MBPartnerLocation(org.compiere.model.MBPartnerLocation) X_I_Invoice(org.compiere.model.X_I_Invoice) ResultSet(java.sql.ResultSet) MLocation(org.compiere.model.MLocation) MUser(org.compiere.model.MUser)

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