Beispiel #1
0
        public bool PI_PushAllRecords(long nRecordLimit)
        {
            if (nRecordLimit < 0)
            {
                nRecordLimit = long.MaxValue;
            }
            var success = false;
            var connStr = $"Data Source={dataSource};Catalog={catalog}";

            var conn = new AdomdConnection(connStr);

            try
            {
                AdomdDataReader reader;
                conn.Open();
                engine.OutputMessage(toolId, AlteryxRecordInfoNet.MessageStatus.STATUS_Info, "Connected to server, processing the query...");
                engine.OutputToolProgress(toolId, 0.05);
                output.UpdateProgress(0.05);

                var command = new AdomdCommand(mdx, conn);
                if (nRecordLimit == 0)
                {
                    reader = command.ExecuteReader(CommandBehavior.SchemaOnly);
                }
                else
                {
                    reader = command.ExecuteReader();
                }

                engine.OutputMessage(toolId, AlteryxRecordInfoNet.MessageStatus.STATUS_Info, "Query finished executing, retrieving schema...");
                engine.OutputToolProgress(toolId, 0.50);
                output.UpdateProgress(0.50);

                var fields     = reader.GetSchemaTable();
                var outputInfo = new AlteryxRecordInfoNet.RecordInfo();

                foreach (DataRow field in fields.Rows)
                {
                    var fieldName = field[0].ToString();

                    outputInfo.AddField(fieldName, AlteryxRecordInfoNet.FieldType.E_FT_V_WString, 1073741823, 0, $"SSAS Input ({toolId})", "");
                }

                output.Init(outputInfo, "Output", null, config);
                engine.OutputMessage(toolId, AlteryxRecordInfoNet.MessageStatus.STATUS_Info, "Prepared output schema, retrieving data...");
                engine.OutputToolProgress(toolId, 0.55);
                output.UpdateProgress(0.55);

                var record    = outputInfo.CreateRecord();
                int totalRead = 0;

                while (totalRead < nRecordLimit && reader.Read())
                {
                    record.Reset();
                    for (int i = 0; i < outputInfo.NumFields(); i++)
                    {
                        AlteryxRecordInfoNet.FieldBase fieldBase = outputInfo[i];

                        if (reader[i] == null)
                        {
                            fieldBase.SetNull(record);
                        }
                        else
                        {
                            fieldBase.SetFromString(record, reader[i].ToString());
                        }
                    }
                    output.PushRecord(record.GetRecord());

                    totalRead++;
                }

                reader.Close();
                reader.Dispose();

                engine.OutputMessage(toolId, AlteryxRecordInfoNet.MessageStatus.STATUS_Complete, $"Data retrieval completed.  {totalRead} records output.");
                engine.OutputToolProgress(toolId, 1.00);
                output.UpdateProgress(1.00);
                success = true;
            }
            catch (Exception ex)
            {
                engine.OutputMessage(toolId, AlteryxRecordInfoNet.MessageStatus.STATUS_Error, $"Error: {ex.Message}  Stack trace: {ex.StackTrace}");
                success = false;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }

            output.Close();
            return(success);
        }
        // Called by Alteryx for an Input tool to request all of the records.
        public bool PI_PushAllRecords(long nRecordLimit)
        {
            // The nRecordLimit parameter specifies the maximum number of records that
            // should be provided, or -1 for unlimited.  If it is -1, set it to
            // long.MaxValue to make the processing easier later on.  Sometimes Alteryx
            // will call this function with a record limit of 0 just to get the output
            // record configuration.
            if (nRecordLimit < 0)
            {
                nRecordLimit = long.MaxValue;
            }

            m_engineInterface.OutputMessage(m_nToolId, AlteryxRecordInfoNet.MessageStatus.STATUS_Info, nRecordLimit.ToString());

            if (m_engineInterface.GetInitVar("UpdateOnly") == "True")
            {
                m_engineInterface.OutputMessage(m_nToolId, AlteryxRecordInfoNet.MessageStatus.STATUS_Complete, nRecordLimit.ToString());
            }
            else
            {
                // Get the configuration section out of the properties xml that was passed
                // into PI_Init() and use it to determine the xml file, container element,
                // and field configuration for our tool.

                XmlElement            configXml = m_xmlProperties.SelectSingleNode("Configuration") as XmlElement;
                XmlInputConfiguration xmlConfig = XmlInputConfiguration.LoadFromConfiguration(configXml);

                if (xmlConfig == null)
                {
                    throw new ApplicationException("Invalid configuration.  Ensure that the input file is set correctly.");
                }

                if (xmlConfig.Fields.Count == 0)
                {
                    throw new ApplicationException("There are no fields.  Make sure your container element is set properly.");
                }

                // Create a new RecordInfo object to describe our outgoing message records and
                // configure it based on the field information in our saved configuration.
                AlteryxRecordInfoNet.RecordInfo recordInfoOut = new AlteryxRecordInfoNet.RecordInfo();
                foreach (XmlInputField field in xmlConfig.Fields)
                {
                    // For each field in our configuration, add it to our RecordInfo object.
                    recordInfoOut.AddField(field.Name, field.FieldType, field.Size, field.Scale, "", "");
                }

                // Use the new RecordInfo object to initialize the PluginOutputConnectionHelper.
                // The PluginOutputConnectionHelper can't be used until this step is performed.
                m_outputHelper.Init(recordInfoOut, "Message Output", null, m_xmlProperties);

                // Create a Record object to hold the data for each outgoing message record.
                AlteryxRecordInfoNet.Record recordOut = recordInfoOut.CreateRecord();

                // Create a new RecordInfo object to describe our outgoing attachment records and
                // configure it based on the field information in our saved configuration.
                AlteryxRecordInfoNet.RecordInfo recordInfoOut_AttachmentPaths = new AlteryxRecordInfoNet.RecordInfo();

                // For each field in our configuration, add it to our RecordInfo object.
                recordInfoOut_AttachmentPaths.AddField("Id", AlteryxRecordInfoNet.FieldType.E_FT_String, 4000, 0, "", "");
                recordInfoOut_AttachmentPaths.AddField("AttachmentPath", AlteryxRecordInfoNet.FieldType.E_FT_String, 4000, 0, "", "");

                // Use the new RecordInfo object to initialize the PluginOutputConnectionHelper.
                // The PluginOutputConnectionHelper can't be used until this step is performed.
                m_attachmentOutputHelper.Init(recordInfoOut_AttachmentPaths, "Attachment Output", null, m_xmlProperties);

                // Create a Record object to hold the data for each outgoing attachment record.
                AlteryxRecordInfoNet.Record recordOut_AttachmentPaths = recordInfoOut_AttachmentPaths.CreateRecord();

                // Define the necessary PropertyDefinitionBase objects for each field in the XML configuration document.
                PropertyDefinitionBase[] propertyDefinitionBase = new PropertyDefinitionBase[xmlConfig.Fields.Count];

                for (int i = 0; i < xmlConfig.Fields.Count; i++)
                {
                    if ((WellKnownFolderName)xmlConfig.Folder == WellKnownFolderName.Calendar && typeof(AppointmentSchema).GetField(xmlConfig.Fields[i].Name) != null)
                    {
                        propertyDefinitionBase[i] = (PropertyDefinitionBase)typeof(AppointmentSchema).GetField(xmlConfig.Fields[i].Name).GetValue(null);
                    }
                    else if ((WellKnownFolderName)xmlConfig.Folder == WellKnownFolderName.Inbox && typeof(EmailMessageSchema).GetField(xmlConfig.Fields[i].Name) != null)
                    {
                        propertyDefinitionBase[i] = (PropertyDefinitionBase)typeof(EmailMessageSchema).GetField(xmlConfig.Fields[i].Name).GetValue(null);
                    }
                    else if ((WellKnownFolderName)xmlConfig.Folder == WellKnownFolderName.Tasks && typeof(TaskSchema).GetField(xmlConfig.Fields[i].Name) != null)
                    {
                        propertyDefinitionBase[i] = (PropertyDefinitionBase)typeof(TaskSchema).GetField(xmlConfig.Fields[i].Name).GetValue(null);
                    }
                    else
                    {
                        propertyDefinitionBase[i] = (PropertyDefinitionBase)typeof(ItemSchema).GetField(xmlConfig.Fields[i].Name).GetValue(null);
                    }
                }

                // Assign the configuration settings and field list to the OutlookEmail object.
                OutlookEmail email = new OutlookEmail()
                {
                    UserName = xmlConfig.UserName, Password = xmlConfig.Password, ExchangeServerVersion = xmlConfig.ExchangeVersion, UseManualServiceURL = xmlConfig.UseManualServiceURL, ServiceURL = xmlConfig.ServiceURL, UseDifferentMailbox = xmlConfig.UseDifferentMailbox, Mailbox = xmlConfig.Mailbox, Folder = (WellKnownFolderName)xmlConfig.Folder, IncludeRecurringEvents = xmlConfig.IncludeRecurringEvents, StartDate = xmlConfig.StartDate, EndDate = xmlConfig.EndDate, AttachmentPath = xmlConfig.AttachmentPath, QueryString = xmlConfig.QueryString, IncludeSubFolders = xmlConfig.IncludeSubFolders, SubFolderName = xmlConfig.SubFolderName, SkipRootFolder = xmlConfig.SkipRootFolder, UseUniqueFileName = xmlConfig.UseUniqueFileName, AttachmentFilter = xmlConfig.AttachmentFilter
                };
                email.Fields = new PropertySet(propertyDefinitionBase);

                // Get the list of items (this includes attachments if the Attachment field was selected for output).
                List <OItem> oItems = email.GetItems(nRecordLimit);

                // We will need to send status updates to Alteryx at regular intervals during
                // this process, so we'll do that based on an elapsed time.
                DateTime last = DateTime.Now;

                // We need to keep track of how many records we have processed.
                long nRecords = 0;

                // Process the data in each message object.
                foreach (var oItem in oItems)
                {
                    // If we've exceeded the record limit, stop processing.
                    if (nRecords >= nRecordLimit)
                    {
                        break;
                    }

                    // Reset our output record so we can reuse it.  This is better than
                    // creating a new Record in each iteration as these objects can get large.
                    recordOut.Reset();

                    // For each field, load the data from the corresponding element into the Record.
                    foreach (XmlInputField field in xmlConfig.Fields)
                    {
                        // Get the FieldBase from the RecordInfo for the field.
                        AlteryxRecordInfoNet.FieldBase fieldBase = recordInfoOut.GetFieldByName(field.Name, false);
                        if (fieldBase != null)
                        {
                            // Find the element within the container element that has the same name as the field.
                            var value = Convert.ToString(oItem.Item.GetType().GetProperty(field.Name).GetValue(oItem.Item, null));

                            if (value == null)
                            {
                                // If the field element doesn't exist, set the output field's value to null.
                                fieldBase.SetNull(recordOut);
                            }
                            else
                            {
                                // Otherwise, set the output field's value based on the element's inner text.
                                fieldBase.SetFromString(recordOut, value);
                            }
                        }
                    }

                    // Return message attachments if applicable.
                    if (!string.IsNullOrWhiteSpace(xmlConfig.AttachmentPath))
                    {
                        foreach (var attachment in oItem.Attachments)
                        {
                            // Reset our output record so we can reuse it.  This is better than
                            // creating a new Record in each iteration as these objects can get large.
                            recordOut_AttachmentPaths.Reset();

                            // Get the FieldBase from the RecordInfo for the ID field.
                            AlteryxRecordInfoNet.FieldBase fieldBase_ID = recordInfoOut_AttachmentPaths.GetFieldByName("ID", false);
                            if (fieldBase_ID != null)
                            {
                                fieldBase_ID.SetFromString(recordOut_AttachmentPaths, oItem.Item.Id.ToString());
                            }

                            // Get the FieldBase from the RecordInfo for the AttachmentPath field.
                            AlteryxRecordInfoNet.FieldBase fieldBase_AttachmentPath = recordInfoOut_AttachmentPaths.GetFieldByName("AttachmentPath", false);
                            if (fieldBase_AttachmentPath != null)
                            {
                                fieldBase_AttachmentPath.SetFromString(recordOut_AttachmentPaths, attachment.AttachmentPath);
                            }

                            // Send the record to the downstream tools through the PluginOutputConnectionHelper.
                            m_attachmentOutputHelper.PushRecord(recordOut_AttachmentPaths.GetRecord());
                        }
                    }

                    // Send the record to the downstream tools through the PluginOutputConnectionHelper.
                    m_outputHelper.PushRecord(recordOut.GetRecord());

                    // If at least 1 second has passed since we started or our last update, update progress.
                    if (DateTime.Now.Subtract(last).TotalSeconds >= 1)
                    {
                        // Determine the percent complete:  (Records Processed) / Min(RecordLimit, # of Container Elements)
                        double percentComplete = (double)nRecords / Math.Min(nRecordLimit, oItems.Count);

                        // Output the progress
                        if (m_engineInterface.OutputToolProgress(m_nToolId, percentComplete) != 0)
                        {
                            // If this returns anything but 0, then the user has canceled the operation.
                            throw new AlteryxRecordInfoNet.UserCanceledException();
                        }

                        // Have the PluginOutputConnectionHelper ask the downstream tools to update their progress.
                        m_outputHelper.UpdateProgress(percentComplete);

                        // Reset the timer.
                        last = DateTime.Now;
                    }

                    // Have the PluginOutputConnectionHelper update the record count display in Alteryx.
                    // The PluginOutputConnectionHelper will actually only do this if enough time and data has elapsed,
                    // so it's ok to call this in every iteration.
                    m_outputHelper.OutputRecordCount(false);

                    // Update our record count.
                    nRecords++;
                }

                // If we weren't just getting the output config, send a status message to Alterys that
                // will display the number of records that we output.
                if (nRecordLimit > 0)
                {
                    m_engineInterface.OutputMessage(m_nToolId, AlteryxRecordInfoNet.MessageStatus.STATUS_Info, nRecords.ToString() + " records read from " + xmlConfig.UserName);
                }


                // Tell Alteryx that we are done sending data so that it can close the connections to our plugin.
                m_engineInterface.OutputMessage(m_nToolId, AlteryxRecordInfoNet.MessageStatus.STATUS_Complete, "");
            }

            // Close our ouput connections.
            m_outputHelper.Close();
            m_attachmentOutputHelper.Close();

            // Return true to indicate that we successfully processed our data.
            return(true);
        }