Examples with MSequence org.compiere.model.MSequence used on opensource projects

Example 6 with MSequence

use of org.compiere.model.MSequence in project lar_361 by comitsrl.

the class LCO_DianGenerateXML method doIt.

// prepare
/**
 * 	Process
 *	@return message
 *	@throws Exception
 */
protected String doIt() throws Exception {
    /**
     *Genera Envios XML
     *	Para Schedule
     *  		Para LCO_SendScheduleLine Order by ID
     *	    	Idx = Idx + 1
     *	    	Si Idx MOD Max_Records = 1
     *	      		Asigne CumulatedAmt(Posicion=1), RecordsQty del XML Header anterior
     *	      		Cree nuevo LCO_DIAN_XML_Header
     *	      		Asigne CurrentNext --Secuencia env�o por a�o
     *	    	Fin Si
     *	    	Asigne LCO_SendScheduleLine.LCO_DIAN_XML_Header_ID
     *	    	Sume CumulatedAmt
     *	    	Cuente RecordsQty
     *  		Fin LCO_SendScheduleLine
     *  	Asigne CumulatedAmt, RecordsQty del ultimo XML Header
     *  	Fin Schedule
     *  LCO_SendSchedule.processed = Y --Content ReadOnly
     */
    X_LCO_DIAN_SendSchedule sendScheduleProcess = new X_LCO_DIAN_SendSchedule(getCtx(), p_LCO_DIAN_SendSchedule_ID, get_TrxName());
    if (!sendScheduleProcess.isGenerated()) {
        throw new AdempiereUserError("SendSchedule has not been generated before");
    }
    if (sendScheduleProcess.isProcessed()) {
        throw new AdempiereUserError("SendSchedule has been processed before");
    }
    MLCODIANFormat format = new MLCODIANFormat(getCtx(), sendScheduleProcess.getLCO_DIAN_Format_ID(), get_TrxName());
    MSequence seqxml = new MSequence(getCtx(), format.getAD_Sequence_ID(), get_TrxName());
    log.info("LCO_DIAN_SendSchedule_ID =  " + p_LCO_DIAN_SendSchedule_ID);
    if (sendScheduleProcess.getLCO_DIAN_SendSchedule_ID() == 0)
        throw new AdempiereUserError("@No@ @LCO_DIAN_SendSchedule@");
    String sql = "SELECT * FROM LCO_DIAN_SendScheduleLine " + "WHERE LCO_DIAN_SendSchedule_ID = ? AND (lco_dian_xml_header_id = 0 or lco_dian_xml_header_id IS NULL) AND IsActive='Y' " + "ORDER BY LCO_DIAN_SendScheduleLine_ID";
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    X_LCO_DIAN_XML_Header xmlh = null;
    BigDecimal cumulatedAmt = Env.ZERO;
    int cnt = 0;
    int cntmedia = 0;
    try {
        pstmt = DB.prepareStatement(sql, get_TrxName());
        pstmt.setInt(1, p_LCO_DIAN_SendSchedule_ID);
        rs = pstmt.executeQuery();
        // para ScheduleLine
        while (rs.next()) {
            X_LCO_DIAN_SendScheduleLine dssl = new X_LCO_DIAN_SendScheduleLine(getCtx(), rs, get_TrxName());
            if (xmlh == null) {
                xmlh = new X_LCO_DIAN_XML_Header(getCtx(), 0, get_TrxName());
                cntmedia++;
                xmlh.setLCO_DIAN_SendSchedule_ID(sendScheduleProcess.getLCO_DIAN_SendSchedule_ID());
                int p_seqEnvio = seqxml.getNextID();
                seqxml.saveEx();
                xmlh.setSequence(p_seqEnvio);
                xmlh.saveEx();
            }
            cnt++;
            cumulatedAmt = cumulatedAmt.add(dssl.getFieldAmt1());
            // Asigne LCO_SendScheduleLine.LCO_DIAN_XML_Header_ID
            dssl.setLCO_DIAN_XML_Header_ID(xmlh.getLCO_DIAN_XML_Header_ID());
            dssl.saveEx();
            if (cnt == format.getMaxXMLRecords()) {
                // Asigne CumulatedAmt, RecordsQty del ultimo XML Header
                xmlh.setCumulatedAmt(cumulatedAmt);
                xmlh.setRecordsQty(new BigDecimal(cnt));
                xmlh.saveEx();
                xmlh = null;
                cnt = 0;
            }
        }
    // Fin LCO_SendScheduleLine
    } catch (SQLException e) {
        log.log(Level.SEVERE, sql, e);
        throw e;
    } finally {
        DB.close(rs, pstmt);
        rs = null;
        pstmt = null;
    }
    // last header not saved
    if (xmlh != null) {
        // Asigne CumulatedAmt, RecordsQty del ultimo XML Header
        xmlh.setCumulatedAmt(cumulatedAmt);
        xmlh.setRecordsQty(new BigDecimal(cnt));
        xmlh.saveEx();
        xmlh = null;
    }
    // LCO_SendSchedule.processed = Y --Content ReadOnly
    sendScheduleProcess.setProcessed(true);
    sendScheduleProcess.saveEx();
    return "@LCO_MediaInserted@=" + cntmedia;
}

Example 7 with MSequence

use of org.compiere.model.MSequence in project idempiere by idempiere.

the class SequenceCheck method checkTableID.

// checkTableSequences
/**
 * 	Check Table Sequence ID values
 *	@param ctx context
 *	@param sp server process or null
 */
private static void checkTableID(Properties ctx, SvrProcess sp) {
    int IDRangeEnd = DB.getSQLValue(null, "SELECT IDRangeEnd FROM AD_System");
    if (IDRangeEnd <= 0)
        IDRangeEnd = DB.getSQLValue(null, "SELECT MIN(IDRangeStart)-1 FROM AD_Replication");
    if (s_log.isLoggable(Level.INFO))
        s_log.info("IDRangeEnd = " + IDRangeEnd);
    // 
    String sql = "SELECT * FROM AD_Sequence " + "WHERE IsTableID='Y' " + "ORDER BY Name";
    int counter = 0;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    String trxName = null;
    if (sp != null)
        trxName = sp.get_TrxName();
    try {
        pstmt = DB.prepareStatement(sql, trxName);
        rs = pstmt.executeQuery();
        while (rs.next()) {
            MSequence seq = new MSequence(ctx, rs, trxName);
            /* NOTE: When using native sequences - every time the sequence check process is run
				 * a sequence number is lost on all sequences - because with native sequences
				 * reading the sequence consumes a number
				 */
            String tableValidation = seq.validateTableIDValue();
            if (tableValidation != null) {
                if (sp != null)
                    sp.addLog(0, null, null, tableValidation);
                else
                    s_log.fine(tableValidation);
                if (seq.save())
                    counter++;
                else
                    s_log.severe("Not updated: " + seq);
            }
        }
    } catch (Exception e) {
        s_log.log(Level.SEVERE, sql, e);
        throw new AdempiereException(e);
    } finally {
        DB.close(rs, pstmt);
        rs = null;
        pstmt = null;
    }
    if (s_log.isLoggable(Level.FINE))
        s_log.fine("#" + counter);
}

Example 8 with MSequence

use of org.compiere.model.MSequence in project idempiere by idempiere.

the class FactReconcile method doIt.

// prepare
/**
 * 	DoIt
 *	@return Message
 *	@throws Exception
 */
protected String doIt() throws Exception {
    if (log.isLoggable(Level.INFO))
        log.info("Reconcile Account: " + account.getName());
    String subselect = "null";
    MRule rule = MRule.get(getCtx(), ruleID);
    if (rule == null || rule.is_new() || !rule.getRuleType().equals("Q") || !rule.getEventType().equals("R"))
        return "Invalid rule for account reconciliation.";
    else
        subselect = rule.getScript();
    if (log.isLoggable(Level.FINE))
        log.log(Level.FINE, "Rule subselect: " + subselect);
    /*  example matching rules:
		 * 
		 * 
		// ar/ap TRade (Receivables/Vendor Liability)
		if ( type.equals("TR") )
			subselect = " (CASE WHEN fa.AD_Table_ID = " + MInvoice.Table_ID +
			" THEN 'C_Invoice:' || fa.Record_ID " +
			" WHEN fa.AD_Table_ID = " + MAllocationHdr.Table_ID +
			" THEN (SELECT 'C_Invoice:' || al.C_Invoice_ID FROM C_AllocationLine al " +
			" WHERE al.C_AllocationHdr_ID = fa.Record_ID " +
			" AND al.C_AllocationLine_ID = fa.Line_ID ) END)";
		// Bank in Transit
		else if ( type.equals("BT"))
			subselect = " (CASE WHEN fa.AD_Table_ID = " + MPayment.Table_ID +
			" THEN 'C_Payment:' || fa.Record_ID " +
			" WHEN fa.AD_Table_ID = " + MBankStatement.Table_ID +
			" THEN (SELECT 'C_Payment:' || bsl.C_Payment_ID FROM C_BankStatementLine bsl " +
			" WHERE bsl.C_BankStatement_ID = fa.Record_ID " +
			" AND bsl.C_BankStatementLine_ID = fa.Line_ID ) END)";
		// Payment Clearing (unallocated cash/payment selection)
		else if ( type.equals("PC") )
			subselect = " (CASE WHEN fa.AD_Table_ID = " + MPayment.Table_ID +
			" THEN 'C_Payment:' || fa.Record_ID " +
			" WHEN fa.AD_Table_ID = " + MAllocationHdr.Table_ID +
			" THEN (SELECT 'C_Payment:' || al.C_Payment_ID FROM C_AllocationLine al " +
			" WHERE al.C_AllocationHdr_ID = fa.Record_ID " +
			" AND al.C_AllocationLine_ID = fa.Line_ID ) END)";
			
			*/
    String sql = "";
    if (log.isLoggable(Level.INFO))
        log.info("AD_PInstance_ID= " + getAD_PInstance_ID());
    PreparedStatement pstmt = null;
    int count;
    int unmatched;
    MSequence seq = MSequence.get(getCtx(), MFactReconciliation.Table_Name);
    if (seq == null)
        throw new AdempiereException("No sequence for Fact_Reconciliation table");
    try {
        // add new facts into reconciliation table
        sql = "INSERT into Fact_Reconciliation " + "(Fact_Reconciliation_ID, AD_Client_ID, AD_Org_ID, Created, CreatedBy, Updated, UpdatedBy, " + "IsActive, Fact_Acct_ID) " + "SELECT nextIDFunc(?, 'N'), AD_Client_ID, AD_Org_ID, Created, CreatedBy, " + "Updated, UpdatedBy, IsActive, " + "Fact_Acct_ID " + "FROM Fact_Acct f " + "WHERE Account_ID = ? " + "AND NOT EXISTS (SELECT 1 FROM Fact_Reconciliation r " + "WHERE r.Fact_Acct_ID = f.Fact_Acct_ID) ";
        pstmt = DB.prepareStatement(sql, get_TrxName());
        pstmt.setInt(1, seq.getAD_Sequence_ID());
        pstmt.setInt(2, account.get_ID());
        count = pstmt.executeUpdate();
        DB.close(pstmt);
        pstmt = null;
        if (log.isLoggable(Level.FINE))
            log.log(Level.FINE, "Inserted " + count + " new facts into Fact_Reconciliation");
        if (DB.isGenerateUUIDSupported())
            DB.executeUpdateEx("UPDATE Fact_Reconciliation SET Fact_Reconciliation_UU=generate_uuid() WHERE Fact_Reconciliation_UU IS NULL", get_TrxName());
        else
            UUIDGenerator.updateUUID(MColumn.get(getCtx(), MFactReconciliation.Table_Name, PO.getUUIDColumnName(MFactReconciliation.Table_Name)), get_TrxName());
        // set the matchcode based on the rule found in AD_Rule
        // which is a sql fragment that returns a string based on the accounting fact
        sql = "UPDATE Fact_Reconciliation " + "SET MatchCode = (" + subselect + " ) " + "WHERE MatchCode is null " + "AND (SELECT f.Account_ID FROM Fact_Acct f " + "     WHERE f.Fact_Acct_ID = Fact_Reconciliation.Fact_Acct_ID ) = ? " + "AND ( " + subselect + " ) IS NOT NULL ";
        pstmt = DB.prepareStatement(sql, get_TrxName());
        pstmt.setInt(1, account.get_ID());
        count = pstmt.executeUpdate();
        DB.close(pstmt);
        pstmt = null;
        if (log.isLoggable(Level.FINE))
            log.log(Level.FINE, "Updated " + count + " match codes.");
        // remove any matchcodes that don't balance to zero
        sql = "UPDATE Fact_Reconciliation " + "SET MatchCode = null " + " WHERE  (SELECT f1.Account_ID FROM Fact_Acct f1 " + "         WHERE f1.Fact_Acct_ID=Fact_Reconciliation.Fact_Acct_ID) = ? " + " AND (SELECT SUM(f2.amtacctdr-f2.amtacctcr) FROM Fact_Reconciliation r " + "    INNER JOIN Fact_Acct f2 ON (f2.Fact_Acct_ID = r.Fact_Acct_ID) " + "       WHERE r.MatchCode=Fact_Reconciliation.MatchCode" + "       AND f2.Account_ID = ?) <> 0 " + " AND MatchCode IS NOT NULL";
        pstmt = DB.prepareStatement(sql, get_TrxName());
        pstmt.setInt(1, account.get_ID());
        pstmt.setInt(2, account.get_ID());
        unmatched = pstmt.executeUpdate();
        if (log.isLoggable(Level.FINE))
            log.log(Level.FINE, "Cleared match codes from " + unmatched + " unreconciled facts.");
    } catch (SQLException e) {
        log.log(Level.SEVERE, sql, e);
        return e.getLocalizedMessage();
    } finally {
        DB.close(pstmt);
        pstmt = null;
    }
    return "Matched " + (count - unmatched) + " facts";
}

Example 9 with MSequence

use of org.compiere.model.MSequence in project idempiere by idempiere.

the class DatabaseTableRename method doIt.

@Override
protected String doIt() throws Exception {
    MTable table = new MTable(getCtx(), p_AD_Table_ID, get_TrxName());
    String oldTableName = table.getTableName();
    log.info(table.toString());
    if (Util.isEmpty(p_NewTableName, true) || p_NewTableName.toLowerCase().equals(oldTableName.toLowerCase())) {
        throw new AdempiereException(Util.cleanAmp(Msg.parseTranslation(getCtx(), "@NotValid@: @NewTableName@")));
    }
    int cnt = DB.getSQLValueEx(get_TrxName(), "SELECT COUNT(*) FROM AD_Table WHERE LOWER(TableName)=?", p_NewTableName.toLowerCase());
    if (cnt > 0) {
        throw new AdempiereException(Util.cleanAmp(Msg.parseTranslation(getCtx(), "@AlreadyExists@: @TableName@ = " + p_NewTableName)));
    }
    String regex = "(?i)\\b" + oldTableName + "\\.";
    String fullregex = ".*" + regex + ".*";
    // Rename table in WhereClause and OrderByClause in AD_Tab
    List<MTab> tabs = new Query(getCtx(), MTab.Table_Name, "AD_Table_ID=? AND (WhereClause IS NOT NULL OR OrderByClause IS NOT NULL)", get_TrxName()).setParameters(p_AD_Table_ID).list();
    for (MTab tab : tabs) {
        boolean changed = false;
        String whereClause = tab.getWhereClause();
        if (whereClause != null && whereClause.matches(fullregex)) {
            whereClause = whereClause.replaceAll(regex, p_NewTableName + ".");
            tab.setWhereClause(whereClause);
            changed = true;
        }
        String orderByClause = tab.getOrderByClause();
        if (orderByClause != null && orderByClause.matches(fullregex)) {
            orderByClause = orderByClause.replaceAll(regex, p_NewTableName + ".");
            tab.setOrderByClause(orderByClause);
            changed = true;
        }
        if (changed) {
            tab.saveEx();
            addLog(0, null, null, "@Updated@ @AD_Tab_ID@ " + tab.getName(), MTab.Table_ID, tab.getAD_Tab_ID());
        }
    }
    // Rename table in WhereClause and OrderByClause in AD_Ref_Table
    List<MRefTable> refts = new Query(getCtx(), MRefTable.Table_Name, "AD_Table_ID=? AND (WhereClause IS NOT NULL OR OrderByClause IS NOT NULL)", get_TrxName()).setParameters(p_AD_Table_ID).list();
    for (MRefTable reft : refts) {
        boolean changed = false;
        String whereClause = reft.getWhereClause();
        if (whereClause != null && whereClause.matches(fullregex)) {
            whereClause = whereClause.replaceAll(regex, p_NewTableName + ".");
            reft.setWhereClause(whereClause);
            changed = true;
        }
        String orderByClause = reft.getOrderByClause();
        if (orderByClause != null && orderByClause.matches(fullregex)) {
            orderByClause = orderByClause.replaceAll(regex, p_NewTableName + ".");
            reft.setOrderByClause(orderByClause);
            changed = true;
        }
        if (changed) {
            reft.saveEx();
            addLog(0, null, null, "@Updated@ @AD_Reference_ID@ " + reft.getAD_Reference().getName(), MRefTable.Table_ID, reft.getAD_Reference_ID());
        }
    }
    // Rename table in sequences
    String whereSeq = "(Name=? AND Description=? AND IsTableID='Y') OR (Name=? AND Description=? AND IsTableID='N')";
    List<MSequence> seqs = new Query(getCtx(), MSequence.Table_Name, whereSeq, get_TrxName()).setParameters(oldTableName, "Table " + oldTableName, "DocumentNo_" + oldTableName, "DocumentNo/Value for Table " + oldTableName).list();
    for (MSequence seq : seqs) {
        if (seq.isTableID()) {
            seq.setName(p_NewTableName);
            seq.setDescription("Table " + p_NewTableName);
        } else {
            seq.setName("DocumentNo_" + p_NewTableName);
            seq.setDescription("DocumentNo/Value for Table " + p_NewTableName);
        }
        seq.saveEx();
        if (seq.getAD_Client_ID() == 0) {
            addLog(0, null, null, "@Updated@ @AD_Sequence_ID@ " + seq.getName(), MSequence.Table_ID, seq.getAD_Sequence_ID());
        } else {
            addLog(0, null, null, "@Updated@ @AD_Sequence_ID@ " + seq.getName() + ", @AD_Client_ID@ " + MClient.get(seq.getAD_Client_ID()).getName());
        }
    }
    String colPrefix = oldTableName.toLowerCase();
    List<M_Element> elements = new Query(getCtx(), M_Element.Table_Name, "LOWER(ColumnName) IN (?, ?)", get_TrxName()).setParameters(colPrefix + "_id", colPrefix + "_uu").setOrderBy("AD_Element_ID").list();
    for (M_Element element : elements) {
        String newColumnName;
        if (element.getColumnName().toLowerCase().endsWith("_id")) {
            newColumnName = p_NewTableName + "_ID";
        } else {
            newColumnName = p_NewTableName + "_UU";
        }
        element.renameDBColumn(newColumnName, getProcessInfo());
        element.saveEx();
    }
    String sql = "ALTER TABLE " + oldTableName + " RENAME TO " + p_NewTableName;
    int rvalue = DB.executeUpdateEx(sql, get_TrxName());
    addLog(rvalue + " - " + sql);
    table.setTableName(p_NewTableName);
    table.saveEx();
    return "@OK@";
}

Example 10 with MSequence

use of org.compiere.model.MSequence in project idempiere by idempiere.

the class MigraID method doIt.

@Override
protected String doIt() throws Exception {
    if (p_ID_From <= 0 && Util.isEmpty(p_UUID_From)) {
        throw new AdempiereUserError("Fill Record ID or UUID to convert");
    }
    if (p_ID_From > 0 && !Util.isEmpty(p_UUID_From)) {
        throw new AdempiereUserError("Record ID and UUID are excluyent, just one can be converted at the same time");
    }
    if (p_ID_From > 0 && p_ID_From == p_ID_To) {
        throw new AdempiereUserError("Same ID");
    }
    if (!Util.isEmpty(p_UUID_From) && p_UUID_From.equals(p_UUID_To)) {
        throw new AdempiereUserError("Same UUID");
    }
    MTable table = MTable.get(getCtx(), p_AD_Table_ID, get_TrxName());
    String tableName = table.getTableName();
    String msg = "";
    if (!Util.isEmpty(p_UUID_From)) {
        String uuidCol = MTable.getUUIDColumnName(tableName);
        if (Util.isEmpty(p_UUID_To)) {
            p_UUID_To = UUID.randomUUID().toString();
        }
        // convert UUID
        StringBuilder updUUIDSB = new StringBuilder().append("UPDATE ").append(tableName).append(" SET ").append(uuidCol).append("=?").append(" WHERE ").append(uuidCol).append("=?");
        int cnt = DB.executeUpdateEx(updUUIDSB.toString(), new Object[] { p_UUID_To, p_UUID_From }, get_TrxName());
        if (cnt <= 0) {
            msg = "@Error@: UUID " + p_UUID_From + " not found on table " + tableName;
        } else {
            msg = "UUID changed on table " + tableName + " from " + p_UUID_From + " to " + p_UUID_To;
            StringBuilder sqlSB = new StringBuilder().append("SELECT  ").append(tableName).append("_ID").append(" FROM ").append(tableName).append(" WHERE ").append(uuidCol).append("=?");
            int id = DB.getSQLValueEx(get_TrxName(), sqlSB.toString(), p_UUID_To);
            addBufferLog(id, null, null, msg, p_AD_Table_ID, id);
            msg = "@OK@";
        }
    } else {
        boolean seqCheck = false;
        String idCol = tableName + "_ID";
        if (p_ID_To <= 0) {
            p_ID_To = DB.getNextID(getAD_Client_ID(), tableName, get_TrxName());
        } else {
            StringBuilder sqlMaxSB = new StringBuilder().append("SELECT  MAX(").append(tableName).append("_ID)").append(" FROM ").append(tableName);
            int maxID = DB.getSQLValueEx(get_TrxName(), sqlMaxSB.toString());
            if (p_ID_To > maxID) {
                seqCheck = true;
            }
        }
        // convert ID
        int cnt = updID(tableName, idCol);
        if (cnt <= 0) {
            msg = "@Error@: ID " + p_ID_From + " not found on table " + tableName;
        } else {
            msg = "ID changed on table " + tableName + " from " + p_ID_From + " to " + p_ID_To;
            addBufferLog(p_ID_From, null, null, msg, p_AD_Table_ID, p_ID_To);
            msg = "@OK@";
        }
        migrateReference(tableName);
        migrateChildren(tableName);
        migrateRecordID();
        migrateAD_Preference(idCol);
        migrateTrees(tableName);
        if ("C_DocType_ID".equals(idCol)) {
            // special preference C_DocTypeTarget_ID
            migrateAD_Preference("C_DocTypeTarget_ID");
        }
        if (seqCheck) {
            MSequence seq = MSequence.get(getCtx(), tableName, get_TrxName());
            if (seq != null) {
                // ignore output messages
                seq.validateTableIDValue(get_TrxName());
            }
        }
    }
    return msg;
}