In the previous issue, we described how and why to subclass the framework classes, the main program, the system menu, we explained, in some detail, the classes that make up the base layers and their most important methods. We also presented the layers of a specific entity representing a simple table and its corresponding data entry form, emphasizing the tiny amount of coding that we require so that everything works. In this issue, we will continue with the creation of the layers for an entity that includes more than one table, in a header-detail schema; as we shall see, also in this case, the required code is minimal.
Remember that you can download the latest version of the TierAdapter and the complete Demo application, including source code, from the main page of our framework at SourceForge.
Entity of the type Header/Detail
As we saw in the previous issue, to represent a specific entity, we must create at least one class to represent it at the data layer tier, one for the business tier and one for the user tier. Taking the "Order" form of the TierDapater Demos as an example, the corresponding classes for each layer would be:
Entity Order, data tier
Define Class doOrder AS doGeneral of doGeneral.prg OLEPUBLIC Procedure Init DoDefault() #If .T. && Example using one or more tables. * The following values for the properties * cGetAllFields, cGetAllJoins and cGetAllView are to show * the use of GetAll and GetAllPaginated against one or * more tables. This.cGetAllFields = "Orders.OrderID, Orders.OrderDate,"; + " Customers.CompanyName, Orders.ShippedDate" This.cGetAllJoins = " INNER JOIN Customers " ; + "ON Orders.CustomerID = Customers.CustomerID " This.cGetAllView = "" #EndIf #If .F. && Example using a view. * The following values for the properties * cGetAllFields, cGetAllJoins and cGetAllView show the use of * GetAll and GetAllPaginated against a view. This.cGetAllFields = "OrderID, OrderDate, CompanyName," + " ShippedDate" This.cGetAllJoins = "" This.cGetAllView = "[Orders Qry]" #EndIf Local lcSQL AS String TEXT TO lcSQL NOSHOW PRETEXT 7 SELECT Orders.OrderID, Orders.CustomerID, Customers.CompanyName, Orders.EmployeeID, Orders.OrderDate, Orders.RequiredDate, Orders.ShippedDate, Orders.ShipVia, Orders.Freight, Orders.ShipName, Orders.ShipAddress, Orders.ShipCity, Orders.ShipRegion, Orders.ShipPostalCode, Orders.ShipCountry FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID ENDTEXT lcSQL = STRTRAN( lcSQL, CHR(13)+CHR(10), "" ) This.ColTables.AddTable( 'Orders' , 'OrderHeader' , '' , '' ,; '', lcSQL, 'OrderID', .F. ) If This.cBackEndEngine = "SQLOLEDB" *!* To use with Northwind from SQL-Server TEXT TO lcSQL NOSHOW PRETEXT 7 SELECT [Order Details].ProductID, Products.ProductName, [Order Details].Quantity, [Order Details].UnitPrice, [Order Details].Discount, 00000000.00 AS TotalItem, [Order Details].OrderID FROM [Order Details] INNER JOIN Products ON Products.ProductID = [Order Details].ProductID ENDTEXT lcSQL = STRTRAN( lcSQL, CHR(13)+CHR(10), "" ) This.ColTables.AddTable( '[Order Details]', 'OrderDetail', ; 'Orders' , 'OrderID' , '', lcSQL, ; 'OrderID, ProductID', .T. ) Else *!* To use with Northwind from VFP TEXT TO lcSQL NOSHOW PRETEXT 7 SELECT OrderDetails.ProductID, Products.ProductName, OrderDetails.Quantity, OrderDetails.UnitPrice, OrderDetails.Discount, 00000000.00 AS TotalItem, OrderDetails.OrderID FROM OrderDetails INNER JOIN Products ON Products.ProductID = OrderDetails.ProductID ENDTEXT lcSQL = Strtran( lcSQL, CHR(13)+CHR(10), "" ) This.ColTables.AddTable( 'OrderDetails' , 'OrderDetail',; 'Orders' , 'OrderID' , '',; lcSQL, 'OrderID, ProductID',; .T. ) EndIf Return ( This.ColTables.Validate() ) EndProc Procedure GetAllFilterCriteria( tcFilterCriteria As String ) As String Local lcRetVal As String XMLToCursor( tcFilterCriteria, 'cFilterCriteria' ) lcRetVal = '' Scan If This.cBackEndEngine = "SQLOLEDB" *!* To use with Northwind from SQL-Server Do Case Case Alltrim( cFilterCriteria.cFilterTag ) = "dOrderDateFrom" lcRetVal = lcRetVal + Iif( Empty( lcRetVal ), ""," And " ); + "OrderDate >= "; + DToSql( Ctot( Alltrim( cFilterCriteria.cFilterValue ))) Case Alltrim( cFilterCriteria.cFilterTag ) = "dOrderDateTo" lcRetVal = lcRetVal + Iif( Empty( lcRetVal ), ""," And " ); + "OrderDate <= "; + DToSql( Ctot( Alltrim( cFilterCriteria.cFilterValue ))) EndCase Else *!* To use with Northwind from VFP Do Case Case Alltrim( cFilterCriteria.cFilterTag ) = "dOrderDateFrom" lcRetVal = lcRetVal + Iif( Empty( lcRetVal ), ""," And " ); + "OrderDate >= "; + Alltrim( cFilterCriteria.cFilterValue ) Case Alltrim( cFilterCriteria.cFilterTag ) = "dOrderDateTo" lcRetVal = lcRetVal + Iif( Empty( lcRetVal ), ""," And " ); + "OrderDate <= "; + Alltrim( cFilterCriteria.cFilterValue ) EndCase EndIf EndScan Return lcRetVal EndProc EndDefine
In the preceding code, we can see two examples, hidden between the preprocessor directives, that correspond to the alternatives to using tables vs. using views in the method GetAll() of TierAdapter. The new properties are cGetAllJoin and cGetAllView. The first one saves the string of the joins that we would need in case of having to combine more than one table to obtain the information in the GetAll() method. In case we have some value in the second property, the GetAll() method will search for a view with this name, instead of a table, to obtain the records.
The next new element is the code between the TEXT and ENDTEXT commands. This text string assigned to the variable lcSQL should correspond to a SELECT instruction that will be used by the GetOne() method when recovering information from an individual member of the corresponding entity. This information is only necessary if we want to do a selection that doesn't include all the fields in the table, or if we fetch information from more than one table through one or more joins.
In our case, since this is a demo which should work with two different data sources, and since there are slight differences between the SQL Server and VFP NorthWind databases, we found it necessary to condition the commands to the data origin; normally, this kind of condition should not be necessary.
If we take a close look at the two calls to the AddTable() method, we will see that the second one is different from the first, in that it has values for the third and fourth parameter. This indicates the name and the primary key of the Orders table. We tell TierAdapter that the table OrderDetails is subordinated to table Orders through a Header-Detail relation, and TierAdapter will establish and manage this relation transparently for the developer.
Finally, we see the method GetAllFilterCriteria(). This method shall be used by the specialized Selector form for this entity, in order to filter the records obtained; we will analyze it in detail when it is time to discuss the selection form, later in this issue.
Order entity, business tier
Define Class boOrder As boGeneral Of boGeneral.prg OLEPUBLIC cTierClass = "doOrder" && Name of the class with which we do the instantiation. * Example of using validations and or adding or modifying * data in the business tier before saving. Procedure Put( idEntidad As Variant, tcDiffGram As String, nLevel AS Number ) As String Local lcDiffGram As String Local loXA As rrXMLAdapter loXA = NewObject("rrXMLAdapter", "rrXMLAdapter.Prg" ) This.GetBack( IdEntidad, tcDiffGram, nLevel, loXA ) *** From here it is possible to validate and modify the cursors at will *** Local llOk As Boolean Local lcErrDetail As String Do Case Case Empty( OrderHeader.RequiredDate ) lcErrDetail = "You can not leave the RequiredDate empty!" llOk = .F. Case Empty( OrderHeader.ShippedDate ) lcErrDetail = "You can not leave the ShippedDate empty!" llOk = .F. Otherwise llOk = .T. EndCase *** Until here, the cursors can be modified at will *** * If all is OK, go to the data tier, else, return to the user tier\ * informing about the problem If llOk lcDiffGram = This.PutBack( loXA ) Return( This.oEntidad.Put( idEntidad, lcDiffGram, nLevel ) ) Else Local loError As rrException loError = NewObject( "rrException", "rrException.prg" ) With loError .Message = "Some errors where found in the order" .ErrorNo = 9999 .Details = lcErrDetail .Procedure = "boOrder.Put()" .Save() EndWith Local lcRetVal As String lcRetVal = This.SendData( 1 ) Return ( lcRetVal ) EndIf EndProc EndDefine
In this class, we see a brief example of how validations and/or business rules can be introduced into the corresponding tier, thus isolating it both from the user tier and from the data tier. To this effect, the Put() method of this class should be specialized to insert the pertinent code before the process of data persistence.
As we can see, once we obtain the corresponding cursors (remember, we are working with a Detail-Header schema), using the method GetBAck(), we proceed to validate the relevant fields, in the code within the DO CASE; next, we decide, based on the results of the variable llOk, whether we should proceed with the data persistence, or return the detected error to the user. The methods GetBack() and PutBack() are the ones in charge of preserving the information of the changes done to the data to the different tiers (you can see the documentation of these methods in the class BusinessTierAdapter).
Entity Order, User tier
Define CLASS uoOrder AS uoGeneral of uoGeneral.prg cTierClass = "boOrder" && Name of the class with which it does the instantiation. cSelectorFormName = "OrdersSelector" cSelectorFields = "OrderID, OrderDate, CompanyName, ShippedDate" cSelectorCaptions = "Order ID, Order Date, Customer, Shipped Date" EndDefine
In this class we don't find new elements, and the existing elements have already been explained before. Therefore, we continue to see the form that will consume the services of the previous classes.
Form Order
This form has a PageFrame with 2 pages, in order to accommodate the header data (Figure 1) and the detail data (Figure 2). In order for this to be automatically synchronized with the TierAdapter toolbar, it has to be based on the msActionForm class, that has been provided with a series of properties and methods, some of which have been described previously.
Figure 1: Form Order, header data
Figure 2: Form Order, detail data
As we can see in figure 3, there are only few properties to be configured in order for the form to be functional. These are:
Figure 3: Properties of the Order form
Next, we shall analyze some of the most significant events of this form.
INIT
Lparameters tnIdEntity, tlModalWindowType With thisform * Create the auxilliary business objects .AddProperty( "oProduct", NewObject( "uoProduct", "uoProduct.prg", "", .DataSessionID ) ) .AddProperty( "oCustomer", NewObject( "uoCustomer", "uoCustomer.prg", "", .DataSessionID ) ) .pgfOrder.msPage1.oPickOneCustomer.SetUp( .oCustomer ) .AddProperty( "oEmployee", NewObject( "uoEmployee", "uoEmployee.prg", "", .DataSessionID ) ) .oEmployee.GetAll() Select ALLTRIM( LastName ) + ", " + ALLTRIM( FirstName ) AS CompleteName, EmployeeID ; From cEmployee ; Into Cursor Employees ; Order By CompleteName ; NoFilter ReadWrite Use In cEmployee .AddProperty( "oShipper", NewObject( "uoShipper", "uoShipper.prg", "", .DataSessionID ) ) .oShipper.GetAll() Select CompanyName, ShipperID ; From cShipper ; Into Cursor Shippers ; Order By CompanyName ; NoFilter ReadWrite Use In cShipper EndWith If DoDefault( tnIdEntity, tlModalWindowType ) Else Return .f. EndIf
Following the order of the code, we can comment:
The parameter tnIdentity allows the form to automatically obtain the data that corresponds to a primary key equal to the value contained in this parameter, and open it showing this information. You can read more about this subject accessing the primitive code of this method in the msActionForm class.
The next thing we encounter is the creation of several auxilliary objects, which, in this case, correspond to the entities required to obtain the data referenced by the fields that constitute the foreign keys in the member tables of the Order entity. In this example we see that these objects have been called "oProduct", "oCustomer", "oEmployee" and "oShipper".
Before we continue with the analysis of the auxilliary object, we want to clarify that the present demo application started in training sessions. Adding the auxiliary objects was done, as shown above, for pedagogical reasons, later, it stayed as it is, for reasons of time restraints. Let's now review this code, remembering that it would be more correct, and have a better performance, if we added these properties to the form at design tame, and eliminate the AddProperty() command from the Init() method. In this case, we can try this, for example, replacing the code:
.AddProperty( "oEmployee", NewObject( "uoEmployee", "uoEmployee.prg", "", .DataSessionID ) )
with:
.oEmployee = NewObject( "uoEmployee", "uoEmployee.prg", "", .DataSessionID )
where the property oEmployee has been added at design time; similarly, we can proceed with the other properties. These changes will be reflected in the demo when we publish the next version. After clarifying this, let's finish seeing the remaing code of the event.
Here, the auxilliary objects are used to provide data to the taPickOne controls (to make this control work, we only need to invoke the SetUp method in the same, sending, as a parameter, the auxilliary object that references the corresponding entity, in this case, oCustomer, from which it will automatically obtain the required data), and msComboBox (configuring as needed the property RowSource at design time, according to the objects oEmployee and oShipper) from the Header page, as well as the product search when we add rows to the order in the Detail page, as we will explain later. For the objects oEmployee and oShipper, the GetAll() method is invoked to obtain the cursor with the totality of the records that will make up the RowSource of the corresponding ComboBoxes.
taPickOne, some considerations
At this point we should comment that, although the taPickOne object has a use which is similar to a ComboBox, that is, it is usually used to obtain a record from a table whose primary key is referenced by a foreign key in the table in which we are working, its use goes much further, as we have already described. Its use is less straightforward than a ComboBox; therefore, the criterion we adopted in TierAdapter is to use taPickOne when the corresponding table has a large number of records, making a ComboBox unpractical, and vice versa.
The other event which we deem useful to analyze for illustrational purposes is the RightClick event of the grid in the Detail page:
RigthClick
Local lnMenu as Integer lnMenu = xMenu( "Add;Delete") Do Case Case lnMenu = 1 Do Form ProductsSelector ; With "uoProduct" ; To loSelect IF loSelect.Estado = "OK" Thisform.oProduct.GetOne( loSelect.Valor, 1 ) INSERT INTO OrderDetail ( OrderID, ProductID, ProductName, ; UnitPrice, Quantity ) ; VALUES ( OrderHeader.OrderID, cProduct.ProductID, ; cProduct.ProductName, cProduct.UnitPrice, 1 ) this.Refresh() ENDIF Case lnMenu = 2 If Eof( "OrderDetail" ) * Nothing to do Else * Erases the records and refreshes the display and the totals Delete In OrderDetail If Eof() Go bottom Else Skip EndIf this.Refresh() thisform.SumaTotal() EndIf EndCase
In this code segment, once again, we see the xMenu() function at work, generating a menu that will allow us to select between adding and eliminating records shown through the grdOrder grid. Depending on the value of variable lnMenu, it proceeds to insert or delete a record of cursor OrderDetail, invoking, in the first case, form ProductsSelector, which – as we have explained previously – allows us to select a product (eventually searching it by name) to add to the order.
As we can see when we look at the Demo application, there is not much code to write for this form to be totally functional.
Creating a new form
We will continue to present the steps required to make a new form work. The first is to generate the 3 classes that will make up the user tier, the business tier and the dada tier. Since we have previously explained this in detail, we will now use the entity Employee and its 3 classes, already existing in the Demo application. After doing this, we only need to create the form.
At this point, it is convenient to make sure that the new form be based on the msActionForm class, for example, using the command
CREATE FORM Employee AS msActionForm FROM msActionForm
Since the table has a considerable amount of fields, a practical way of obtaining the required editing controls is as follows:
Let's see some of the previous steps in images.
Figure 4: Steps 1 to 3
Figure 5: Steps 4 and 5
Figure 6: Steps 6 to 8
Figure 7: The finished form
The entire process up to step 10 doesn't take more than a minute. From here on, configuring the few properties that we saw above, the form is completely functional; the only thing missing is to add the validation code that the developer considers necessary.
Note: This trick, of generating the controls, is quite embarassing, since the correct thing would be to create a builder that does this work. As we already explained before, the lack of time makes us misuse this kind of things. We hope to include many of you in the optimization of this framework, both with comments and with suggestions, and with tools that make the development quicker, which you can send us, and they will be included in the official framework site. For any additional information that you consider necessary for the development of such tools, feel free to contact us.
Including a menu access for the new form
Since TierAdapter constructs the menu dynamically, to call the new form which we just created, we simply add a record to the Access table, filling in the fields as follows:
idAccess: (autoincremental) cType: MENU cModule: MainMenu cCodeItem: 12 cCodeParent: 1 iOrder: 40 cCaption: \<Employees cCommand: Launch('Employee')
Figure 8: Edit of the added record
And now, our new form is ready to be used.
The selection form
The selection form "Selector" lets us select an element from a data set, according to specific parameters that we can define according to every specific entity.
Apart from filtering the data set according to the specified criteria (we will immediately see how), the results are returned in a paged format.
For simple entities, where perhaps it doesn't make sense to define filter criteria if there are few records in the data source, we have a generic selector (see form GenericSelector.scx), where the search page is disabled, and the second page, called the result page, is automatically activated.
Let's see, as an example, the group selector, which is a GenericSelector.
Figure 9: The generic selector
Here, with double-click, we can select one of the items, or use the control to move through the result pages, searching the desired option.
It should be noted that the generic selector is useful if the amount of items from which we are going to choose is not very big; if it is, it will no longer be practical, since, although the results are returned by pages, which will maintain a good performance, the number of pages returned will be high, and almost impossible to review completely. For these cases, we should create a new selection form based on the taSelector class (see taSelector.vcx), and thus define, in the first page of the PageFrame, called "Search", some filter criteria that we consider appropriate, and which, if used correctly, will reduce the size of the sample.
The way of indicating to the framework that it should use a special selector rather than a generic selector is to fill out three properties in the entity's user tier. We will now see the three properties of the user tier for the case of the Customer entity (see uoCustomer.prg)
cSelectorFormName = "CustomersSelector" cSelectorFields = "CompanyName, CustomerID" cSelectorCaptions = "Company Name, Customer ID"
In the first, we indicate the name of the form; in the second, the fields to be included as columns in the result grid, and in the third, the header caption of these columns. It should be noted that the fields which we can include in the property "cSelectorFields" must include those which we obtain from a common query through the GetAll method, which is the method which the selector executes internally.
Let's see, as an example, the Customers selector (see CustomersSelector.scx)
Figure 10: The Customers selector – Filter definition
Figure 11: The Customers selector – Results obtained
In figure 10 we see how we have defined four filter criteria, i.e. CustomerID, CompanyName, City and Country. Inputting data in some of them, the results will be filtered according to these criteria (Figure 11).
The "secret" is in every one of the controls where the user is going to input the desired filters, in this case, 4 TextBoxes. You have to define a criteria name in its "Tag" property. As an example, see, in figure 12, the TextBox for the filter criteria by CustomerID, whose "Tag" property is defined as "cCustomerID".
Figure 12: The Customers selector – Definition of "Tags"
Internally, when you click on the "Search" button, the framework loops through all the controls on the page, and those whose "Tag" property is not empty are included in a list in which the Tag and the value used is included. This data is sent through the tiers, until it reaches the data tier, where it is processed by the method GetAllFilterCriteria, where the filter criteria is set up. Let's see part of this method, extracted from the data tier of the Customer entity (see doCustomer.prg)
Procedure GetAllFilterCriteria( tcFilterCriteria As String ) As String Local lcRetVal As String XMLToCursor( tcFilterCriteria, 'cFilterCriteria' ) lcRetVal = '' Scan Do Case Case Alltrim( cFilterCriteria.cFilterTag ) = "cCustomerID" lcRetVal = lcRetVal ; + Iif( Empty( lcRetVal ), "", " And " ) ; + "Customers.CustomerID Like '" ; + Alltrim( cFilterCriteria.cFilterValue ) + "%'" Case ... EndCase EndScan Return lcRetVal EndProc
As you can see, the data arrives in XML format, and is transformed into a cursor. This cursor is scanned completely, and depending on the name of the "Tag", it is processed as appropriate for the data type, the engine on which the SQL query is applied, the type of search to be done, etc.
Conclusion
Today we saw how to create new forms for data input, how to add items to the system menu, how to use the generic record selector and how to specialize a selector for a specific entity.
In the next issue, we will continue to develop an application from the start, one step at a time. We will delve a little into the internals of the framework, to see how to manage errors (left out of this issue due to space considerations), and how to manage transactions. See you next month.
