// NOTE USED - adds supplementary data fdor the second tab
private AssetDataRecordset AddSupplementaryData(AssetDataRecordset recordset)
{
string[] AssetCountTabHeaders = { "Cur-Qty", "Level", "Zone-Type", "Zone", "Asset-Category", "Asset-Type", "Asset-Model", "Workflow Statuses" };
const string TAB2Name = "PAR Count"; // the name of the second tab
// create the output recordset
AssetDataRecordset targetData = new AssetDataRecordset();
targetData.LastModified = recordset.LastModified; // initialise with source recordset's cutoff time
// add the file headers and data rows into the targetData output recordset
try {
// COLUMNS -------------------------------------------------------------------
targetData.Columns = new List <string>(AssetCountTabHeaders); // add the Equipment Level Management Report headers
// ROWS --------------------------------------------------------------------- // add escaped data for each row
foreach (AssetDataRow sourceRow in recordset.Rows)
{
AssetDataRow newRow = new AssetDataRow();
// add the row - add data for each ELMR COLUMN
newRow.Fields.Add(EscapeCharacters(sourceRow.Fields[recordset.Index.AssetQuantity])); // 01. Cur-Qty
newRow.Fields.Add(EscapeCharacters(sourceRow.Fields[recordset.Index.Level])); // 02. Level
newRow.Fields.Add(EscapeCharacters(sourceRow.Fields[recordset.Index.ZoneType])); // 03. Zone-Type
newRow.Fields.Add(EscapeCharacters(sourceRow.Fields[recordset.Index.AssetSublocationOrZone])); // 04. Zone
newRow.Fields.Add(EscapeCharacters(sourceRow.Fields[recordset.Index.AssetCategory])); // 05. Asset-Category
newRow.Fields.Add(EscapeCharacters(sourceRow.Fields[recordset.Index.AssetType])); // 06. Asset-Type
newRow.Fields.Add(EscapeCharacters(sourceRow.Fields[recordset.Index.AssetModel])); // 07. Asset-Model
newRow.Fields.Add(EscapeCharacters(sourceRow.Fields[recordset.Index.WorkflowStatus])); // 08. Workflow Statuses
// add the row to the recordset
targetData.Rows.Add(newRow);
}
//set the name of the second tab
targetData.Name = TAB2Name;
} catch (Exception ex) {
Log.Error(ex.Message, EventLogger.EventIdEnum.QUERYING_DATA, ex);
}
return(targetData);
}
/* creates a file with the headers and data needed for output PAR equipment level report
* and writes out a file depending on the DataFileType of this service subclass - either a CSV or XLSX - .
* depending on the DataFileType of this service . The created filename is timestamped based on the last change
* date in AssetDataRecordset.LastModified. The returned asset recordset will have zero rows if there are no changed
* records. This method is invoked in the subclass by the service event (e.g LSAsetPARData.OnService).
* The subclass executes service specific functions then invokes the super to write out the recordset to disk
* The LSapard output file structure is :
* | PAR Rule| PAR Rule-Status | PAR Rule-Qty | PAR Rule-Repl Qty | PAR Rule-Date | Cur-Status | Cur-Qty | Cur-Repl Qty | Cur-Status Date
| Level | Zone | Zone-Type | Asset-Category | Asset-Type | Asset-Model | Asset-Model Descr | Workflow Statuses | PAR Rule-ID | Asset-Model/Type ID | Zone-ID
* Unlike other asset data file exports this output file does NOT append the database column names from the query result.
*/
internal sealed override AssetDataRecordset WriteFile(AssetDataRecordset recordset, string targetFileSpec = "", bool overwrite = false)
{
string[] ParTabHeaders = { "Cur-Status", "Cur-Qty", "Cur-Repl Qty", "PAR Rule-Status", "PAR Rule-Qty", "PAR Rule-Repl Qty",
"Level", "Zone-Type", "Zone", "Asset-Category", "Asset-Type", "Asset-Model", "Asset-Model Descr","Workflow Statuses",
"PAR Rule-Name", "PAR Rule-Date", "Cur-Status Date" };
const string ABOVEParStatus = "Above PAR"; // status label in CORE tbEnum
const string ATParStatus = "At PAR"; // status label in CORE tbEnum
const string BELOWParStatus = "Below PAR"; // status label in CORE tbEnum
const string NOParStatus = ""; // status when PAR is reset
const string MASTERFileName = "MASTER_AssetPARDataFile"; // the name of the master file. This file is overwritten each time a new AssetPARDataFile is cretaed in the target folder.
const string TAB1Name = "PAR Data"; // the name of the first tab
// create the output recordset
AssetDataRecordset targetData = new AssetDataRecordset();
targetData.LastModified = recordset.LastModified; // initialise with source recordset's cutoff time
// add the file headers and data rows into the targetData output recordset
try {
// COLUMNS -------------------------------------------------------------------
targetData.Columns = new List <string>(ParTabHeaders); // add the Equipment Level Management Report headers
// ROWS --------------------------------------------------------------------- // add escaped data for each row
foreach (AssetDataRow sourceRow in recordset.Rows)
{
AssetDataRow newRow = new AssetDataRow();
// row variables
int curReplQty = 0;
int curQty, parRuleQty, parRuleReplQty; // replenishment quantity depends on current qty, PAR Rule qty, and PAR Rule qty repl qty
string parRuleStatus = sourceRow.Fields[recordset.Index.ParRuleStatus];
string curStatus = sourceRow.Fields[recordset.Index.AssetOrParStatus]; // initialise from CORE query - but overwrite below to workaround 1) lagging updates in CORE and 2) inconsistent current state in CORE when a rule's PAR Status is reconfigured
string curStatusDate = sourceRow.Fields[recordset.Index.LastChanged]; // read the date as a string
Int32.TryParse(sourceRow.Fields[recordset.Index.AssetQuantity], out curQty);
Int32.TryParse(sourceRow.Fields[recordset.Index.ParRuleQty], out parRuleQty);
Int32.TryParse(sourceRow.Fields[recordset.Index.ParRuleRepQty], out parRuleReplQty);
DateTime rowLastChanged = targetData.LastModified; // initialise, replace with row's lastchanged next
// calculate the current replenishment quantity for this row // the replenishment qty calculates qty needed to pick up or drop off so that equipment quantities are set to level at which PAR Status would get reset (regardless of whether the current PAR status is set)
switch (parRuleStatus)
{
case ABOVEParStatus:
if (curQty > (parRuleQty - parRuleReplQty)) // do not recommend pickup if current quantity is already low, only if it is higher than optimum level determined by parRuleQty - parRuleReplQty
{
curReplQty = (parRuleQty - parRuleReplQty) - curQty; // for Above PAR rules the replenishment is negative (pick up): based on (PAR Rule Qty - PAR Rule Repl Qty) - current qty
}
break;
case BELOWParStatus: // do not recommend drop off if current quantity is already high, only if it is lower than optimum level determined by parRuleQty + parRuleReplQty
if (curQty < (parRuleQty + parRuleReplQty))
{
curReplQty = (parRuleQty + parRuleReplQty) - curQty; // for Below PAR rules the replenishment qty is positive (drop off): based on (PAR Rule Qty + PAR Rule Repl Qty) - current qty
}
break;
case ATParStatus: // for At PAR rules
if (curQty >= parRuleQty) // if the current qty is greater than the rule qty
{
curReplQty = (parRuleQty + parRuleReplQty) - curQty; // status is reset if count goes to greater than or equal to parRuleQty + parRuleReplQty
}
else // if the current qty is less than the rule qty
{
curReplQty = (parRuleQty - parRuleReplQty) - curQty; // status is reset if count goes to less than or equal to parRuleQty - parRuleReplQty
}
break;
}
// SET current status - check if the current status is not blank and different to the par rule status, of so it should be set or reset to blank - as there is a defect in CORE which shows an incorrect status when a par rule's status is changed after a current status has been set previously
if (String.IsNullOrEmpty(curStatus) || (!String.IsNullOrEmpty(curStatus) && (curStatus != parRuleStatus))) // set if curstatus empty, or if current status in CORE does not match the rule status
{
if ((parRuleStatus == BELOWParStatus) && (curQty < parRuleQty)) // must be less than (NOT equal to)
{
curStatus = BELOWParStatus;
}
else if ((parRuleStatus == ABOVEParStatus) && (curQty > parRuleQty))
{
curStatus = ABOVEParStatus;
}
else if ((parRuleStatus == ATParStatus) && (curQty == parRuleQty))
{
curStatus = ATParStatus;
}
else
{
curStatus = NOParStatus;
}
// RESET current status - if the status is currently set check if it needs to be reset (as CORE is very slow to do this, and the report will show quantities which contradict stauses until CORE does its updates)
}
else if (!String.IsNullOrEmpty(curStatus) && (curStatus == parRuleStatus)) // check reset if current status is set
{
if ((parRuleStatus == BELOWParStatus) && (curQty >= (parRuleQty + parRuleReplQty))) // must be greater than OR equal to
{
curStatus = NOParStatus;
}
else if ((parRuleStatus == ABOVEParStatus) && (curQty <= (parRuleQty - parRuleReplQty)))
{
curStatus = NOParStatus;
}
else if ((parRuleStatus == ATParStatus) && (curQty <= (parRuleQty - parRuleReplQty) || curQty >= (parRuleQty + parRuleReplQty)))
{
curStatus = NOParStatus;
}
}
// track the last changed par status datetime // for this service track last changed based on par status LastChanged field
if (!String.IsNullOrEmpty(curStatusDate)) // skip rows which do not refer to a par rule, these rows are for asset counts but do not have a curStatusDate
{
if (!(DateTime.TryParse(curStatusDate, out rowLastChanged))) // check if the date can be parsed - it may be null if a par status had never been set for this par rule row
{
rowLastChanged = targetData.LastModified; // if the data field could not be parsed keep the previous date time unchanged
Log.Trace("Could not parse row LastChanged.. (" + curStatusDate + ")", EventLogger.EventIdEnum.QUERYING_DATA);
}
}
// add the row - add data for each ELMR COLUMN
newRow.Fields.Add(EscapeCharacters(curStatus)); // Cur-Status
newRow.Fields.Add(curQty.ToString()); // Cur-Qty
newRow.Fields.Add(curReplQty.ToString()); // Cur-Repl Qty
newRow.Fields.Add(EscapeCharacters(parRuleStatus)); // PAR Rule-Status
newRow.Fields.Add(parRuleQty.ToString()); // PAR Rule-Qty
newRow.Fields.Add(parRuleReplQty.ToString()); // PAR Rule-Repl Qty
newRow.Fields.Add(EscapeCharacters(sourceRow.Fields[recordset.Index.Level])); // Level
newRow.Fields.Add(EscapeCharacters(sourceRow.Fields[recordset.Index.ZoneType])); // Zone-Type
newRow.Fields.Add(EscapeCharacters(sourceRow.Fields[recordset.Index.AssetSublocationOrZone])); // Zone
newRow.Fields.Add(EscapeCharacters(sourceRow.Fields[recordset.Index.AssetCategory])); // Asset-Category
newRow.Fields.Add(EscapeCharacters(sourceRow.Fields[recordset.Index.AssetType])); // Asset-Type
newRow.Fields.Add(EscapeCharacters(sourceRow.Fields[recordset.Index.AssetModel])); // Asset-Model
newRow.Fields.Add(EscapeCharacters(sourceRow.Fields[recordset.Index.AssetModelDescription])); // Asset-Model Descr
newRow.Fields.Add(EscapeCharacters(sourceRow.Fields[recordset.Index.WorkflowStatus])); // 17. Workflow Statuses
newRow.Fields.Add(EscapeCharacters(sourceRow.Fields[recordset.Index.ParRule])); // 14. PAR Rule
newRow.Fields.Add(EscapeCharacters(sourceRow.Fields[recordset.Index.CoreModifiedDate])); // 15. PAR Rule-Date
newRow.Fields.Add(EscapeCharacters(sourceRow.Fields[recordset.Index.LastChanged])); // 16. Cur-Status Date
// keep a tab on the latest change to use in the output file
if (rowLastChanged > targetData.LastModified)
{
targetData.LastModified = rowLastChanged;
}
// add the row to the recordset
targetData.Rows.Add(newRow);
}
//set the name of the first tab
targetData.Name = TAB1Name;
// NOTE USED - add supplementary data
// targetData.SupplementaryData = AddSupplementaryData(recordset.SupplementaryData);
// WRITE FILE - write to disk in base class
targetData = base.WriteFile(targetData, overwrite: true); // write with overwrite. Returns lastchanged in targetData recordset
// OVERWRITE and UPDATE the MASTER FILE
if (targetData.Saved)
{
string masterFileSpec = Path.Combine(OutputFolderPath, MASTERFileName + "." + DataFileExtension);
targetData = base.WriteFile(targetData, masterFileSpec, true);
}
} catch (Exception ex) {
Log.Error(ex.Message, EventLogger.EventIdEnum.QUERYING_DATA, ex);
}
return(targetData);
}
/* create an asset data recordset with the headers and data needed for output
* and writes out a file depending on the DataFileType of this service subclass - either a CSV or XLSX - .
* depending on the DataFileType of this service . The created filename is timestamped based on the last change
* date in AssetDataRecordset.LastModified. The returned asset recordset will have zero rows if there are no changed
* records. This method is invoked in the subclass by the service event (e.g LSAsetWorkflowData.OnService).
* The subclass executes service specific functions then invokes the super to write out the recordset to disk
* The LSAWD output file structure is :
* | Asset Category | Asset Type | Asset Model | Asset Code | Asset Status | Last Modified |
* In addition the output file appends the database column names from thje query result, to help with
* data-related troubleshooting and analysis at runtime
* In addition the output file appends the following column to show the time since cutoff, for any records which were changed
* before the current period
* | __Before Cutoff |
*/
internal sealed override AssetDataRecordset WriteFile(AssetDataRecordset recordset, string targetFileSpec = "", bool overwrite = false)
{
string[] COREFileHeaders = { "Asset Category", "Asset Type", "Asset Model", "Asset Code", "Asset Status", "Last Modified" };
const string retroActiveCutoffHeader = "__Changed Before Cutoff"; // double underscore prefix for special columns
const string DBColumnPrefix = "_"; // prefix database columns in the output file with an underscore to make these appear distinct from columns which CORE consumes
const string TAB1Name = "Workflow Data"; // the name of the first tab
const string MINUTEPrecisionDateTimeFormat = "dd/MM/yyyy HH:mm";
// create the output recordset
AssetDataRecordset targetData = new AssetDataRecordset();
targetData.LastModified = recordset.LastModified; // initialise with source recordset's cutoff time
// add the file headers and data rows into the targetData output recordset
try {
// COLUMNS ------------------------------------------------------------------- // add specified columns to comply with the output file specifation, and database columns from the source recordset
targetData.Columns = new List <string>(COREFileHeaders); // CORE COLUMNS - first add the service-specific headers as specified
targetData.Columns.Add(EscapeCharacters(retroActiveCutoffHeader)); // SUPPLEMENTARY COLUMNS - add "__Changed Before Cutoff" to show which rows were retroactively included in the results
foreach (string column in recordset.Columns) // DB COLUMN HEADERS - append the database column names at the end, prefix each with an underscore to make these distinct from the columns which CORE consumes
{
targetData.Columns.Add(EscapeCharacters(DBColumnPrefix + column));
}
// ROWS --------------------------------------------------------------------- // add escaped data for each row
foreach (AssetDataRow sourceRow in recordset.Rows)
{
AssetDataRow newRow = new AssetDataRow();
DateTime agilityLastChanged = DateTime.Parse(sourceRow.Fields[recordset.Index.LastChanged]); // by default track last changed based on Agility LastChange field
DateTime coreLastChanged = DateTime.Parse(sourceRow.Fields[recordset.Index.CoreModifiedDate]); // use this to track assets which have just been provisioned in CORE and have a null workflow status, the CORE modified date is used to timestamp these record instead of the agility lastmodified
string coreStatus = sourceRow.Fields[recordset.Index.CoreWorkflowStatus].Trim(); // check the CORE workflow status as this needs to be inspected to implement a workaround for defect 2894 (failing workflowstatus updates) see ALM SAD
// check if this is a newly provisioned asset (core status is empty), and if so replace the agility last changed date time with the core last modified. This implements a workaround for defect 2894 (failing workflowstatus updates) see ALM SAD
DateTime lastChanged = agilityLastChanged; // by default the last changed timestamp for the row is based on agility
if (String.IsNullOrEmpty(coreStatus)) // if core status is null it means the asset has just been provisioned and CORE has not yet imported a workflow status: the coreStatus will be null and Last Modified should be set to the core modified date and time
{
lastChanged = coreLastChanged;
}
// keep a tab on the latest change to use in the output file
if (lastChanged > targetData.LastModified)
{
targetData.LastModified = lastChanged;
}
// if the row timestamp is later than the file timestamp then round down lastChanged to the nearest mniute , this is required to implement the second workaround for defect 2894 (failing workflowstatus updates) see ALM SAD
if (lastChanged >= targetData.LastModified)
{
lastChanged = Convert.ToDateTime(lastChanged.ToString(MINUTEPrecisionDateTimeFormat)).AddSeconds(-1); // round down to the minute and subtract a second
}
// add the CORE-specified file fields ---------------------------------------- // CORE COLUMNS - first add the service-specific headers as specified by CORE
newRow.Fields = new List <string>(new string[] { "", "", "" }); // "Asset Category", "Asset Type", "Asset Model" - column 1 - 3, empty - unused but CORE needs these - known defect
newRow.Fields.Add(EscapeCharacters(sourceRow.Fields[recordset.Index.AssetCode])); // "Asset Code" - column 4
newRow.Fields.Add(EscapeCharacters(sourceRow.Fields[recordset.Index.AssetOrParStatus])); // "Asset Status" - column 5
newRow.Fields.Add(EscapeCharacters(lastChanged.ToString(ISO8601DateTimeFormat))); // "Last Modified" - column 6 (LastChanged) e.g. '11/10/2017 16:46:00.507'
// now append the supplementary fields ---------------------------------- // SUPPLEMENTARY COLUMNS - double underscore prefix for special columns
string timeSinceCutoff = "";
if (agilityLastChanged < recordset.LastModified)
{
timeSinceCutoff = (recordset.LastModified.Subtract(agilityLastChanged)).ToString(@"hh\:mm\:ss"); // show how long before the cutoff the asset was changed, ignore if less than a minute as the 1 minute precision error in the filename datetime will show inconsistency
}
newRow.Fields.Add(EscapeCharacters(timeSinceCutoff)); // add the time since cutoff, if the record was changed after the cuttoff this column is left blank
// now append the database fields --------------------------------------- // DB COLUMN HEADERS - append the database column names at the end, prefix each with an underscore to make these distinct from the columns which CORE consumes
foreach (string field in sourceRow.Fields)
{
newRow.Fields.Add(EscapeCharacters(field));
}
// add the row to the recordset
targetData.Rows.Add(newRow);
}
//set the name of the tab
targetData.Name = TAB1Name;
// WRITE FILE - write to disk in base class
targetData = base.WriteFile(targetData, overwrite: false); // write without overwrite. Returns the lastchanged from the targetData recordset
} catch (Exception ex) {
Log.Error(ex.Message, EventLogger.EventIdEnum.QUERYING_DATA, ex);
}
return(targetData);
}
