Oracle APEX - 根据包含值在交互式报表中的单元格中有条件地更改文本颜色？ [英] Oracle APEX - Conditionally Changing Text Color in a cell on an Interactive Report based on Contained Value?

问题描述

好吧，那么我在APEX中有一个交互式报告，显示我们系统中的文档数据。我们的文件应该每两年进行一次审查，表格中有一列，其中包含文件下次审核的日期。我们希望在日期达到某些基准时（即当日期在当前日期的6个月内，3个月，2，1等）内时可视地指示包含日期的单元格或行中的另一个单元格。

我希望做的是根据该单元格的值或另一个值的值来更改特定单元格的文本（或背景）的颜色单元格在同一行。需要一个进程或一些功能来进行一些计算，以确定系统日期和单元格中包含的日期之间的跨度...它不一定需要计算何时加载页面，而是每天或每周的进程，一些事情会很好

是否有可能在没有人工交互的情况下设置每天执行的进程或触发器？我还需要使用相同的功能来发送有关这些即将到期的提醒电子邮件。 （即当文件需要审查6个月后，电子邮件将在3个月，2等1等发送。）

解决方案

使用hiredate on emp的示例：我即将对11000的单元格进行着色（sysdate-hiredate的范围从10k到12k）。您可以使用查询中计算的字段，或通过某些过程填写的字段，无关紧要：）

 选择empno，ename，job，mgr，hiredate，sal，comm，deptno，
 trunc（（trunc（sysdate）-hiredate））to_colour_or_not 
 from emp 
  

您将需要2个动态操作来对报表中的行进行颜色分析：一个加载操作和一次刷新。如果您跳过刷新后，由于部分页面刷新，行分页后不会进行着色。

动态操作一：刷新区域后： / p>

True action：

  $（td [headers ='TO_COLOUR_OR_NOT']）。 （）{
 alert（$（this）.text（））; 
 if（parseInt（$（this）.text（））> 11000）{
 $（this） css（{background-color：red}）; 
}; 
}）; 
  

使用一列作为条件来着色另一列的示例。始终保持与你测试什么和你测试它！例如，hiredate列是一个日期，如果需要，一定要对待它！另外要注意的是：如果您的日期格式设置为DD-MON-YYYY，那么您必须按月编号（JAN = 1，DEC = 12）进行映射！也许这是一个更改日期格式的选项，甚至...

  $（td [headers ='HIREDATE ']）。each（function（）{
 var i_date = $（this）.text（）; 
 // date format = MM / DD / YYYY 
 // be carefull with日期格式
 //在我的情况下，我知道我的日期格式，知道它不会更改
 //我的代码远非完整的解析可能的日期值！
 var dMonth = i_date.substring（0,2），
 dDay = i_date.substring（3，5），
 dYear = i_date.substring（6）; 
 var d = new Date（dYear， dMonth，dDay，0，0，0，0）; 
 
 if（d.getFullYear（）< = 1981）{
 //我们循环了TD元素， 
 // so，$（this）= TD元素我们循环，parent = TR元素，
 /然后回到行的孩子
 $（this）.parent（）。children（td [headers ='ENAME']）。css（background-color：red }）; 
}; 
}）; 
  

第二个动态动作：加载

作为真正的动作，使用与刷新的真正操作相同的代码。

使用JS可以做任何你想要的：你只需要知道你想要绘制哪些单元格。使用 [headers =''] 来定位您想要的单元格（ jquery选择器）。
而不是css（），你可以使用addClass，如果这更多的是你想要的。

请记住：IRs内置 - 鼠标悬停操作。这样当鼠标悬停操作时，您的绘制单元格不会显示为其颜色。如果你不想要这个，你需要为mouseover / mouseleave事件另一个动态的动作，并且定位这些单元格。

至于排定的作业：检查 DBMS_JOBS 。

Ok... So. I have an interactive report in APEX that displays data about documents in our system. Our documents are supposed to be reviewed every two years and there is a column in the table that contains the date that the document should be next reviewed by. We want to visually indicate either in the cell that contains the date, or another cell in the row, when the date reaches certain benchmarks (i.e. When the date is within 6 months of the current date, 3 months, 2, 1 etc.)

What I need to hopefully do is to change the color of the text (or background) of a specific cell based on either the value of that cell or the value of another cell in the same row. There would need to be a process or some function that does some computation to determine the span between the sysdate and the date contained in the cell... It does not necessarily need to calculate when the page is loaded but a daily or weekly process or something would be good.

Is it possible to set up processes or triggers that execute daily without human interaction? I also have a need to use the same functionality for sending reminder emails about these upcoming deadlines. (i.e. when a document is 6 months out from needing to be reviewed an email would be sent out, at 3 months, 2, 1 etc.)

解决方案

Example with hiredate on emp: i'm about to colour the cells that are > 11000. (sysdate-hiredate ranges from about 10k to 12k). You could use a field you calculate in the query, or one you filled in through some procedure, doesn't matter :)

select empno, ename, job, mgr, hiredate, sal, comm, deptno, 
       trunc((trunc(sysdate)-hiredate)) to_colour_or_not 
  from emp

You will need 2 dynamic actions to colour rows in reports: an onload action, and an after refresh. If you skip the after refresh, rows won't be coloured after for example pagination, due to partial page refreshing.

Dynamic action one: After refresh on the region:

True action:

$("td[headers='TO_COLOUR_OR_NOT']").each(function(){
alert($(this).text());
   if(parseInt($(this).text()) > 11000){
      $(this).css({"background-color":"red"});
   };
});

Example of using one column as a condition to colour another column. Always be carefull with what you test and what you test it for! For example, the hiredate column is a date, be sure to treat it as such if necessary! Further caution too: if your date format is set as DD-MON-YYYY, then you'd have to do the mapping for month to number (JAN = 1, DEC = 12)! Maybe it is an option to change the date format for this column even...

$("td[headers='HIREDATE']").each(function(){
   var i_date = $(this).text();
   //date format = MM/DD/YYYY
   //be carefull with date formats. 
   //in my case, i know my date format and know it won't change
   //my code is far from a complete parse of possible date values!
   var dMonth = i_date.substring(0, 2),
       dDay = i_date.substring(3, 5),
       dYear = i_date.substring(6);    
   var d = new Date(dYear, dMonth, dDay, 0, 0, 0, 0);

   if(d.getFullYear() <= 1981){
      //we are looping over TD elements. I want to colour the 
      //column ENAME in red when the condition is true for this row.
      //so, $(this) = TD element we loop with. parent = TR element,
      //then back to the children of the row
      $(this).parent().children("td[headers='ENAME']").css({"background-color":"red"});
   };
});

The second dynamic action: on load

As true action, use the same code as the true action for the refresh.

With the JS you can do whatever you want: you just need to know which cells you wish to paint. Use the [headers=''] to target the cells you'd like (jquery selectors). Instead of the css(), you can use addClass for example, if that is more what you'd like.

Do mind: IRs come with built-in mouseover actions. This causes your painted cells not to show in their colour when there is a mouseover action. If you don't want this, you'd need another dynamic action for the mouseover/mouseleave events, and target those cells necessary.

As for scheduled jobs: check out DBMS_JOBS.

这篇关于Oracle APEX - 根据包含值在交互式报表中的单元格中有条件地更改文本颜色？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！