/**************************************************************/
/* 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 the PSRF_REPORT_CREATE message    */
/* which is published by the process scheduler.               */
/**************************************************************/

REM ************* End of constants for processing ***************************;
Local string &sParmList1;
Local string &sParmlist2;

REM Get the data from the message published by the Process Scheduler;
Local Rowset &rsReportMessage = GetMessage().GetRowset();

REM Dig out the Expiration Date and other attribtes to be used;
Local Row &rowReport = &rsReportMessage.GetRow(1);
Local Record &recReport = &rowReport.GetRecord(Record.PSRF_RCREAT_WRK);
Local string &sReportURL = &recReport.GetField(Field.PSRF_REPORT_URL).Value;

Local date &dReportExpireDate = &recReport.GetField(Field.EXPIRATION_DATE).Value;
Local date &dReportCreationDateTime = &recReport.GetField(Field.PSRF_CREATION_DTTM).Value;
Local integer &nPrcsInstance = &recReport.GetField(Field.PRCSINSTANCE).Value;
Local string &sprcsinstance = String(&nPrcsInstance);
Local integer &nContentID = &recReport.GetField(Field.CONTENTID).Value;

Local string &sPrcsType = &recReport.GetField(Field.PRCSTYPE).Value;
Local string &sPrcsName = &recReport.GetField(Field.PRCSNAME).Value;
Local string &sReportName = &recReport.GetField(Field.PSRF_REPORT_NAME).Value;
Local string &sFolderName = &recReport.GetField(Field.PSRF_FOLDER_NAME).Value;
Local string &sFolderPath = &recReport.GetField(Field.PSRF_FOLDER_PATH).Value;

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;
   rem 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 ******;
      rem warning ("Running nVision Rule");
      rem set expiration date of current item (1 year);
      &dNewExpDate = AddToDate(&dReportCreationDateTime, 1, 0, 0);
      SQLExec("UPDATE %TABLE(:1) SET EXPIRATION_DATE = %DATEIN(:2) WHERE PRCSINSTANCE = :3 AND CONTENTID = :4", Record.CDM_LIST, &dNewExpDate, &nPrcsInstance, &nContentID);
      
      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, "%");
      rem warning ("Old parm list = " | &NvsParmList);
      rem 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) ******;
      rem 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, "%");
      rem warning ("Old parm list = " | &NvsParmList);
      rem warning ("New Parm list = " | &sParmList1);
      
      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;