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

Example 26 with MQuery

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

the class DrillCommand method service.

public boolean service(AuRequest request, boolean everError) {
    if (!DrillEvent.ON_DRILL_ACROSS.equals(request.getCommand()) && !DrillEvent.ON_DRILL_DOWN.equals(request.getCommand()))
        return false;
    final Map<?, ?> map = request.getData();
    JSONArray data = (JSONArray) map.get("data");
    final Component comp = request.getComponent();
    if (comp == null)
        throw new UiException(MZk.ILLEGAL_REQUEST_COMPONENT_REQUIRED, this);
    if (data == null || data.size() < 2)
        throw new UiException(MZk.ILLEGAL_REQUEST_WRONG_DATA, new Object[] { Objects.toString(data), this });
    String columnName = (String) data.get(0);
    String tableName = MQuery.getZoomTableName(columnName);
    String code = (String) data.get(1);
    //
    MQuery query = new MQuery(tableName);
    query.addRestriction(columnName, MQuery.EQUAL, code);
    Events.postEvent(new DrillEvent(request.getCommand(), comp, query));
    return true;
}

Example 27 with MQuery

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

the class InvoicePrint method doIt.

//	prepare
/**
	 *  Perform process.
	 *  @return Message
	 *  @throws Exception
	 */
protected String doIt() throws java.lang.Exception {
    //	Need to have Template
    if (p_EMailPDF && p_R_MailText_ID == 0)
        throw new AdempiereUserError("@NotFound@: @R_MailText_ID@");
    log.info("C_BPartner_ID=" + m_C_BPartner_ID + ", C_Invoice_ID=" + m_C_Invoice_ID + ", EmailPDF=" + p_EMailPDF + ",R_MailText_ID=" + p_R_MailText_ID + ", DateInvoiced=" + m_dateInvoiced_From + "-" + m_dateInvoiced_To + ", DocumentNo=" + m_DocumentNo_From + "-" + m_DocumentNo_To);
    MMailText mText = null;
    if (p_R_MailText_ID != 0) {
        mText = new MMailText(getCtx(), p_R_MailText_ID, get_TrxName());
        if (mText.get_ID() != p_R_MailText_ID)
            throw new AdempiereUserError("@NotFound@: @R_MailText_ID@ - " + p_R_MailText_ID);
    }
    //	Too broad selection
    if (m_C_BPartner_ID == 0 && m_C_Invoice_ID == 0 && m_dateInvoiced_From == null && m_dateInvoiced_To == null && m_DocumentNo_From == null && m_DocumentNo_To == null)
        throw new AdempiereUserError("@RestrictSelection@");
    MClient client = MClient.get(getCtx());
    //	Get Info
    StringBuffer sql = new StringBuffer(//	1..3
    "SELECT i.C_Invoice_ID,bp.AD_Language,c.IsMultiLingualDocument," + //	Prio: 1. BPartner 2. DocType, 3. PrintFormat (Org)	//	see ReportCtl+MInvoice
    " COALESCE(bp.Invoice_PrintFormat_ID, dt.AD_PrintFormat_ID, pf.Invoice_PrintFormat_ID)," + //	4 
    " dt.DocumentCopies+bp.DocumentCopies," + //	5
    " bpc.AD_User_ID, i.DocumentNo," + //	6..7
    " bp.C_BPartner_ID " + //	8
    "FROM C_Invoice i" + " INNER JOIN C_BPartner bp ON (i.C_BPartner_ID=bp.C_BPartner_ID)" + " LEFT OUTER JOIN AD_User bpc ON (i.AD_User_ID=bpc.AD_User_ID)" + " INNER JOIN AD_Client c ON (i.AD_Client_ID=c.AD_Client_ID)" + " INNER JOIN AD_PrintForm pf ON (i.AD_Client_ID=pf.AD_Client_ID)" + " INNER JOIN C_DocType dt ON (i.C_DocType_ID=dt.C_DocType_ID)" + " WHERE i.AD_Client_ID=? AND i.AD_Org_ID=? AND i.isSOTrx='Y' AND " + //	more them 1 PF
    "       pf.AD_Org_ID IN (0,i.AD_Org_ID) AND ");
    boolean needAnd = false;
    if (m_C_Invoice_ID != 0)
        sql.append("i.C_Invoice_ID=").append(m_C_Invoice_ID);
    else {
        if (m_C_BPartner_ID != 0) {
            sql.append("i.C_BPartner_ID=").append(m_C_BPartner_ID);
            needAnd = true;
        }
        if (m_dateInvoiced_From != null && m_dateInvoiced_To != null) {
            if (needAnd)
                sql.append(" AND ");
            sql.append("TRUNC(i.DateInvoiced, 'DD') BETWEEN ").append(DB.TO_DATE(m_dateInvoiced_From, true)).append(" AND ").append(DB.TO_DATE(m_dateInvoiced_To, true));
            needAnd = true;
        } else if (m_dateInvoiced_From != null) {
            if (needAnd)
                sql.append(" AND ");
            sql.append("TRUNC(i.DateInvoiced, 'DD') >= ").append(DB.TO_DATE(m_dateInvoiced_From, true));
            needAnd = true;
        } else if (m_dateInvoiced_To != null) {
            if (needAnd)
                sql.append(" AND ");
            sql.append("TRUNC(i.DateInvoiced, 'DD') <= ").append(DB.TO_DATE(m_dateInvoiced_To, true));
            needAnd = true;
        } else if (m_DocumentNo_From != null && m_DocumentNo_To != null) {
            if (needAnd)
                sql.append(" AND ");
            sql.append("i.DocumentNo BETWEEN ").append(DB.TO_STRING(m_DocumentNo_From)).append(" AND ").append(DB.TO_STRING(m_DocumentNo_To));
        } else if (m_DocumentNo_From != null) {
            if (needAnd)
                sql.append(" AND ");
            if (m_DocumentNo_From.indexOf('%') == -1)
                sql.append("i.DocumentNo >= ").append(DB.TO_STRING(m_DocumentNo_From));
            else
                sql.append("i.DocumentNo LIKE ").append(DB.TO_STRING(m_DocumentNo_From));
        }
        if (p_EMailPDF) {
            if (needAnd) {
                sql.append(" AND ");
            }
            /* if emailed to customer only select COmpleted & CLosed invoices */
            sql.append("i.DocStatus IN ('CO','CL') ");
        }
    }
    //	more than 1 PF record
    sql.append(" ORDER BY i.C_Invoice_ID, pf.AD_Org_ID DESC");
    log.fine(sql.toString());
    MPrintFormat format = null;
    int old_AD_PrintFormat_ID = -1;
    int old_C_Invoice_ID = -1;
    int C_BPartner_ID = 0;
    int count = 0;
    int errors = 0;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
        pstmt = DB.prepareStatement(sql.toString(), get_TrxName());
        pstmt.setInt(1, Env.getAD_Client_ID(Env.getCtx()));
        pstmt.setInt(2, Env.getAD_Org_ID(Env.getCtx()));
        rs = pstmt.executeQuery();
        while (rs.next()) {
            int C_Invoice_ID = rs.getInt(1);
            if (//	multiple pf records
            C_Invoice_ID == old_C_Invoice_ID)
                continue;
            old_C_Invoice_ID = C_Invoice_ID;
            //	Set Language when enabled
            //	Base Language
            Language language = Language.getLoginLanguage();
            String AD_Language = rs.getString(2);
            if (AD_Language != null && "Y".equals(rs.getString(3)))
                language = Language.getLanguage(AD_Language);
            //
            int AD_PrintFormat_ID = rs.getInt(4);
            int copies = rs.getInt(5);
            if (copies == 0)
                copies = 1;
            int AD_User_ID = rs.getInt(6);
            MUser to = new MUser(getCtx(), AD_User_ID, get_TrxName());
            String DocumentNo = rs.getString(7);
            C_BPartner_ID = rs.getInt(8);
            //
            String documentDir = client.getDocumentDir();
            if (documentDir == null || documentDir.length() == 0)
                documentDir = ".";
            //
            if (p_EMailPDF && (to.get_ID() == 0 || to.getEMail() == null || to.getEMail().length() == 0)) {
                addLog(C_Invoice_ID, null, null, DocumentNo + " @RequestActionEMailNoTo@");
                errors++;
                continue;
            }
            if (AD_PrintFormat_ID == 0) {
                addLog(C_Invoice_ID, null, null, DocumentNo + " No Print Format");
                errors++;
                continue;
            }
            //	Get Format & Data
            if (AD_PrintFormat_ID != old_AD_PrintFormat_ID) {
                format = MPrintFormat.get(getCtx(), AD_PrintFormat_ID, false);
                old_AD_PrintFormat_ID = AD_PrintFormat_ID;
            }
            format.setLanguage(language);
            format.setTranslationLanguage(language);
            //	query
            MQuery query = new MQuery("C_Invoice_Header_v");
            query.addRestriction("C_Invoice_ID", MQuery.EQUAL, new Integer(C_Invoice_ID));
            //	Engine
            PrintInfo info = new PrintInfo(DocumentNo, X_C_Invoice.Table_ID, C_Invoice_ID, C_BPartner_ID);
            info.setCopies(copies);
            ReportEngine re = new ReportEngine(getCtx(), format, query, info);
            boolean printed = false;
            if (p_EMailPDF) {
                String subject = mText.getMailHeader() + " - " + DocumentNo;
                EMail email = client.createEMail(to.getEMail(), subject, null);
                if (!email.isValid()) {
                    addLog(C_Invoice_ID, null, null, DocumentNo + " @RequestActionEMailError@ Invalid EMail: " + to);
                    errors++;
                    continue;
                }
                //	Context
                mText.setUser(to);
                //	Context
                mText.setBPartner(C_BPartner_ID);
                mText.setPO(new MInvoice(getCtx(), C_Invoice_ID, get_TrxName()));
                String message = mText.getMailText(true);
                if (mText.isHtml())
                    email.setMessageHTML(subject, message);
                else {
                    email.setSubject(subject);
                    email.setMessageText(message);
                }
                //
                File invoice = null;
                if (!Ini.isClient())
                    invoice = new File(MInvoice.getPDFFileName(documentDir, C_Invoice_ID));
                File attachment = re.getPDF(invoice);
                log.fine(to + " - " + attachment);
                email.addAttachment(attachment);
                //
                String msg = email.send();
                MUserMail um = new MUserMail(mText, getAD_User_ID(), email);
                um.saveEx();
                if (msg.equals(EMail.SENT_OK)) {
                    addLog(C_Invoice_ID, null, null, DocumentNo + " @RequestActionEMailOK@ - " + to.getEMail());
                    count++;
                    printed = true;
                } else {
                    addLog(C_Invoice_ID, null, null, DocumentNo + " @RequestActionEMailError@ " + msg + " - " + to.getEMail());
                    errors++;
                }
            } else {
                ServerReportCtl.startDocumentPrint(ReportEngine.INVOICE, // No custom print format
                null, C_Invoice_ID, // No custom printer
                null, null);
                count++;
                printed = true;
            }
            //	Print Confirm
            if (printed) {
                StringBuffer sb = new StringBuffer("UPDATE C_Invoice " + "SET DatePrinted=SysDate, IsPrinted='Y' WHERE C_Invoice_ID=").append(C_Invoice_ID);
                int no = DB.executeUpdate(sb.toString(), get_TrxName());
            }
        }
    //	for all entries						
    } catch (Exception e) {
        log.log(Level.SEVERE, "doIt - " + sql, e);
        throw new Exception(e);
    } finally {
        DB.close(rs, pstmt);
    }
    //
    if (p_EMailPDF)
        return "@Sent@=" + count + " - @Errors@=" + errors;
    return "@Printed@=" + count;
}

Example 28 with MQuery

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

the class HtmlDashboard method goalsDetail.

private String goalsDetail(int AD_Table_ID) {
    //TODO link to goals
    String output = "";
    if (m_goals == null)
        return output;
    for (int i = 0; i < m_goals.length; i++) {
        MMeasureCalc mc = MMeasureCalc.get(Env.getCtx(), m_goals[i].getMeasure().getPA_MeasureCalc_ID());
        if (AD_Table_ID == m_goals[i].getPA_Goal_ID()) {
            // mc.getAD_Table_ID()) {
            output += "<table class=\"dataGrid\"><tr>\n<th colspan=\"3\" class=\"label\"><b>" + m_goals[i].getName() + "</b></th></tr>\n";
            output += "<tr><td class=\"label\">Target</td><td colspan=\"2\" class=\"tdcontent\">" + m_goals[i].getMeasureTarget() + "</td></tr>\n";
            output += "<tr><td class=\"label\">Actual</td><td colspan=\"2\" class=\"tdcontent\">" + m_goals[i].getMeasureActual() + "</td></tr>\n";
            //if (mc.getTableName()!=null) output += "table: " + mc.getAD_Table_ID() + "<br>\n";
            Graph barPanel = new Graph(m_goals[i]);
            GraphColumn[] bList = barPanel.getGraphColumnList();
            MQuery query = null;
            output += "<tr><td rowspan=\"" + bList.length + "\" class=\"label\" valign=\"top\">" + m_goals[i].getXAxisText() + "</td>\n";
            for (int k = 0; k < bList.length; k++) {
                GraphColumn bgc = bList[k];
                if (k > 0)
                    output += "<tr>";
                if (//	Single Achievement
                bgc.getAchievement() != null) {
                    MAchievement a = bgc.getAchievement();
                    query = MQuery.getEqualQuery("PA_Measure_ID", a.getPA_Measure_ID());
                } else if (//	Multiple Achievements 
                bgc.getGoal() != null) {
                    MGoal goal = bgc.getGoal();
                    query = MQuery.getEqualQuery("PA_Measure_ID", goal.getPA_Measure_ID());
                } else if (//	Document
                bgc.getMeasureCalc() != null) {
                    mc = bgc.getMeasureCalc();
                    query = mc.getQuery(m_goals[i].getRestrictions(false), bgc.getMeasureDisplay(), bgc.getDate(), //	logged in role
                    MRole.getDefault());
                } else if (//	Document
                bgc.getProjectType() != null) {
                    MProjectType pt = bgc.getProjectType();
                    query = pt.getQuery(m_goals[i].getRestrictions(false), bgc.getMeasureDisplay(), bgc.getDate(), bgc.getID(), //	logged in role
                    MRole.getDefault());
                } else if (//	Document
                bgc.getRequestType() != null) {
                    MRequestType rt = bgc.getRequestType();
                    query = rt.getQuery(m_goals[i].getRestrictions(false), bgc.getMeasureDisplay(), bgc.getDate(), bgc.getID(), //	logged in role
                    MRole.getDefault());
                }
                output += "<td class=\"tdcontent\">" + bgc.getLabel() + "</td><td  class=\"tdcontent\">";
                if (query != null) {
                    output += "<a class=\"hrefZoom\" href=\"http:///window/zoom#" + queryZoom.size() + "\">" + bgc.getValue() + "</a><br>\n";
                    queryZoom.add(query);
                } else {
                    log.info("Nothing to zoom to - " + bgc);
                    output += bgc.getValue();
                }
                output += "</td></tr>";
            }
            output += "</tr>" + "<tr><td colspan=\"3\">" + m_goals[i].getDescription() + "<br>" + stripHtml(m_goals[i].getColorSchema().getDescription(), true) + "</td></tr>" + "</table>\n";
            bList = null;
            barPanel = null;
        }
    }
    return output;
}

Example 29 with MQuery

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

the class SimulatedPickList method print.

/**
	 * Print result generate for this report
	 */
void print() throws Exception {
    // Base Language
    Language language = Language.getLoginLanguage();
    MPrintFormat pf = null;
    int pfid = 0;
    // get print format for client, else copy system to client
    RowSet pfrs = MPrintFormat.getAccessiblePrintFormats(MTable.getTable_ID(X_RV_PP_Product_BOMLine_Storage_TableName), -1, null);
    pfrs.next();
    pfid = pfrs.getInt("AD_PrintFormat_ID");
    if (pfrs.getInt("AD_Client_ID") != 0)
        pf = MPrintFormat.get(getCtx(), pfid, false);
    else
        pf = MPrintFormat.copyToClient(getCtx(), pfid, getAD_Client_ID());
    pfrs.close();
    if (pf == null)
        raiseError("Error: ", "No Print Format");
    pf.setLanguage(language);
    pf.setTranslationLanguage(language);
    // query
    MQuery query = new MQuery(X_RV_PP_Product_BOMLine_Storage_TableName);
    query.addRestriction(X_T_BOMLine.COLUMNNAME_AD_PInstance_ID, MQuery.EQUAL, AD_PInstance_ID, getParamenterName(X_T_BOMLine.COLUMNNAME_AD_PInstance_ID), getParamenterInfo(X_T_BOMLine.COLUMNNAME_AD_PInstance_ID));
    query.addRestriction(X_T_BOMLine.COLUMNNAME_M_Warehouse_ID, MQuery.EQUAL, p_M_Warehouse_ID, getParamenterName(X_T_BOMLine.COLUMNNAME_M_Warehouse_ID), getParamenterInfo(X_T_BOMLine.COLUMNNAME_M_Warehouse_ID));
    query.addRestriction(X_T_BOMLine.COLUMNNAME_M_Warehouse_ID, MQuery.EQUAL, p_M_Warehouse_ID, getParamenterName("DateTrx"), getParamenterInfo("DateTrx"));
    PrintInfo info = new PrintInfo(X_RV_PP_Product_BOMLine_Storage_TableName, MTable.getTable_ID(X_RV_PP_Product_BOMLine_Storage_TableName), getRecord_ID());
    ReportEngine re = new ReportEngine(getCtx(), pf, query, info);
    ReportCtl.preview(re);
    // records are deleted when process ends
    while (re.getView().isDisplayable()) {
        Env.sleep(1);
    }
}

Example 30 with MQuery

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

the class PrintBOM method print.

/**
	 * Print result generate for this report
	 */
void print() throws Exception {
    // Base Language
    Language language = Language.getLoginLanguage();
    MPrintFormat pf = null;
    int pfid = 0;
    // get print format for client, else copy system to client  
    RowSet pfrs = MPrintFormat.getAccessiblePrintFormats(X_RV_PP_Product_BOMLine_Table_ID, -1, null);
    pfrs.next();
    pfid = pfrs.getInt("AD_PrintFormat_ID");
    if (pfrs.getInt("AD_Client_ID") != 0)
        pf = MPrintFormat.get(getCtx(), pfid, false);
    else
        pf = MPrintFormat.copyToClient(getCtx(), pfid, getAD_Client_ID());
    pfrs.close();
    if (pf == null)
        raiseError("Error: ", "No Print Format");
    pf.setLanguage(language);
    pf.setTranslationLanguage(language);
    // query
    MQuery query = MQuery.get(getCtx(), AD_PInstance_ID, X_RV_PP_Product_BOMLine_Table_Name);
    query.addRestriction("AD_PInstance_ID", MQuery.EQUAL, AD_PInstance_ID);
    PrintInfo info = new PrintInfo(X_RV_PP_Product_BOMLine_Table_Name, X_RV_PP_Product_BOMLine_Table_ID, getRecord_ID());
    ReportEngine re = new ReportEngine(getCtx(), pf, query, info);
    ReportCtl.preview(re);
    // records are deleted when process ends 
    while (re.getView().isDisplayable()) {
        Env.sleep(1);
    }
}