Progress Bar for Processes

1. Create an HTML area on a page where you are going to query the progress of a Process.

2. You need to setup a "progress" table for your process. You will need to update the values from within the process while it is running.

e.g. fields EMPLOYEES_TOTAL | EMPLOYEES_PROCESSED etc.

You will probably have to run a precursory SQL to count how many rows are expected to be processed up front, and then update the progress as each row or set of rows is processed.

3. Here is some sample code to update the HTML area with details of how the process is progressing. CC_DERIVED_UOA.DESCRLONG is the HTML Area on the page.

Function UpdateProgressBar(&title, &complete, &total) Returns string
   
   Local string &str = "";
   &str = &str | "   <tr><td style=""font-size:small"">" | &title | ":&nbsp;</td>";
   
   /* Calc pct complete. */
   If &total > 0 Then
      &pct = (&complete / &total) * 10;
   Else
      &pct = 10;
   End-If;
   
   /* Update bars. */
   For &i = 1 To 10
      If &pct >= &i Then
         If &total = 0 Then
            &str = &str | "      <td BGCOLOR=""#CCCCCC"">&nbsp;</td>"; /* grey */
         Else
            &str = &str | "      <td BGCOLOR=""#AAFFAA"">&nbsp;</td>"; /* green */
         End-If;
      Else
         &str = &str | "      <td BGCOLOR=""#FFAAD4"">&nbsp;</td>"; /* red */
      End-If;
   End-For;
   
   &str = &str | "      <td style=""font-size:x-small"">&nbsp;&nbsp;" | &complete | " / " | &total | "</td>";
   &str = &str | "   </tr>";
   
   Return &str;
   
End-Function;



Local Rowset &rsCC_PROGRESS_UOA = CreateRowset(Record.CC_PROGRESS_UOA);
&rsCC_PROGRESS_UOA.Fill(); /* only ever one row */
Local Record &recCC_PROGRESS_UOA = &rsCC_PROGRESS_UOA(1).GetRecord(Record.CC_PROGRESS_UOA);

&strl_progbar = &strl_progbar | "<table>";
&strl_progbar = &strl_progbar | UpdateProgressBar("Employees", &recCC_PROGRESS_UOA.CC_FULLTM_COMPLETE.Value, &recCC_PROGRESS_UOA.CC_FULLTM_TOTAL.Value);
&strl_progbar = &strl_progbar | UpdateProgressBar("Casuals", &recCC_PROGRESS_UOA.CC_CASUAL_COMPLETE.Value, &recCC_PROGRESS_UOA.CC_CASUAL_TOTAL.Value);
&strl_progbar = &strl_progbar | UpdateProgressBar("Scholarships", &recCC_PROGRESS_UOA.CC_SCHOL_COMPLETE.Value, &recCC_PROGRESS_UOA.CC_SCHOL_TOTAL.Value);
&strl_progbar = &strl_progbar | "</table><br>";

CC_DERIVED_UOA.DESCRLONG.Value = &strl_progbar;

Filter Retro Payments

SELECT
 (select pin_nm from ps_gp_pin where pin_num = a.pin_num) AS PIN_NM
, A.EMPLID
, A.EMPL_RCD
, A.PIN_NUM
, Sum(A.CALC_RSLT_VAL)
, A.RATE_RSLT_VAL
, Sum(A.UNIT_RSLT_VAL)
, Sum(A.PCT_RSLT_VAL)

FROM 
  PS_GP_RSLT_ERN_DED A
, PS_GP_PYE_PRC_STAT C

WHERE A.PIN_NUM = 13265 --#PinNum
AND A.EMPLID = '1000269' --$Emplid
AND A.EMPL_RCD = 0 --#Empl_Rcd
AND A.CAL_RUN_ID = '2010F12' --$Calendar_ID

AND C.EMPLID = A.EMPLID
AND C.CAL_RUN_ID = A.CAL_RUN_ID
AND C.EMPL_RCD = A.EMPL_RCD
AND C.GP_PAYGROUP = A.GP_PAYGROUP
AND C.CAL_ID = A.CAL_ID
AND C.ORIG_CAL_RUN_ID = A.ORIG_CAL_RUN_ID
AND C.PRD_TYPE = 'C'

As Of Date in Oracle SQL - Back to the Future

This works:

select *
from
   (select * from ps_job where emplid = '123')
AS OF TIMESTAMP SYSDATE - 1;

Binary Search

Example of:

Function BinarySearch(&arr, &srchVal, &low As integer, &high As integer) Returns integer
  
   If (&high < &low) Then
      Return - 1; /* not found */
   End-If;
  
   &mid = &low + ((&high - &low) / 2);
   If &arr [&mid] > &srchVal Then
      Return BinarySearch(&arr, &srchVal, &low, &mid - 1);
   Else
      If &arr [&mid] < &srchVal Then
         Return BinarySearch(&arr, &srchVal, &mid + 1, &high);
      Else
         Return &mid; /* found */
      End-If;
   End-If;
  
End-Function;

I don't often post random snippets of SQL, but I thought this was a good one. Rare enough to be able to forget it easily, and perhaps will be required again.

This returns schedules based on the schedule id(s) that is/are current for a given employee during a given payment period. If there is no schedule id default to 'UOA01'.

The first part was tricky as sch_assign is an effective dated table, and has no required end date, but each row does end when the next effective date comes into effect. I solved that using a subselect to derive the end_dt. Easy enough.

The next part, defaulting in 'UOA01' was a little trickier. A union to always bring in UOA01, but then a not exists subselect to only bring in where part A of the union returned nothing.

Perhaps easy when you see the solution first, this one took me quite a few attempts, and half the afternoon. Saved for prosperity and that time in the future when I ask myself "I remember doing similar...".

Apart from doing some tricky things with dates this SQL always returns a value. There is a default value in the SQL.

select
  %DateOut(dur)
, b.sched_hrs
from
    (select
      a.schedule_id
    , a.effdt as bgn_dt
    ,
        (select min(x.effdt) - 1
        from ps_sch_assign x
        where x.emplid = a.emplid
        and x.empl_rcd = a.empl_rcd
        and x.effdt < a.effdt) as end_dt
    from %Table(sch_assign) a
    where a.emplid = %Bind(cc_a20_uoa_aet.emplid)
    and a.empl_rcd = %Bind(cc_a20_uoa_aet.empl_rcd)
    and a.schedule_id <> ' '
    and a.effdt < %Bind(cc_a20_uoa_aet.prd_end_dt)
    union
    select 
      'UOA01'
    , to_date('01/01/2000', 'dd/mm/yyyy') as bgn_dt
    , to_date('31/12/9999', 'dd/mm/yyyy') as end_dt
    from dual
    where not exists
        (select 1
        from %Table(sch_assign) a
        where a.emplid = %Bind(cc_a20_uoa_aet.emplid)
        and a.empl_rcd = %Bind(cc_a20_uoa_aet.empl_rcd)
        and a.schedule_id <> ' '
        and a.effdt < %Bind(cc_a20_uoa_aet.prd_end_dt))) a
, %Table(sch_clnd_vw) b
where b.schedule_id = a.schedule_id
and b.dur between %Bind(cc_a20_uoa_aet.prd_bgn_dt) and %Bind(cc_a20_uoa_aet.prd_end_dt)
and (b.dur <= a.end_dt or a.end_dt is null)
and b.sched_hrs > 0
order by dur

HP Quality Center (QC)

In case I'm ever asked to export from QC again.

/*
Built for Jira 26921
Michael Nitschke 30/03/2010

Examples:
define testPlanRoot = 'SA Master'
define testLabRoot = 'System Testing\SYS1 SA'

define testPlanRoot = 'MTP Testing'
define testLabRoot = 'MTP Testing\MTP 13'

Hint: you can optionally add a trailing back-slash on the testLabRoot variable if you
need to clearly define it as seperate from and similarly named folder,
e.g. 'SYSTEM' and 'SYSTEM1'.
*/

/* notes:
Test Lab runs do not neccessarily line up in the same folders in Test Lab as the folders that are in Test Plan ...if that makes sense.
Joined by TEST_ID but the Test Lab folder Name can be changed, so the lab results for a given test plan may be in a test lab folder that has a different name to the plan.
hard to explain.
*/

define testPlanRoot = 'SA Master'
define testLabRoot = 'System Testing\System Test Phase 1 - SA\'


select * from (

SELECT
  substr(path, 2, instr(path,'\',1,2) - 2) as lev1
, substr(path, instr(path,'\',1,2) + 1, instr(path,'\',1,3) - instr(path,'\',1,2) - 1) as lev2
, substr(path, instr(path,'\',1,3) + 1, instr(path,'\',1,4) - instr(path,'\',1,3) - 1) as lev3
, substr(path, instr(path,'\',1,4) + 1, instr(path,'\',1,5) - instr(path,'\',1,4) - 1) as lev4
, substr(path, instr(path,'\',1,5) + 1, instr(path,'\',1,6) - instr(path,'\',1,5) - 1) as lev5
, substr(path, instr(path,'\',1,6) + 1, instr(path,'\',1,7) - instr(path,'\',1,6) - 1) as lev6
, nvl(users.full_name, 'anon.') || ' (' || test.ts_responsible || ')' as sweet_fa
, test.ts_name as test_script_name
, test.ts_status
, run.rn_status

from (
 select
 al_father_id, al_item_id, sys_connect_by_path(al_description, '\') || '\' as path
 FROM (SELECT * from all_lists where al_father_id <> 2329) -- 2329 is a 'bad', invisible folder. Yes really.
 connect by prior al_item_id = al_father_id
) path
, test
, users
,
 (select run.rn_test_id, run.rn_status
 from run
 where exists
  (select 1
  from cycle
  where cycle.cy_cycle_id = run.rn_cycle_id
  and cycle.cy_folder_id in
   (select cf_item_id from
    (select cf_item_id, sys_connect_by_path(cf_item_name, '\') as path
    from cycl_fold a
    connect by prior cf_item_id = cf_father_id)
   where path like '\' || &testLabRoot || '%'))
 and run.rn_execution_date =
  (select max(r.rn_execution_date)
  from run r
  where r.rn_test_id = run.rn_test_id)
 and run.rn_execution_time =
  (select max(rn_execution_time)
  from run r
  where r.rn_test_id = run.rn_test_id
  and r.rn_execution_date = run.rn_execution_date)) run


where path.path like '\' || &testPlanRoot || '%'

and test.ts_subject (+) = path.al_item_id
and users.user_name (+) = test.ts_responsible

and run.rn_test_id (+) = test.ts_test_id

)
where lev1 = &testPlanRoot -- Removes data where same folder name exists on another branch.
and not lev2 is null -- Don't need base folder only in data (blanks).
and not lev3 is null
order by 1, 2, 3, 4, 5, 6, 8
;

%DateIn or %DateOut

Sometimes you need both:

%Select(CC_DAY_UOA_AET.DATE_DAY, CC_DAY_UOA_AET.SCHED_HRS)

SELECT
%DateOut(%DateIn(%Bind(CC_PYDT_UOA_AET.PYMT_DT)) + daynum)
, sched_hrs
FROM ps_sch_defn_dtl a
...