Блог

Я унаследовал BIRT .rptdesign, который не возвращает никаких записей в «результатах предварительного просмотра». Задействован один открытый скрипт, а также скрипт выборки, внутри которого содержится еще примерно 5 SQL-запросов. Многое из того, что я пробовал для решений, включает в себя выделение этих запросов, участвующих в скрипте выборки, в их собственный набор данных, а затем связывание их с открытым скриптом (в качестве альтернативы я пытался сформировать совместный набор данных, но безуспешно).). Даже после выполнения небольшого запроса из скрипта выборки я не могу генерировать записи в предварительном просмотре результатов. Я пытался загрузить изображение макета, но у меня нет репутации для этого … Пожалуйста, дайте мне знать, если вам нужен файл дизайна, чтобы помочь визуализировать, где все приземляется!

Открыть скрипт:

 maximoDataSet = MXReportDataSetProvider.create(this.getDataSource().getName(), this.getName());
maximoDataSet.open();

var sqlText = new String();
var printed = params["Printed"].toUpperCase().replace("Y","1");
var printed1 = printed.replace("N","0");
var where = new String(params["where"]);

if (params["Printed"].value)
    where  = " and workorder.em_printed = '"   printed1   "' "; 

// Add query to sqlText variable.
sqlText = "select workorder.wonum, workorder.workorderid as workorderid1, (select min(w2.workorderid) from maximo.workorder w2 where w2.istask=1 and w2.parent=workorder.wonum and w2.siteid=workorder.siteid) as workorderid, "
  " workorder.pmnum as pmnum, workorder.targstartdate, workorder.siteid, workorder.description as wdesc, workorder.worktype, a.assetnum, a.description as adesc, a.em_relaymajor, a.em_relayminor, "
  " workorder.location, l.description as ldesc, a.em_circuit, a.em_manfmodl, a.serialnum, j.em_craft, a.assetuid, "
  " (select al.description from alndomain al where al.value=j.em_craft and al.domainid='EM_CRAFT') as cdesc, "
  " (select wt.wtypedesc from maximo.worktype wt where wt.worktype=workorder.worktype) as wtdesc "
  " from maximo.workorder "
  " left outer join maximo.asset as a on a.assetnum=workorder.assetnum and a.siteid=workorder.siteid "
  " left outer join maximo.jobplan as j on j.jpnum=workorder.jpnum and j.pluscrevnum=workorder.pluscjprevnum "
  " left outer join maximo.locations as l on l.location=workorder.location and l.siteid=workorder.siteid"
// Include the Maximo where clause
  " where workorder.istask=0 and workorder.status='INPRG' and workorder.siteid='EM' and "   where
;

maximoDataSet.setQuery(sqlText);
  

Скрипт выборки:

 if (!maximoDataSet.fetch())
    return (false);

// Add a line for each output column
// The specific get method should match the data type of the output column.
row["wonum"] = maximoDataSet.getString("wonum");
row["workorderid"] = maximoDataSet.getString("workorderid");
row["workorderid1"] = maximoDataSet.getString("workorderid1");
row["wdesc"] = maximoDataSet.getString("wdesc");
row["worktype"] = maximoDataSet.getString("worktype");
row["assetnum"] = maximoDataSet.getString("assetnum");
row["location"] = maximoDataSet.getString("location");
row["adesc"] = maximoDataSet.getString("adesc");    
row["ldesc"] = maximoDataSet.getString("ldesc");
row["em_circuit"] = maximoDataSet.getString("em_circuit");
row["em_manfmodl"] = maximoDataSet.getString("em_manfmodl");
row["serialnum"] = maximoDataSet.getString("serialnum");
row["em_craft"] = maximoDataSet.getString("em_craft");  
row["cdesc"] = maximoDataSet.getString("cdesc");
row["wtdesc"] = maximoDataSet.getString("wtdesc");
row["pmnum"] = maximoDataSet.getString("pmnum");
row["siteid"] = maximoDataSet.getString("siteid");
row["assetuid"] = maximoDataSet.getString("assetuid");
row["em_relaymajor"] = maximoDataSet.getString("em_relaymajor");
row["em_relayminor"] = maximoDataSet.getString("em_relayminor");
row["targstartdate"] = maximoDataSet.getTimestamp("targstartdate");

// Long Description
    longDescDataSet = MXReportDataSetProvider.create(this.getDataSource().getName(), "longDescDataSet");
    longDescDataSet.open();



    longDescSQL = "select longdescription.ldtext  "
      "from longdescription "
      "where longdescription.ldownertable='WORKORDER' and longdescription.ldownercol='DESCRIPTION' "
      "and longdescription.ldownercol='DESCRIPTION' "
      "and longdescription.ldkey=?";

    longDescDataSet.setQuery(longDescSQL);

    longDescDataSet.setQueryParameterValue(1, maximoDataSet.getString("workorderid"));

    if(longDescDataSet.fetch()) {
        row["longdesc"] = longDescDataSet.getString("ldtext");
    }
    longDescDataSet.close();
// End Long Description

// Last Major
    lastmajorDataSet = MXReportDataSetProvider.create(this.getDataSource().getName(), "lastmajorDataSet");
    lastmajorDataSet.open();

    lastmajorSQL = "select workorder.wonum as wn1, workorder.workorderid as wid1, workorder.description as d1, p.frequency as f1, p.frequnit as fu1, "
      "p.lastcompdate as lc1, p.extdate as e1, p.nextdate as n1, workorder.targstartdate as tsd1 "
      "from workorder "
      "join pm as p on p.pmnum=workorder.pmnum and p.siteid=workorder.siteid "
      "where workorder.istask = 0 and workorder.actfinish = (select max(w1.actfinish) from maximo.workorder as w1 where w1.wonum=workorder.wonum and w1.siteid=workorder.siteid) "
      "and workorder.worktype = 'MAJPM' and workorder.assetnum=? and workorder.siteid=? ";


    lastmajorDataSet.setQuery(lastmajorSQL);

    lastmajorDataSet.setQueryParameterValue(1, maximoDataSet.getString("assetnum"));
    lastmajorDataSet.setQueryParameterValue(2, maximoDataSet.getString("siteid"));

    if(lastmajorDataSet.fetch()) {
        row["wn1"] = lastmajorDataSet.getString("wn1");
        row["wid1"] = lastmajorDataSet.getString("wid1");
        row["d1"] = lastmajorDataSet.getString("d1");
        row["f1"] = lastmajorDataSet.getString("f1");
        row["lc1"] = lastmajorDataSet.getTimestamp("lc1");
        row["fu1"] = lastmajorDataSet.getString("fu1");
        row["e1"] = lastmajorDataSet.getTimestamp("e1");
        row["n1"] = lastmajorDataSet.getTimestamp("n1");
        row["tsd1"] = lastmajorDataSet.getTimestamp("tsd1");
    }
    lastmajorDataSet.close();
// End Last Major

// Last Major Initials
    lastmajorIDataSet = MXReportDataSetProvider.create(this.getDataSource().getName(), "lastmajorIDataSet");
    lastmajorIDataSet.open();

    lastmajorISQL =" WITH x(key, val, rnum) AS "
      " (SELECT refwo, em_initials, row_number() over(partition by refwo) "
      " from maximo.labtrans as labtrans "
      " join maximo.person as person on person.personid=labtrans.laborcode where labtrans.refwo='"   row["wn1"]   "' and labtrans.siteid=?), "
      " y(key, str, cnt, cnt_max) AS "
      " ( SELECT key, VARCHAR('', 1000), 0, MAX(rnum) "
      " FROM x "
      " GROUP BY key "
      " UNION ALL "
      " SELECT y.key, y.str || RTRIM(CHAR(x.val)) || ',', y.cnt   1, y.cnt_max "
      " FROM x, y "
      " WHERE x.key = y.key AND "
      " x.rnum = y.cnt   1 AND "
      " y.cnt < y.cnt_max ) "
      " SELECT key, str "
      " FROM y "
      " WHERE y.cnt = y.cnt_max ";

    lastmajorIDataSet.setQuery(lastmajorISQL);

    //lastmajorIDataSet.setQueryParameterValue(1, lastmajorDataSet.getString("wn1"));
    lastmajorIDataSet.setQueryParameterValue(1, maximoDataSet.getString("siteid"));

    if(lastmajorIDataSet.fetch()) {
        row["str"] = lastmajorIDataSet.getString("str");
    }
    lastmajorIDataSet.close();
// End Last Major Initials

// Last Major Long Description
    LMlongDescDataSet = MXReportDataSetProvider.create(this.getDataSource().getName(), "LMlongDescDataSet");
    LMlongDescDataSet.open();

    LMlongDescSQL = "select longdescription.ldtext as ldtext1  "
      "from longdescription "
      "where longdescription.ldownertable='WORKORDER' and longdescription.ldownercol='DESCRIPTION' "
      "and longdescription.ldkey="   row["wid1"];


    LMlongDescDataSet.setQuery(LMlongDescSQL);

    //LMlongDescDataSet.setQueryParameterValue(1, lastmajorDataSet.getString("wid1"));

    if(LMlongDescDataSet.fetch()) {
        row["longdesc1"] = LMlongDescDataSet.getString("ldtext1");
    }
    LMlongDescDataSet.close();
// End Last Major Long Description

// Last Minor
    lastminorDataSet = MXReportDataSetProvider.create(this.getDataSource().getName(), "lastminorDataSet");
    lastminorDataSet.open();

    lastminorSQL = "select workorder.wonum as wn2, workorder.workorderid as wid2, workorder.description as d2, p.frequency as f2, p.frequnit as fu2, "
      "p.lastcompdate as lc2, p.extdate as e2, p.nextdate as n2, workorder.targstartdate as tsd2 "
      "from workorder "
      "join pm as p on p.pmnum=workorder.pmnum and p.siteid=workorder.siteid "
      "where workorder.istask = 0 and workorder.actfinish = (select max(w1.actfinish) from maximo.workorder as w1 where w1.wonum=workorder.wonum and w1.siteid=workorder.siteid) "
      "and workorder.worktype = 'MINPM' and workorder.assetnum=? and workorder.siteid= ? ";

    lastminorDataSet.setQuery(lastminorSQL);

    lastminorDataSet.setQueryParameterValue(1, maximoDataSet.getString("assetnum"));
    lastminorDataSet.setQueryParameterValue(2, maximoDataSet.getString("siteid"));

    if(lastminorDataSet.fetch()) {
        row["wn2"] = lastminorDataSet.getString("wn2");
        row["wid2"] = lastminorDataSet.getString("wid2");
        row["d2"] = lastminorDataSet.getString("d2");
        row["f2"] = lastminorDataSet.getString("f2");
        row["lc2"] = lastminorDataSet.getTimestamp("lc2");
        row["fu2"] = lastminorDataSet.getString("fu2");
        row["e2"] = lastminorDataSet.getTimestamp("e2");
        row["n2"] = lastminorDataSet.getTimestamp("n2");
        row["tsd2"] = lastminorDataSet.getTimestamp("tsd2");
    }
    lastminorDataSet.close();
// End Last Minor

// Last Minor Initials
    lastminorIDataSet = MXReportDataSetProvider.create(this.getDataSource().getName(), "lastminorIDataSet");
    lastminorIDataSet.open();

    lastminorISQL =" WITH x(key, val, rnum) AS "
      " (SELECT refwo, em_initials, row_number() over(partition by refwo) "
      " from maximo.labtrans as labtrans "
      " join maximo.person as person on person.personid=labtrans.laborcode where labtrans.refwo='"   row["wn2"]   "' and labtrans.siteid=?), "
      " y(key, str1, cnt, cnt_max) AS "
      " ( SELECT key, VARCHAR('', 1000), 0, MAX(rnum) "
      " FROM x "
      " GROUP BY key "
      " UNION ALL "
      " SELECT y.key, y.str1 || RTRIM(CHAR(x.val)) || ',', y.cnt   1, y.cnt_max "
      " FROM x, y "
      " WHERE x.key = y.key AND "
      " x.rnum = y.cnt   1 AND "
      " y.cnt < y.cnt_max ) "
      " SELECT key, str1 "
      " FROM y "
      " WHERE y.cnt = y.cnt_max ";

    lastminorIDataSet.setQuery(lastminorISQL);

    //lastminorIDataSet.setQueryParameterValue(1, lastminorDataSet.getString("wn2"));
    lastminorIDataSet.setQueryParameterValue(1, maximoDataSet.getString("siteid"));

    if(lastminorIDataSet.fetch()) {
        row["str1"] = lastminorIDataSet.getString("str1");
    }
    lastminorIDataSet.close();
// End Last Minor Initials

// Last Minor Long Description
    LM2longDescDataSet = MXReportDataSetProvider.create(this.getDataSource().getName(), "LM2longDescDataSet");
    LM2longDescDataSet.open();

    LM2longDescSQL = "select longdescription.ldtext as ldtext2  "
      "from longdescription "
      "where longdescription.ldownertable='WORKORDER' and longdescription.ldownercol='DESCRIPTION' "
      "and longdescription.ldkey="   row["wid2"];

    LM2longDescDataSet.setQuery(LM2longDescSQL);

    //LM2longDescDataSet.setQueryParameterValue(1, lastminorDataSet.getString("wid2"));

    if(LM2longDescDataSet.fetch()) {
        row["longdesc2"] = LM2longDescDataSet.getString("ldtext2");
    }
    LM2longDescDataSet.close();
// End Last Minor Long Description

// Asset Long Description
    AlongDescDataSet = MXReportDataSetProvider.create(this.getDataSource().getName(), "AlongDescDataSet");
    AlongDescDataSet.open();

    AlongDescSQL = "select longdescription.ldtext as ldtext3  "
      "from longdescription "
      "where longdescription.ldownertable='ASSET' and longdescription.ldownercol='DESCRIPTION' "
      "and longdescription.ldkey=?";

    AlongDescDataSet.setQuery(AlongDescSQL);

    AlongDescDataSet.setQueryParameterValue(1, maximoDataSet.getString("assetuid"));

    if(AlongDescDataSet.fetch()) {
        row["longdesc3"] = AlongDescDataSet.getString("ldtext3");
    }
    AlongDescDataSet.close();
// End Asset Long Description

myTxn = MXReportTxnProvider.create(this.getDataSource().getName());
myStmt = myTxn.createStatement();
myStmt.setQuery("update workorder set workorder.em_printed = '1' where workorder.em_printed = '0' and workorder.workorderid = ? ");
myStmt.setQueryParameterValue(1,row["workorderid1"]); 
//myStmt.setQueryParameterValue(2,"'" row["siteid"] "'");
myTxn.save();

return (true);
  

введите описание изображения здесь