/**************************************************************/
/* Grey Sparling Solutions - Reset Report Expiration code     */
/* This code was developed and is owned by Grey Sparling      */
/* Solutions                                                  */
/**************************************************************/
/* This PeopleCode shows several examples of changing report  */
/* expiration dates                                           */
/*                                                            */
/* This code is attached to an application engine for testing */
/**************************************************************/

rem ***** Get Maximum Process Instance for Processing ******;
Local integer &nPrcsInstance;
Local integer &nContentID;
Local Record &UpdtRec;
Local string &NvsParmList;
Local number &nParmLen;
Local string &sParmList1;
Local string &sParmlist2;
Local string &sPrcsInstance;

rem get maximum process instance for initial testing against the most recent report run;
Warning ("Getting Maximum Process Instance");
SQLExec("SELECT MAX(CONTENTID) FROM %TABLE(:1)", Record.CDM_LIST, &nContentID);
rem set a specific contentid to test the logic for a specific report;
rem &nContentID = 8613;
Warning ("Max Content ID = " | &nContentID);
SQLExec("SELECT MAX(PRCSINSTANCE) FROM %TABLE(:1) WHERE CONTENTID = :2", Record.CDM_LIST, &nContentID, &nPrcsInstance);
&sPrcsInstance = String(&nPrcsInstance);
Warning ("Max Process Instance = " | &sPrcsInstance);

&UpdtRec = CreateRecord(Record.CDM_LIST);
&UpdtRec.GetField(Field.PRCSINSTANCE).Value = &nPrcsInstance;
&UpdtRec.GetField(Field.CONTENTID).Value = &nContentID;
If &UpdtRec.SelectByKey() Then
   
   Local date &dReportExpireDate = &UpdtRec.EXPIRATION_DATE.Value;
   Local date &dReportCreationDateTime = &UpdtRec.ENDDTTM.Value;
   
   Local string &sPrcsType = &UpdtRec.PRCSTYPE.Value;
   Local string &sPrcsName = &UpdtRec.PRCSNAME.Value;
   rem Local string &sReportName = &UpdtRec.PSRF_REPORT_NAME.Value;
   Warning ("Successfully Retrieved CDM List record, PRCSNAME = " | &sPrcsName);
End-If;

REM ************* End of constants for processing ***************************;

Local date &dNewExpDate;
Local string &sPrcsInstanceParm = "-I" | String(&nPrcsInstance);

If &sPrcsName = "DRILLDWN" Then
   REM ************ Example 1 - Set Expiration of nVision Drills to be 1 day from creation date;
   Warning ("Running Drilldown Rule");
   &dNewExpDate = AddToDate(&dReportCreationDateTime, 0, 0, 1);
   SQLExec("UPDATE %TABLE(:1) SET EXPIRATION_DATE = %DATEIN(:2) WHERE PRCSINSTANCE = :3 AND CONTENTID = :4", Record.CDM_LIST, &dNewExpDate, &nPrcsInstance, &nContentID);
Else
   
   If &sPrcsName = "NVSRUN" Then
      REM ************ Example 2 - Set Expiration of current nVision report to 1 year, but expiring historical ones with same parameters ******;
      Warning ("Running nVision Rule");
      rem set expiration date of current item (1 year);
      &dNewExpDate = AddToDate(&dReportCreationDateTime, 1, 0, 0);
      SQLExec("SELECT count(*) FROM %TABLE(:1) WHERE PRCSINSTANCE = :2 AND CONTENTID = :3", Record.CDM_LIST, &nPrcsInstance, &nContentID, &nRows);
      Warning ("Number of Rows Returned by selecting from contentid and process instance against CDM List for current process instance = " | &nRows);
      SQLExec("UPDATE %TABLE(:1) SET EXPIRATION_DATE = %DATEIN(:2) WHERE PRCSINSTANCE = :3 AND CONTENTID = :4", Record.CDM_LIST, &dNewExpDate, &nPrcsInstance, &nContentID);
      SQLExec("SELECT expiration_date FROM %TABLE(:1) WHERE PRCSINSTANCE = :2 AND CONTENTID = :3", Record.CDM_LIST, &nPrcsInstance, &nContentID, &dExpDateAfter);
      Warning ("Expration Date from table after update statement " | &dExpDateAfter);
      
      REM Get Paramter list for current report;
      SQLExec("SELECT PARMLIST FROM %TABLE(:1) WHERE PRCSINSTANCE = :2", Record.PSPRCSPARMS, &nPrcsInstance, &NvsParmList);
      &nParmLen = Len(&NvsParmList);
      
      rem replace the process instance with a %;
      &sParmList1 = Substitute(&NvsParmList, &sPrcsInstance, "%");
      Warning ("Old parm list = " | &NvsParmList);
      Warning ("New Parm list = " | &sParmList1);
      
      rem set expiration date of all nVision reports with same parameters to be yesterday;
      &dNewExpDate = AddToDate(&dReportCreationDateTime, 0, 0, - 1);
      
      SQLExec("UPDATE %TABLE(:1) SET EXPIRATION_DATE = %DATEIN(:2) WHERE PRCSINSTANCE IN (SELECT PRCSINSTANCE FROM PSPRCSPARMS WHERE PARMLIST LIKE :3 AND PRCSINSTANCE < :4)", Record.CDM_LIST, &dNewExpDate, &sParmList1, &nPrcsInstance);
   Else
      
      REM ************ Example 3 - Set Expiration of Historical nVision Reports and Queries (keeping current one) ******;
      Warning ("Running All Others Rule");
      rem leave expiration date of current item alone;
      
      REM Get Paramter list for current report;
      SQLExec("SELECT PARMLIST FROM %TABLE(:1) WHERE PRCSINSTANCE = :2", Record.PSPRCSPARMS, &nPrcsInstance, &NvsParmList);
      &nParmLen = Len(&NvsParmList);
      
      rem replace the process instance with a %;
      &sParmList1 = Substitute(&NvsParmList, &sPrcsInstance, "%");
      Warning ("Old parm list = " | &NvsParmList);
      Warning ("New Parm list = " | &sParmList1);
      SQLExec("SELECT COUNT(*) FROM %TABLE(:1) WHERE PRCSINSTANCE IN (SELECT PRCSINSTANCE FROM PSPRCSPARMS WHERE PARMLIST LIKE :2 AND PRCSINSTANCE < :3)", Record.CDM_LIST, &sParmList1, &nPrcsInstance, &nRows);
      Warning ("Rows to be updated = " | &nRows);
      
      rem set expiration date of all reports with same parameters to be -1 day;
      &dNewExpDate = AddToDate(&dReportCreationDateTime, 0, 0, - 1);
      
      SQLExec("UPDATE %TABLE(:1) SET EXPIRATION_DATE = %DATEIN(:2) WHERE PRCSINSTANCE IN (SELECT PRCSINSTANCE FROM PSPRCSPARMS WHERE PARMLIST LIKE :3 AND PRCSINSTANCE < :4)", Record.CDM_LIST, &dNewExpDate, &sParmList1, &nPrcsInstance);
      
   End-If;
End-If;