Extract System Screens

Objective / Overview

An extract is simply a list of partner keys that was created by using certain parameters (selection criteria). In addition each partner record in the extract also contains a link to a location record as partners can have more than one address (location) records.

Extracts and reports in Petra have some similarities. They both are created by using certain selection criteria. While an extract does not immediately have a visual output (but only a list of partner keys) a report will be presented to the user in visual format, either as printout or in Excel format or other formats. However, for some extracts it would also be beneficial to use the selection criteria not only for creating an extract but straight away to produce a report. Extracts are also often used as input for reports so a report can visualize data that exists in the database for the partner records.

Due to similarities and benefits for the user the decision has been taken to combine the efforts of reports and extracts into the same user interface style. Extracts will be simpler to produce than reports as they don't have to deal with the effort of formatting data and selecting columns for display. However, with this approach it is possible to provide the user with a user interface where - at least for some screens - it will be possible to use the same selection criteria to produce either an extract or a report.

As an example take the publication report/extract. The user will have the possibility to either create an extract of partners or a report for people having subscribed to a certain publication. This can be limited to people living in certain locations if needed or other helpful criteria.

Desired Outcome

Adding extract screens, initially from Phase 1 and in the course of doing that

• learning about our screen definition files (YAML files)
• being able to modularize screen parts where appropriate (e.g. address data entry)
• getting to know the openPETRA database structure documentation
• querying data from the database with the extract criteria as input to produce the desired list of partner records (depending on extract this will involve creating SQL queries but also processing of returned data to apply additional filters)
• learning about the client-server-programming and the project structure
• storing query data for extracts so they can be re-run in the future with the same criteria

Recommended Reading

• Reading about the initial prototype idea (although this has now developed further so just take this as initial input): Extracts generated from Reports
• Important for referencing during development will be the database structure. This is available in either a more visual format produced through SchemaSpy or hyperlinked from HTML files (http://dbdoc.openpetra.org/)
• Probably not needed but in case somebody comes across report creation, here is how the reports are defined: Report Designer

Tasks for creating a new extract

• Create User Interface on client side
  • Create a yaml file by copying from a similar example. Proper syntax of yaml files can be checked by using the developer tool. Once the solution is built from a syntactically correct yaml file there will be some generated files available that are created from the definition described in the yaml file. They end with "...generated.cs" and "...generated.Designer.cs". The generated files can't be changed as they are re-created when the solution is built.
  • For any manual code to intervene with behaviour of the designed screen it is necessary to add a file that ends in "...ManualCode.cs". Have a look at other examples to see how those files are structured.
  • All client files need to go into the reporting directory of the module they belong to as for some of them we may add reporting capability later on. So for example files in the Partner module where most of the extracts will be located need to go into csharp\ICT\Petra\Client\MReporting\Gui\MPartner

• Implement server side code
  • sql file: each extract will have a ".sql" file connected with it. Those files can all be found in "csharp\ICT\Petra\Server\sql". Naming convention here is to start the file name with the module name and then a reference to what it is related to. For example "Partner.Queries.ExtractPartnerByCity.sql". It is best to use a naming convention that makes it easy to relate it to the client side files.
  • cs file to call sql file and process result: Those files can be found in "csharp\ICT\Petra\Server\lib\MPartner\queries" (as an example for a file to be used in the partner module). The name should make it clear that it belongs to the sql file and the client side files. The name of this file and its location is set in the yaml file on client side: e.g. CALCULATEEXTRACTFROMMETHOD: Ict.Petra.Server.MPartner.queries.QueryPartnerByCity.CalculateExtract (this already includes the actual method that is called in the server side class).
  • The cs file needs to call the sql query and then - depending on the complexity of the extract - needs to process the returned partner records to possibly filter them further. It is desirable that the query in the sql file filters as much as possible (due to speed reasons) but as this may not always be feasible it is possible to filter further by the use of C# code. There is a large variety of auto-generated access classes and methods for the database tables.

Other Tasks

• A Dialog is needed to set the name and description for an extract when the "Generate Extract" button is used. Until now this information is included in the "Parameters" tab but this is only temporary and it needs to be moved to a dialog.
• We need to be able to store query data for extracts so they can be re-run in the future with the same criteria. There are already tables existing in the database that are planned to be used for that purpose (m_extract_type, m_extract_parameter). The format for storing the data needs to be decided and a mechanism is needed to plug it into the existing code so it automatically gets stored and the user can re-run the extract.
• Need proper handling of the case when the extract is empty. Ideally no extract should be created in this case and the user should see an information message.

Scope: Extract Screens List

Note: The column 'Complexity' is not of importance to you, but it is important for the openPETRA team to be able to assign easier screens to you as you get started.

Screen Name	4GL files	Complexity	Phase	Comment	Assigned To	Approved
General Extract	maex195.w	?	1
Manual Extract		?	1
Publication Extract	maex170.w	?	1		Wolfgang Browa
Partner Type Extract	maex180.w	?	1		Andrew Webster
Conference Extract	maex230.w	?	1		Josh Peters
Outreach Extract	maex240.w	?	1		Josh Peters
Field Extract	maex160.w	?	1
Commitment Extract	maex165.w	?	1		Nathan Brink
Donor Extract by Field	maex190.w	?	1
Donor Extract by Motivation	maex270.w	?	1
Donor Extract by Amount	maex360.w	?	1
Donor Extract (Miscellaneous)	maex310.w	?	1
Recipient Extract	maex300.w	?	1
Contact Extract		?	2
Interest Extract		?	2
Relationship Extract		?	2
Local Partner Data Extract		?	2
Local Personnel Data Extract		?	2
Family Members Extract		?	2
Family Extract for Persons		?	2
Event Role Extract		?	2
Conference Role Extract		?	2
Accounts Payable Extract		?	2