コード例 #1
0
        // 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);
        }
コード例 #2
0
        /* 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);
        }
コード例 #3
0
        /* 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);
        }