/* NOTE USED- Reads optional data for the second tab in the data report - NOT USED **/
private AssetDataRecordset ReadSupplementaryData(DateTime cutoff)
{
AssetDataRecordset assetRecords = null;
// column positions in query
const int SQLCurQty = 0, SQLLevel = 1, SQLZoneType = 2, SQLZone = 3, SQLAssetCategory = 4, SQLAssetType = 5, SQLAssetModel = 6, SQLWorkflowStatuses = 7;
try {
/* CONSTRUCT QUERY */
string queryString = string.Format("", LsDatabase);
/* EXECUTE QUERY in base class.. then add INDEX pointers for Asset data attributes */
assetRecords = base.ReadData(queryString); // execute overloaded method in base
if (assetRecords.Rows.Count > 0)
{
// assetRecords.Index.AssetQuantity = SQLCurQty;
}
assetRecords.LastModified = cutoff; // timestamp the result recordset based on the requested cutoff
Log.Trace(this.GetType().Name + Environment.NewLine + "\tqueryString=" + queryString + Environment.NewLine + "\tcutoff= " + cutoff.ToString(SQLDateTimeFormat) + Environment.NewLine + "\tassetRecords.LastModified=" + assetRecords.LastModified.ToString(SQLDateTimeFormat) + Environment.NewLine, EventLogger.EventIdEnum.QUERYING_DATA);
} catch (Exception ex) {
Log.Error(ex.Message, EventLogger.EventIdEnum.QUERYING_DATA, ex);
}
return(assetRecords);
}
// the specialised query string for each service, to retrieve assets for the period after the cutof date until now
internal sealed override AssetDataRecordset ReadData(DateTime cutoff)
{
const int retroActiveHours = 24;
AssetDataRecordset assetRecords = null;
// column positions in query
const int SQLCode = 0, SQLStatusCode = 1, SQLLastChangeDate = 2, SQLCoreStatus = 4, SQLCoreModifiedDate = 6;
try {
/* LSAWD QUERY - The batch of records in the data file retrieved in each period, will contain:
* 1) assets which have been updated in Agility after the previous cutoff time
* 2) and, assets which have a 'null' workflow status in CORE (these assets would have just been provisioned in CORE)
* workflow status is retrieved for assets which exist in both CORE and Agility (the assets must have the same CORE UID and Agility Code)
* RETROACTIVE CUTOFF - the query will backdate and include a day of records before the cutoff time
* This is required to work around a functionality gap in the CORE import process: CORE imports only the most recent file even though there may be
* more than one new file produced since the last import, for example after an outage or if two service timer events occurred during the CORE
* import interval. The retroactive cutoff in the LSAWD query will therefore include all records changed in the 24 hours before the cutoff.
* to ensure that changes in a previous file which had not been imported, would be included by CORE when the latest file is imported, and CORE
* will update these assets retroactively. Note however that the query will trigger a data file output only if assets had changed after the cutoff.
*/
DateTime retroActiveCutoff = cutoff.Subtract(TimeSpan.FromHours(retroActiveHours)); // set the retroactive cutoff to include previous changes
/* CONSTRUCT QUERY - gets results only if there were changes since the cutoff. If there were, also includes the changes since the retroativecutoff,
* in case CORE has not imported the previous extract (due to system being down or if the LSa*d service had stopped). */
string queryString = string.Format("IF EXISTS(SELECT TOP 1 AgilityAsset.Code, AgilityAsset.LastChangeDate FROM {0}..pmAsset AgilityAsset INNER JOIN "
+ "(SELECT tbAsset.UID, tbAsset.WorkflowStatus FROM {1}..tbAsset WHERE tbAsset.IsDeleted = 0 AND tbAsset.UID IS NOT NULL) CoreAsset ON "
+ "AgilityAsset.Code = CoreAsset.UID WHERE AgilityAsset.LastChangeDate > '{2}' OR CoreAsset.WorkflowStatus = 0) SELECT AgilityAsset.Code, "
+ "AgilityStatus.StatusCode, AgilityAsset.LastChangeDate, CoreAsset.UID CoreUID, CoreAsset.StatusName CoreStatus, CoreAsset.RFIDTagID CoreRFID, "
+ "CoreAsset.ModifiedDate CoreModifiedDate FROM {0}..pmAsset AgilityAsset INNER JOIN {0}..syAssetStatus AgilityStatus ON AgilityAsset.AssetStatusID = "
+ "AgilityStatus.AssetStatusID INNER JOIN(SELECT tbAsset.UID, tbAsset.WorkflowStatus, tbWorkflowStatus.StatusName, tbRFIDBank.RFIDTagID, "
+ "tbRFIDBank.IsDeleted RFIDIsDeleted, tbAsset.ModifiedDate FROM {1}..tbAsset LEFT JOIN {1}..tbRFIDBank ON tbAsset.RFIDBankIDF = tbRFIDBank.RFIDBankIDP "
+ "LEFT JOIN {1}..tbWorkflowStatus ON tbAsset.WorkflowStatus = tbWorkflowStatus.WorkflowStatusIDP WHERE tbAsset.IsDeleted = 0 AND (tbRFIDBank.IsDeleted "
+ "IS NULL OR tbRFIDBank.IsDeleted = 0)) CoreAsset ON AgilityAsset.Code = CoreAsset.UID WHERE(AgilityAsset.LastChangeDate > '{3}' OR "
+ "CoreAsset.WorkflowStatus = 0) ORDER BY AgilityAsset.LastChangeDate DESC", AssetDatabase, LsDatabase, cutoff.ToString(SQLDateTimeFormat), retroActiveCutoff.ToString(SQLDateTimeFormat));
// eg: cutoff date format : 2017-10-02 16:08:24.507
/* EXECUTE QUERY in base class.. then add INDEX pointers for Asset attributes - these point each attribute to the relevant column in the assetrecord array
* so that the file writer can access these without needing to understand each different query and its result data, which is
* different in each service */
assetRecords = base.ReadData(queryString); // execute overloaded method in base
if (assetRecords.Rows.Count > 0)
{
assetRecords.Index.AssetCode = SQLCode;
assetRecords.Index.AssetOrParStatus = SQLStatusCode;
assetRecords.Index.LastChanged = SQLLastChangeDate;
assetRecords.Index.CoreWorkflowStatus = SQLCoreStatus; // set the CORE workflow status as this needs to be inspected in the Filewriter to implement a workaround for defect 2894 (failing workflowstatus updates)
assetRecords.Index.CoreModifiedDate = SQLCoreModifiedDate; // 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
}
assetRecords.LastModified = cutoff; // timestamp the result recordset based on the requested cutoff
Log.Trace(this.GetType().Name + Environment.NewLine + "\tqueryString=" + queryString + Environment.NewLine + "\tcutoff= " + cutoff.ToString(SQLDateTimeFormat) + Environment.NewLine + "\tretroActiveCutoff= " + retroActiveCutoff.ToString(SQLDateTimeFormat) + Environment.NewLine + "\tassetRecords.LastModified=" + assetRecords.LastModified.ToString(SQLDateTimeFormat) + Environment.NewLine, EventLogger.EventIdEnum.QUERYING_DATA);
} catch (Exception ex) {
Log.Error(ex.Message, EventLogger.EventIdEnum.QUERYING_DATA, ex);
}
return(assetRecords);
}
// 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);
}
/* The data query produce a PAR Data Report, which provides a row with a count for every asset definition across all PAR Rules (i.e. every unique combination of Asset Category, Type, Model,
* & Workflow Status), in every zone, including zones in which the defined assets are present but do not have a PAR Rule defined.
* It produces a row for all asset and zone combinations in every PAR rule configured in CORE.
* The data query will execute if rfid movement was detected during the last refresh period (as defined by MAXMinuteBeforeRefresh constant below)
* or earlier if a PAR status changes since the last query was executed.
* If no PAR statuses have changed in 3 minutes the report will run anyway to refresh asset counts which may have changed due to workflow status changes in Agility.
* If the report executes the results will include all zones and assets, not just the ones which had changed.
* The results are sorted by Asset Category, Asset Type, Asset Model, Zone, & Current Status descending.
*/
internal sealed override AssetDataRecordset ReadData(DateTime cutoff)
{
AssetDataRecordset assetRecords = null;
// refresh period
const int MAXMinuteBeforeRefresh = 3; // if par statuses have not changed in 3 minutes run the report again anyway. this will refresh the counts, which may have changed due to workflowstatus changes
// column positions in query
const int SQLCurStatus = 0, SQLCurQty = 1, SQLParRuleStatus = 2, SQLParRuleQty = 3, SQLParRuleRepQty = 4,
SQLLevel = 5, SQLZoneType = 6, SQLZone = 7,
SQLAssetCategory = 8, SQLAssetType = 9, SQLAssetModel = 10, SQLAssetModelDesc = 11, SQLWorkflowStatuses = 12,
SQLParRule = 13, SQLParRuleDate = 14, SQLCurStatusDate = 15;
try {
/* CONSTRUCT QUERY - gets results only if there were changes since the cutoff. */
string queryString = string.Format("SELECT vwPARRuleCurStatus.CurStatus, vwAssetTagAssetMapCount.CurQty, vwPARRuleCurStatus.ParRuleStatus, vwPARRuleCurStatus.ParRuleQty, vwPARRuleCurStatus.ParRuleRepQty, "
+ "COALESCE(vwAssetTagAssetMapCount.Level, vwPARRuleCurStatus.Level) Level, COALESCE(vwAssetTagAssetMapCount.ZoneType, vwPARRuleCurStatus.ZoneType) ZoneType, COALESCE(vwAssetTagAssetMapCount.Zone, vwPARRuleCurStatus.Zone) Zone, "
+ "COALESCE(vwAssetTagAssetMapCount.AssetCategory, vwPARRuleCurStatus.AssetCategory) AssetCategory, COALESCE(vwAssetTagAssetMapCount.AssetType, vwPARRuleCurStatus.AssetType) AssetType, "
+ "COALESCE(vwAssetTagAssetMapCount.AssetModel, vwPARRuleCurStatus.AssetModel) AssetModel, COALESCE(vwAssetTagAssetMapCount.AssetModelDescription, vwPARRuleCurStatus.AssetModelDescription) AssetModelDescription, "
+ "COALESCE(vwAssetTagAssetMapCount.WorkflowStatuses, vwPARRuleCurStatus.WorkflowStatuses) WorkflowStatuses, vwPARRuleCurStatus.ParRule, vwPARRuleCurStatus.ParRuleDate, vwPARRuleCurStatus.CurStatusDate "
+ "FROM(SELECT SUM(Count) CurQty, vwAssetTag.Level, vwAssetTag.ZoneType, vwAssetTag.Zone, vwAssetTag.AssetCategory, vwAssetTag.AssetType, vwAssetTag.AssetModel, vwAssetTag.AssetModelDescription, vwPARAssetMap.WorkflowStatuses, "
+ "vwAssetTag.ZoneID, vwAssetTag.AssetCategoryID, vwAssetTag.AssetTypeID, vwAssetTag.AssetModelID FROM(SELECT Count(UID) Count, tbLocation.LocationName Level, tbEnum.Description ZoneType, tbSubLocation.SubLocationName Zone, "
+ "tbAssetCategory.AssetCategoryName AssetCategory, tbAssetType.AssetTypeName AssetType, tbAssetModel.ModelName AssetModel, tbAssetModel.ModelDesc AssetModelDescription, tbWorkflowStatus.StatusName WorkflowStatus, "
+ "tbAssetType.AssetCategoryIDF AssetCategoryID, tbAsset.AssetTypeIDF AssetTypeID, tbAsset.AssetModelIDF AssetModelID, tbAsset.WorkflowStatus WorkflowStatusID, tbRFIDBank.RFIDZoneIDF ZoneID FROM[{0}]..[tbAsset] "
+ "INNER JOIN[{0}]..[tbRFIDBank] ON tbAsset.RFIDBankIDF = tbRFIDBank.RFIDBankIDP INNER JOIN[{0}]..[tbAssetModel] ON tbAsset.AssetModelIDF = tbAssetModel.AssetModelIDP AND tbAssetModel.IsDeleted IN (0, NULL) "
+ "INNER JOIN[{0}]..[tbAssetType] ON tbAsset.AssetTypeIDF = tbAssetType.AssetTypeIDP AND tbAssetType.IsDeleted IN (0, NULL) INNER JOIN[{0}]..[tbAssetCategory] ON tbAssetType.AssetCategoryIDF = "
+ "tbAssetCategory.AssetCategoryIDP AND tbAssetCategory.IsDeleted IN (0, NULL) INNER JOIN[{0}]..[tbSubLocation] ON tbRFIDBank.RFIDZoneIDF = tbSubLocation.SubLocationIDP AND tbSubLocation.IsDeleted = 0 "
+ "INNER JOIN [{0}]..[tbEnum] ON tbSubLocation.SublocationType = Value AND tbEnum.EnumEntity = 'SubLocationType' INNER JOIN [{0}]..[tbLocation] ON tbLocation.LocationIDP = tbSubLocation.LocationIDF "
+ "AND tbLocation.IsDeleted = 0 AND tbSubLocation.IsDeleted = 0 INNER JOIN [{0}]..[tbWorkflowStatus] ON tbAsset.WorkflowStatus = tbWorkflowStatus.WorkflowStatusIDP WHERE tbAsset.IsDeleted = 0 AND "
+ "tbRFIDBank.IsDeleted = 0 AND tbAsset.RFIDBankIDF IS NOT NULL AND tbSubLocation.IsDeleted = 0 AND tbSubLocation.SublocationType IN (1,2,3) GROUP BY tbLocation.LocationName, tbEnum.Description, tbRFIDBank.RFIDZoneIDF, "
+ "tbAssetType.AssetCategoryIDF, tbAsset.AssetTypeIDF, tbAsset.AssetModelIDF, tbAsset.WorkflowStatus, tbSubLocation.SubLocationName, tbAssetCategory.AssetCategoryName, tbAssetType.AssetTypeName, "
+ "tbAssetModel.ModelName, tbAssetModel.ModelDesc, tbWorkflowStatus.StatusName) vwAssetTag INNER JOIN(SELECT DISTINCT tbAssetType.AssetCategoryIDF AssetCategoryID, tbAssetType.AssetTypeIDP AssetTypeID, "
+ "tbAssetModel.AssetModelIDP AssetModelID, WfStatuses.WorkflowStatusID, WfStatuses.WorkflowStatuses FROM [{0}]..[tbPARAssetMap] INNER JOIN[{0}]..[tbAssetModel] "
+ "ON tbPARAssetMap.PreferenceType = 1 AND tbPARAssetMap.ReferenceIDF = tbAssetModel.AssetModelIDP INNER JOIN [{0}]..[tbAssetType] ON((tbPARAssetMap.PreferenceType = 2 AND tbPARAssetMap.ReferenceIDF = tbAssetType.AssetTypeIDP) OR "
+ "(tbPARAssetMap.PreferenceType = 1 AND tbAssetModel.AssetTypeIDF = tbAssetType.AssetTypeIDP)) AND tbAssetType.IsDeleted IN (0, NULL) INNER JOIN(SELECT tbPARCountByStatus.ParIDF, tbWorkflowStatus.StatusName WorkflowStatus, "
+ "tbPARCountByStatus.WorkflowStatus WorkflowStatusID, tbWorkflowStatus.StatusName WokflowStatus, WorkflowStatuses = LTRIM(STUFF((SELECT ', ' + StatusName FROM[{0}]..[tbWorkflowStatus] "
+ "INNER JOIN [{0}]..[tbPARCountByStatus] tbPARCountByStatus2 ON tbWorkflowStatus.WorkflowStatusIDP = tbPARCountByStatus2.WorkflowStatus WHERE tbPARCountByStatus.ParIDF = tbPARCountByStatus2.ParIDF AND "
+ "tbWorkflowStatus.IsDeleted IN(0, NULL) ORDER BY StatusName FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')) FROM[{0}]..[tbPARCountByStatus] INNER JOIN[{0}]..[tbWorkflowStatus] "
+ "ON tbWorkflowStatus.WorkflowStatusIDP = tbPARCountByStatus.WorkflowStatus GROUP BY tbPARCountByStatus.ParIDF, tbWorkflowStatus.StatusName, tbPARCountByStatus.WorkflowStatus) WfStatuses ON "
+ "tbPARAssetMap.PARIDF = WfStatuses.ParIDF) vwPARAssetMap ON vwAssetTag.AssetCategoryID = vwPARAssetMap.AssetCategoryID AND vwAssetTag.AssetTypeID = vwPARAssetMap.AssetTypeID AND vwAssetTag.AssetModelID = "
+ "vwPARAssetMap.AssetModelID AND vwAssetTag.WorkflowStatusID = vwPARAssetMap.WorkflowStatusID GROUP BY vwPARAssetMap.WorkflowStatuses, vwAssetTag.Level, vwAssetTag.ZoneType, vwAssetTag.Zone, vwAssetTag.ZoneID, "
+ "vwAssetTag.AssetCategory, vwAssetTag.AssetCategoryID, vwAssetTag.AssetType, vwAssetTag.AssetTypeID, vwAssetTag.AssetModel, vwAssetTag.AssetModelDescription, vwAssetTag.AssetModelID) vwAssetTagAssetMapCount "
+ "FULL JOIN(SELECT DISTINCT vwZone.Level, vwZone.ZoneType, vwZone.Zone, vwPARCurStatus.AssetCategory, vwPARCurStatus.AssetType, vwPARCurStatus.AssetModel, vwPARCurStatus.AssetModelDescription, "
+ "vwWorkflowStatuses.WorkflowStatuses, vwPARCurStatus.CurStatus, vwPARCurStatus.CurQty, vwPARCurStatus.CurStatusDate, vwPARRule.ParRule, vwPARRule.ParRuleStatus, vwPARRule.ParRuleQty, vwPARRule.ParRuleRepQty, "
+ "vwPARRule.ParRuleDate, vwPARCurStatus.ZoneID, vwPARCurStatus.AssetCategoryID, vwPARCurStatus.AssetTypeID, vwPARCurStatus.AssetModelID FROM (SELECT tbPARCurrentStatus.PARIDF CurStatusPARID, "
+ "tbPARCurrentStatus.SublocationIDF ZoneID, tbAssetCategory.AssetCategoryName AssetCategory, tbAssetType.AssetTypeName AssetType, tbAssetModel.ModelName AssetModel, tbAssetModel.ModelDesc AssetModelDescription, "
+ "tbAssetType.AssetCategoryIDF AssetCategoryID, tbAssetType.AssetTypeIDP AssetTypeID, tbAssetModel.AssetModelIDP AssetModelID, tbEnum2.Description CurStatus, tbPARCurrentStatus.CurPARQty CurQty, "
+ "tbPARCurrentStatus.StatusModifiedDate CurStatusDate FROM[{0}]..[tbPARCurrentStatus] LEFT JOIN[{0}]..[tbEnum] tbEnum2 ON tbPARCurrentStatus.PARStatus = tbEnum2.Value AND tbEnum2.EnumEntity = 'EnumPARStatus' "
+ "LEFT JOIN[{0}]..[tbAssetModel] ON tbPARCurrentStatus.PreferenceType = 1 AND tbPARCurrentStatus.ReferenceIDF = tbAssetModel.AssetModelIDP INNER JOIN [{0}]..[tbAssetType] ON((tbPARCurrentStatus.PreferenceType = 2 "
+ "AND tbPARCurrentStatus.ReferenceIDF = tbAssetType.AssetTypeIDP) OR(tbPARCurrentStatus.PreferenceType = 1 AND tbAssetModel.AssetTypeIDF = tbAssetType.AssetTypeIDP)) AND tbAssetType.IsDeleted IN (0, NULL) "
+ "INNER JOIN[{0}]..[tbAssetCategory] ON tbAssetType.AssetCategoryIDF = tbAssetCategory.AssetCategoryIDP AND tbAssetCategory.IsDeleted IN (0, NULL) WHERE tbPARCurrentStatus.IsDeleted = 0) vwPARCurStatus "
+ "LEFT JOIN(SELECT tbPAR.PARIDP RulePARID, tbPAR.PARName ParRule, tbEnum.Description ParRuleStatus, tbPAR.Quantity ParRuleQty, tbPAR.RepQuantity ParRuleRepQty, tbPAR.ModifiedDate ParRuleDate FROM[{0}]..[tbPAR] "
+ "LEFT JOIN[{0}]..[tbEnum] ON tbPAR.PARStatus = tbEnum.Value AND tbEnum.EnumEntity = 'EnumPARStatus') vwPARRule ON(vwPARCurStatus.CurStatusPARID = vwPARRule.RulePARID) LEFT JOIN(SELECT tbSubLocation.SubLocationIDP "
+ "ZoneID, tbLocation.LocationName Level, tbSubLocation.SubLocationName Zone, tbEnum.Description ZoneType FROM[{0}]..[tbSubLocation] INNER JOIN[{0}]..[tbEnum] "
+ "ON tbSubLocation.SublocationType = Value AND tbEnum.EnumEntity = 'SubLocationType' INNER JOIN [{0}]..[tbLocation] ON tbLocation.LocationIDP = tbSubLocation.LocationIDF AND tbLocation.IsDeleted = 0 AND tbSubLocation.IsDeleted = 0) vwZone "
+ "ON vwPARCurStatus.ZoneID = vwZone.ZoneID INNER JOIN(SELECT tbPARCountByStatus.ParIDF, tbWorkflowStatus.StatusName WorkflowStatus, tbPARCountByStatus.WorkflowStatus WorkflowStatusID, WorkflowStatuses = "
+ "LTRIM(STUFF((SELECT ', ' + StatusName FROM[{0}]..[tbWorkflowStatus] INNER JOIN[{0}]..[tbPARCountByStatus] tbPARCountByStatus2 ON tbWorkflowStatus.WorkflowStatusIDP = tbPARCountByStatus2.WorkflowStatus "
+ "WHERE tbPARCountByStatus.ParIDF = tbPARCountByStatus2.ParIDF AND tbWorkflowStatus.IsDeleted IN(0, NULL) ORDER BY StatusName FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')) FROM[{0}]..[tbPARCountByStatus] "
+ "INNER JOIN[{0}]..[tbWorkflowStatus] ON tbWorkflowStatus.WorkflowStatusIDP = tbPARCountByStatus.WorkflowStatus GROUP BY tbPARCountByStatus.ParIDF, tbWorkflowStatus.StatusName, tbPARCountByStatus.WorkflowStatus "
+ ") vwWorkflowStatuses ON vwPARCurStatus.CurStatusPARID = vwWorkflowStatuses.ParIDF) vwPARRuleCurStatus ON(vwAssetTagAssetMapCount.ZoneID = vwPARRuleCurStatus.ZoneID AND vwAssetTagAssetMapCount.AssetCategoryID = "
+ "vwPARRuleCurStatus.AssetCategoryID AND vwAssetTagAssetMapCount.AssetTypeID = vwPARRuleCurStatus.AssetTypeID AND vwAssetTagAssetMapCount.AssetModelID = vwPARRuleCurStatus.AssetModelID AND "
+ "vwAssetTagAssetMapCount.WorkflowStatuses = vwPARRuleCurStatus.WorkflowStatuses) ORDER BY COALESCE(vwAssetTagAssetMapCount.Level, vwPARRuleCurStatus.Level), COALESCE(vwAssetTagAssetMapCount.ZoneType, "
+ "vwPARRuleCurStatus.ZoneType), COALESCE(vwAssetTagAssetMapCount.Zone, vwPARRuleCurStatus.Zone), COALESCE(vwAssetTagAssetMapCount.AssetCategory, vwPARRuleCurStatus.AssetCategory), "
+ "COALESCE(vwAssetTagAssetMapCount.AssetType, vwPARRuleCurStatus.AssetType), COALESCE(vwAssetTagAssetMapCount.AssetModel, vwPARRuleCurStatus.AssetModel), "
+ "COALESCE(vwAssetTagAssetMapCount.WorkflowStatuses, vwPARRuleCurStatus.WorkflowStatuses)", LsDatabase, cutoff.ToString(SQLDateTimeFormat), MAXMinuteBeforeRefresh.ToString());
// eg: cutoff date format : 2017-10-02 16:08:24.507
/* EXECUTE QUERY in base class.. then add INDEX pointers for Asset data attributes - these point each attribute to the relevant column in the assetrecord array
* so that the file writer can access these without needing to understand each different query and its result data, which is
* different in each service */
assetRecords = base.ReadData(queryString); // execute overloaded method in base
if (assetRecords.Rows.Count > 0)
{
assetRecords.Index.AssetOrParStatus = SQLCurStatus;
assetRecords.Index.AssetQuantity = SQLCurQty;
assetRecords.Index.ParRuleStatus = SQLParRuleStatus;
assetRecords.Index.ParRuleQty = SQLParRuleQty;
assetRecords.Index.ParRuleRepQty = SQLParRuleRepQty;
assetRecords.Index.Level = SQLLevel;
assetRecords.Index.AssetSublocationOrZone = SQLZone;
assetRecords.Index.ZoneType = SQLZoneType;
assetRecords.Index.AssetCategory = SQLAssetCategory;
assetRecords.Index.AssetType = SQLAssetType;
assetRecords.Index.AssetModel = SQLAssetModel;
assetRecords.Index.AssetModelDescription = SQLAssetModelDesc;
assetRecords.Index.WorkflowStatus = SQLWorkflowStatuses;
assetRecords.Index.ParRule = SQLParRule;
assetRecords.Index.CoreModifiedDate = SQLParRuleDate;
assetRecords.Index.LastChanged = SQLCurStatusDate;
}
assetRecords.LastModified = cutoff; // timestamp the result recordset based on the requested cutoff
Log.Trace(this.GetType().Name + Environment.NewLine + "\tqueryString=" + queryString + Environment.NewLine + "\tcutoff= " + cutoff.ToString(SQLDateTimeFormat) + Environment.NewLine + "\tassetRecords.LastModified=" + assetRecords.LastModified.ToString(SQLDateTimeFormat) + Environment.NewLine, EventLogger.EventIdEnum.QUERYING_DATA);
// NOT USED - read supplementary data into a second recordset - NOT USED
// assetRecords.SupplementaryData = ReadSupplementaryData(cutoff);
} catch (Exception ex) {
Log.Error(ex.Message, EventLogger.EventIdEnum.QUERYING_DATA, ex);
}
return(assetRecords);
}
/* 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);
}
/* retrieves assets for the period after the cutoff date until now.
* TRACKABLE ASSETS - To be trackable an asset must be flagged for RFID tagging. To start CORE provisioning the asset code must be entered into
* the RFID tag field in agility.
* To retag an asset (i.e to print a new tag) delete its RFID tag in CORE and 'change' the record in agility (i.e. update last changed date by opening
* the asset and selecting 'change' and 'ok'). This will cause the asset to appear in the next asset provisionng data extract from Agility.
* The adminsitrator can then reprint the tag and import the record into CORE (with the 'modify' option selected).
* To deprovision an asset the RFID tag field must be cleared and the corresponding asset and tag must be manually deleted in CORE
* Only assets with a category (HDWA_Category) of 'Facilities Management' or 'MTMU' are imported into CORE. If other categories are required the
* SQL query below must be modified to include the categeory and its agility field mappings for asset category, type, and model; as these are
* different for each asset category in agility data loads, and therefore require custom handling.*/
internal sealed override AssetDataRecordset ReadData(DateTime cutoff)
{
AssetDataRecordset assetRecords = null;
// column positions in query - if the query is modified these need to be updated as well
const int SQLCode = 0, SQLLastChangeDate = 1, SQLCategory = 2, SQLType = 3;
const int SQLModel = 4, SQLAssetName = 5, SQLRfidTag = 6, SQLDepartment = 7, SQLSerialNumber = 8, SQLSublocation = 9;
const int SQLCoreUID = 10, SQLCoreRFID = 11, SQLCoreModifiedDate = 12, SQLCoreCreatedDate = 13, SQLCoreLevel = 14, SQLCoreZone = 15;
// execute query
try {
/*
* LSAPD QUERY - the extracted data file will include agility asset data for all of the following
* 1. assets flagged for tracking in agility after the cutoff time (these do not exist in CORE)
* 2. assets which exist in CORE without an RFID tag (these were unlinked for retagging or reprovisioning)
* 3. assets which which were provisioned (created) in CORE after the cutoff time.
*
* the query will be sorted by agility last modified or core created date
* when the data file is created later it will show 'New' in the __COREImportMethod column for assets which do not exist in core, and the LocationName and ZoneNamecolumn will
* display default zone for new assets, which is 'LB' and 'LBS02 [ICT Storeroom]' respectively.
*
* the __COREImportMethod column will be
* 'Modify (Retag)' if the asset exists in CORE and there is no linked RFID tag;
* 'Modify (Reprovision)' if the asset exists in CORE and the asset and tag are linked.
* 'New' if the asset has been flagged in Agility and does not exist in CORE
*
* if the __COREImportMethod is 'Modify (Reprovision)' the LocationName and ZoneNamecolumn will cotnain the current Zone of the asset in CORE. This is to ensure that
* the CORE asset location is preserved correctly when the data file is imported into CORE (when the rest of the asset data requires update due to Agility changes,
* but not location)
*
* if the __COREImportMethod is 'Modify (Retag)' or 'New' the LocationName and ZoneNamecolumn will be set to a default zone for new tags (an ICT Room in LB).
* The data file should not be imported into CORE if the __COREImportMethod is 'Modify (Retag)' as this will overwrite the current zone location of the asset
* with a default location. The retag option is typically needed only for reprinting new tags.
*
* if the __COREImportMethod is 'New' or 'Modify (Reprovision)' importing the file will refresh CORE asset data and create a new tag and/or link the tag and asset.
*
* As 'Modify (Reprovision)' import will overwrite the zone and location from the import file, it is important that the import is executed soon (if not immmediately)
* after the data is extracted. It is recommended that a new data file is generated for provisioning just before the administrator is ready to execute the import.
* The instructions for this process are described in the TWI for asset provisioning.
*/
string queryString = string.Format("IF EXISTS(SELECT AgilityAsset.Code, COREAsset.ModifiedDate, COREAsset.RFIDBankIDF FROM {0}..pmAsset AgilityAsset LEFT JOIN(SELECT tbAsset.UID, "
+ "tbAsset.ModifiedDate, tbAsset.CreatedDate, tbAsset.RFIDBankIDF FROM {1}..tbAsset WHERE tbAsset.IsDeleted = 0) COREAsset ON AgilityAsset.Code = COREAsset.UID "
+ "WHERE (COREAsset.UID IS NULL AND AgilityAsset.Code = AgilityAsset.HDWA_RfidTag AND AgilityAsset.LastChangeDate > '{2}') "
+ "OR (COREAsset.UID IS NOT NULL AND COREAsset.RFIDBankIDF IS NULL AND COREAsset.ModifiedDate > '{2}') OR (COREAsset.UID IS NOT NULL AND COREAsset.CreatedDate > '{2}') "
+ ") SELECT AgilityAsset.Code, AgilityAsset.LastChangeDate, AssetHierarchy.AssetCategory, AssetHierarchy.AssetType, AssetHierarchy.AssetModel, "
+ "COALESCE(AgilityAsset.HDWA_AssetName, COREAssetName) AssetName, AgilityAsset.HDWA_RfidTag, AgilityAsset.HDWA_PrimaryUser, AgilityAsset.SerialNumber, pmSublocation.SublocationCode, "
+ "COREAsset.UID COREUID, COREAsset.RFIDTagID CORERFID, COREAsset.ModifiedDate COREModifiedDate, COREAsset.CreatedDate CORECreatedDate, "
+ "COREAsset.CORELevel, COREAsset.COREZone, COREAssetName FROM {0}..pmAsset AgilityAsset LEFT JOIN(SELECT pmAsset.Code, pmAsset.HDWA_Category AssetCategory, "
+ "MTMUAssetType.ItemDesc AssetType, pmAsset.ModelNumber AssetModel FROM {0}..pmAsset INNER JOIN(SELECT DictItem.ItemCode, DictItem.ItemDesc "
+ "FROM {0}..Dictionary INNER JOIN {0}..DictItem ON Dictionary.DictionaryID = DictItem.DictionaryID WHERE Dictionary.DictCode = 'HDWA_ModelType' "
+ ") MTMUAssetType ON pmAsset.HDWA_ModelType = MTMUAssetType.ItemCode WHERE pmAsset.HDWA_Category = 'MTMU' UNION SELECT pmAsset.Code, pmAsset.HDWA_Category AssetCategory, "
+ "pmAsset.HDWA_Keyword AssetType, pmAsset.Type AssetModel FROM {0}..pmAsset WHERE pmAsset.HDWA_Category = 'Facilities Management' AND pmAsset.HDWA_NamePlate <> 'LOC' "
+ ") AssetHierarchy ON AgilityAsset.Code = AssetHierarchy.Code LEFT JOIN {0}..pmSublocation ON AgilityAsset.Sublocation = pmSublocation.SublocationID "
+ "LEFT JOIN(SELECT tbAsset.UID, tbRFIDBank.RFIDTagID, tbAsset.RFIDBankIDF, tbAsset.ModifiedDate, tbAsset.CreatedDate, tbAsset.Field1 COREAssetName, "
+ "tbLocation.LocationName CORELevel, tbSubLocation.SubLocationName COREZone FROM {1}..tbAsset LEFT JOIN {1}..tbRFIDBank ON RFIDBankIDF = RFIDBankIDP "
+ "LEFT JOIN {1}..tbSubLocation ON tbRFIDBank.RFIDZoneIDF = tbSubLocation.SubLocationIDP AND tbSubLocation.IsDeleted = 0 LEFT JOIN {1}..tbLocation "
+ "ON tbLocation.LocationIDP = tbSubLocation.LocationIDF AND tbLocation.IsDeleted = 0 AND tbSubLocation.IsDeleted = 0 WHERE tbAsset.IsDeleted = 0) COREAsset "
+ "ON AgilityAsset.Code = COREAsset.UID WHERE(COREAsset.UID IS NULL AND AgilityAsset.Code = AgilityAsset.HDWA_RfidTag AND AgilityAsset.LastChangeDate > '{2}') "
+ "OR (COREAsset.UID IS NOT NULL AND COREAsset.RFIDBankIDF IS NULL AND COREAsset.ModifiedDate > '{2}') OR (COREAsset.UID IS NOT NULL AND COREAsset.CreatedDate > '{2}') "
+ "ORDER BY AgilityAsset.LastChangeDate DESC, COREAsset.ModifiedDate DESC", AssetDatabase, LsDatabase, cutoff.ToString(SQLDateTimeFormat)); // date format eg: 2017-10-02 16:08:24.507
/* EXECUTE QUERY in base class.. then add INDEX pointers for Asset attributes - these point each attribute to the relevant column in the assetrecord array
* so that the file writer can access these without needing to understand each different query and its result data, which is
* different in each service */
assetRecords = base.ReadData(queryString); // execute overloaded method in base
if (assetRecords.Rows.Count > 0)
{
assetRecords.Index.AssetCode = SQLCode;
assetRecords.Index.LastChanged = SQLLastChangeDate; // Agility timestamp
assetRecords.Index.AssetCategory = SQLCategory; // HDWA_Category
assetRecords.Index.AssetType = SQLType; // MTMU Type - HDWA_ModelType
assetRecords.Index.AssetModel = SQLModel; // MTMU Model - ModelNumber
assetRecords.Index.AssetName = SQLAssetName;
assetRecords.Index.RfidTagId = SQLRfidTag;
assetRecords.Index.Department = SQLDepartment;
assetRecords.Index.SerialNumber = SQLSerialNumber;
assetRecords.Index.AssetSublocationOrZone = SQLSublocation;
assetRecords.Index.CoreUID = SQLCoreUID;
assetRecords.Index.CoreRFID = SQLCoreRFID;
assetRecords.Index.CoreModifiedDate = SQLCoreModifiedDate; // CORE last modified timestamp
assetRecords.Index.CoreCreatedDate = SQLCoreCreatedDate; // CORE provisioning timestamp
assetRecords.Index.CoreSublocationOrZone = SQLCoreZone; // CORE zone for reprovisioning
assetRecords.Index.CoreLevel = SQLCoreLevel; // CORE level for reprovisioning
}
assetRecords.LastModified = cutoff; // timestamp the result recordset based on the requested cutoff
Log.Trace(this.GetType().Name + Environment.NewLine + "\tqueryString=" + queryString + Environment.NewLine + "\tcutoff= " + cutoff.ToString(SQLDateTimeFormat) + Environment.NewLine + "\tassetRecords.LastModified=" + assetRecords.LastModified.ToString(SQLDateTimeFormat) + Environment.NewLine, EventLogger.EventIdEnum.QUERYING_DATA);
} catch (Exception ex) {
Log.Error(ex.Message, EventLogger.EventIdEnum.QUERYING_DATA, ex);
}
return(assetRecords);
}
