Maximize Efficiency: Managing More Files in Programs
Estimated reading time: 24 minutes
Source
You can get the GitHub Source Files here.
Overview
Sometimes a program requires reading from additional database files to collect more data that we want to present to the user.
On the Sales/Returns Page, instead of showing in the Chart totals for Sales and Returns for a given Customer, we would like to show a year’s full of information with monthly data (the last year registered). The new chart is useful to show performance trends.
Reading from additional database files
We start with a program CUSTINQ
which calls CUSTCALC
to compute Total Sales and Returns.
CUSTINQ
does not have direct access to the CSMASTER
(Customer Sales/Returns Master File).
There is a Logical file, namely CSMASTERL1
that would give us a view of the Customer Sales/Returns Master File in the sorting we need - by customer number - to collect the yearly data. The following image shows the first ten records:
Since we have the source file for CUSTCALC
, we can copy code to bring this Logical file to CUSTINQ
.
We need to copy:
- Declaration of the file.
- Code to explicitly open the file.
Note:
CUSTINQ
closes All files when de-activated. This will include the new file.
We add the declaration along with the current file declarations, at the top of the class declaration:
DclDiskFile CSMASTERL1 Type(*Input) Org(*Indexed) DB(MyJob.MyDatabase) File("*LIBL/CSMASTERL1") ImpOpen(*No) RnmFmt( RCSMASTL1 )
We add code to open the new file in the class constructor:
Open CSMASTERL1 DB(CurrentJob.Database)
We leave the call to CUSTCALC
in case we decide to show Totals in the future (and as a reference for this example to contrast the code changes).
BegSr SalesInfo
ClearSel()
CMCUSTNO = SFCUSTNO
LockRec = "N"
CustChk()
CmCust# = CMCUSTNO
Move CMCUSTNO CmCust#CH
Move *Zeros SalesCh
Move *Zeros ReturnsCh
CallD Pgm( "ACME.SunFarm.CUSTCALC" ) LR(*IN88)
DclParm CmCust#CH
DclParm SalesCh
DclParm ReturnsCh
SFNAME = CMNAME
Move SalesCh SFSALES
Move ReturnsCh SFRETURNS
DoUntil ( *IN03 *Or ( *IN12) )
LoadSalesAndReturns()
ExFmt SALESREC
EndDo
SetOff *IN03 // Reset the exit ind.
EndSr
Note a call to a new subroutine named
LoadSalesAndReturns
. The rest of the code remains basically the same.
There is one more valuable piece of code we want from CUSTCALC
. Since we are collecting twelve months worth of data from the record at a time, it is convenient to declare a Data structure that will allow us to map all the values into an array:
DclDs CUSTSL ExtDesc(*Yes) DbDesc(MyJob.MyDatabase) FileDesc("*LIBL/CSMASTER")
DclDsFld YearlySales Type( *Packed ) Len( 11 ,2 ) Dim( 12 ) StartAt(11)
Each time we read from the
CSMASTER
file, theYearlySales
array will be populated with the values from fieldsCSSALES01
toCSSALES12
(positioned together starting at position 11 in the record buffer).
Collecting the data read using a twelve dimensioned array.
As you can see in the previous image, CSMASTERL1
has sales and returns records for full-years (January to December). Field CSYEAR
indicates the year where this data was recorded. CSTYPE
indicates if the record belongs to Sales or Returns (1
or 2
). Finally, field CSCUSTNO
groups the records by Customer number.
The logic we need is as follows:
CHAIN
(position to) the first record belonging to the requested Customer (CMCUSTNO
).- Depending on the value of
CSTYPE
, the record will have either Sales or Returns information. - Save the Year value (database field
CSYEAR
) into field variableLastRecordedYear
. - If this is the first time in the loop, save the monthly values (fields
CSSALES01
toCSSALES12
). - Else compare the last record’s
CSYEAR
with theLastRecordedYear
, if the year is greater or equal to the previously read year, updateLastRecordedYear
and save the yearly data: for Sales useLastRecordedYearSales
, for Returns useLastRecordedYearReturns
. - Read the next record going back to step 2. until no more records matching the same Customer exist.
The EncoreRPG is the following:
BegSr LoadSalesAndReturns
DclArray LastRecordedYearSales Like(CSSALES01) Dim(12)
DclArray LastRecordedYearReturns Like(CSSALES01) Dim(12)
DclFld LastRecordedYear Like(CSYEAR)
LastRecordedYear = 0
SetLL RCSMASTL1 Key(CMCUSTNO)
ReadE From(RCSMASTL1) Key(CMCUSTNO) Eof(*IN03)
DoWhile ( *Not *IN03)
If CSYEAR *GE LastRecordedYear
LastRecordedYear = CSYEAR
If CSTYPE = SaleEvent
LastRecordedYearSales = YearlySales
Else
LastRecordedYearReturns = YearlySales
EndIf
EndIf
ReadE From(RCSMASTL1) Key(CMCUSTNO) Eof(*IN03) // Read Next
EndDo
If LastRecordedYear *GT 0
// We found the yearly values we were looking for!
EndIf
.
.
.
Using a second Subfile in the Display file to populate the UI Model record
We use a subfile to communicate the yearly data collected (in the Application Logic) to the Sale’s record Model class (in the Website project).
We need the following new code:
- Declaration of the Subfile in the UI Model class.
- Since we are using a subfile in
SALESREC
we need to change theSALESREC_Model
type from RecordModel to SubfileRecordModel. - Update the RazorPage to reflect the changes in the DisplayPageModel (done in step 2).
WEBSITE CHANGES
The SALESREC_Model
defines the following fields:
[Char(10)]
public string SCPGM { get; private set; }
[Dec(6, 0)]
public decimal SFCUSTNO { get; private set; } // CUSTOMER NUMBER
[Char(40)]
public string SFNAME { get; private set; }
[Dec(13, 2)]
public decimal SFSALES { get; private set; }
[Dec(13, 2)]
public decimal SFRETURNS { get; private set; }
Right after SFRETURNS
we define the subfile as follows:
public class SFL_SalesReturns_Model: SubfileRecordModel
{
[Dec(4, 0)]
public decimal YEAR { get; private set; }
[Dec(11, 2)]
public decimal SALES { get; private set; }
[Dec(11, 2)]
public decimal RETURNS { get; private set; }
}
The SALESREC_Model
is defined as:
public class SALESREC_Model : RecordModel
{
We need to upgrade it to include the SFL_SalesReturns_Model
:
public class SALESREC_Model : SubfileControlModel
{
public List<SFL_SalesReturns_Model> SFL_SalesReturns { get; set; } = new List<SFL_SalesReturns_Model>();
Now that SALESREC_Model
is complete, lets update the tagHelper in the RazorPage’s matching declaration:
The Razor Page refers to the SALESREC
using the following markup:
<DdsRecord For="SALESREC" StretchConstantText=false KeyNames="F12 'Cancel';" ExcludeEmptyRows="6-20">
We need to upgrade it to match the Model by changing the tagHelper from DdsRecord
to DdsSubfileControl
:
<DdsSubfileControl For="SALESREC" StretchConstantText=false KeyNames="F12 'Cancel';" ExcludeEmptyRows="6-20">
If we recompile the Website, the new subfile is now available to the Application logic, were records will be written with yearly Sales and Returns information.
Note: When new fields are added to the DisplayPageModel class, the RazorPage has more fields available to display, but it is not required that the fields are used. We will see later in this Example how we will pass this new information directly to the Chart object.
WRITING SUBFILE RECORDS IN THE APPLICATION LOGIC.
The LoadSalesAndReturns
subroutine we added above to CUSTINQ
program has collected a year-worth of Sales and Returns data (for the last recorded activity year in the database). For this purpose we are using two arrays: LastRecordedYearSales
and LastRecordedYearReturns
.
The first thing we need is to update the DclWorkStnFile
declaration to include the second subfile SFL_SalesReturns
(that we added above). We also associate a field variable to represent the RRN (subfile Relative Record Number).
The declaration changes, from this:
DclWorkStnFile CUSTDSPF DspFile("~\Areas\SunFarmViews\Pages\CUSTDSPF") Subfile( SFL1, sflrrn)
To this:
DclWorkStnFile CUSTDSPF DspFile("~\Areas\SunFarmViews\Pages\CUSTDSPF") Subfile( SFL1, sflrrn, SFL_SALESRETURNS, SalesSflrrn )
.
.
.
DclFld SalesSflrrn Like(sflrrn)
The new Subfile SFL_SALESRETURNS
and corresponding RRN SalesSflrrn
are declared, ready for the rest of the Logic code to use.
Note: the record format
SFL_SalesReturns
needs to be declared using Uppercase (otherwise the external declaration will not be found).
To write the twelve records we have collected to the Subfile, we use the following code:
DclConst MonthsInYear 12
.
.
.
If LastRecordedYear *GT 0
If LastRecordedYear *GT 0
For Index( month=0 ) To( MonthsInYear )
WriteSalesReturnsSubfileRecord( LastRecordedYear, LastRecordedYearSales[month], LastRecordedYearReturns[month])
EndFor
EndIf
EndIf
Note: If
LastRecordedYear
remains in zero, this is because we did not find any Sales/Returns records in the database (the subfile will have zeros).
Where the new Subroutine WriteSalesReturnsSubfileRecord
is defined as:
BegSr WriteSalesReturnsSubfileRecord
DclSrParm _year Like(YEAR)
DclSrParm _sales Like(SALES)
DclSrParm _returns Like(RETURNS)
YEAR = _year
SALES = _sales
RETURNS = _returns
SalesSflrrn = SalesSflrrn + 1
Write SFL_SALESRETURNS
EndSr
VERIFY THE NEW DATA IN THE WEBSITE RECORD MODEL
If you run the Website, you will not get the new Chart yet.
We need to Intercept the Data-Flow from Program to the Model so we can transform the data to the format the amcharts JavaScript Library requires.
Let’s add the following method override to CUSTDSPF
(DisplayPageModel derived class):
protected override void OnCopyDspFileToBrowser()
{
base.OnCopyDspFileToBrowser();
if (SALESREC.IsActive && SALESREC.SFL_SalesReturns.Count > 0)
SALESREC.PopulateChartData();
}
//
// You can declare PopulateChartData in SALESREC_Model as empty for now ...
//
internal void PopulateChartData()
{
}
As the code states, we are interested in intercepting the data ONLY if the SALESREC
is Active and the Subfile with the yearly Sales/Returns is NOT empty.
Let’s set a breakpoint at the call to SALESREC.PopulateChartData();
:
- Run the Application (with Debugging enabled).
- Use
3=Display sales
on one of the Customers. - Hit
Enter
. The breakpoint should Hit.
You can use Visual Studio Quick Watch debugging tools to verify that the subfile has twelve records written. Expand the first record (zero sub-index) - corresponding to January Sales/Returns - and you can verify that the record belongs to YEAR=1997 with January SALES=8,673.93 and RETURNS=-1,2329.47 (these numbers depend on the record you selected, and the information in the database).
With the confidence that the Subfile is working correctly to provide the information from the database, we proceed to complete the code to pass this information to the amcharts JavaScript Library.
Note: you can skip the next section if you are not interested on using the amcharts JavaScript Library. The goal of this Example was to show you how you can get more database information from a Program to the Website.
Interfacing with a JavaScript third-party Library.
The Chart we want to show is a two-series Line chart. The first series is the Sales information displayed in green and the second is the Returns information, displayed in red. The x-Axis will use Date units (where we are interested in Month labels, for a given Year). The y-Axis represents dollar amounts.
Instead of adding the required JavaScript to the RazorPage itself, we use a reference to a new JavaScript file. (Having the scripting code external to the Page helps while debugging client-side scripts using Browser Developer Tools).
The new file is named Chart.js
and is created under wwwroot\js
folder.
const MONTHS_YEAR = 12;
const LoadChart = (appData) => {
if (Object.keys(appData).length === 0) { return; } // Make sure object is NOT empty
const CHART_ID = 'sales-chart';
const chartEl = document.getElementById('sales-chart');
if (chartEl) {
// Create chart instance
const chart = am4core.create(CHART_ID, am4charts.XYChart);
chart.paddingRight = 20;
// Create axes
const dateAxis = chart.xAxes.push(new am4charts.DateAxis());
const valueAxis = chart.yAxes.push(new am4charts.ValueAxis());
dateAxis.dataFields.category = 'date';
dateAxis.dateFormat = 'MMM';
dateAxis.renderer.minGridDistance = 50;
dateAxis.renderer.grid.template.location = 0.5;
dateAxis.startLocation = 0.5;
dateAxis.endLocation = 0.5;
valueAxis.dataFields.valueY = 'value';
valueAxis.title.text = 'Dollars';
const salesSeries = chart.series.push(new am4charts.LineSeries());
salesSeries.dataFields.dateX = 'date';
salesSeries.dataFields.valueY = 'value';
salesSeries.tensionX = 0.8;
const salesBullet = salesSeries.bullets.push(new am4charts.CircleBullet());
salesBullet.tooltipText = 'Sales {valueY}';
salesSeries.stroke = am4core.color('rgb(0,255,0)');
const returnsSeries = chart.series.push(new am4charts.LineSeries());
returnsSeries.dataFields.dateX = 'date';
returnsSeries.dataFields.valueY = 'value';
returnsSeries.tensionX = 0.8;
const returnsBullet = returnsSeries.bullets.push(new am4charts.CircleBullet());
returnsBullet.tooltipText = 'Returns {valueY}';
returnsSeries.stroke = am4core.color('rgb(255,0,0)');
salesSeries.data = [];
returnsSeries.data = [];
for (let month = 0; month < MONTHS_YEAR; month++) {
salesSeries.data.push({
"date": new Date(appData.year, month), "value": appData.sales[month]
});
returnsSeries.data.push({
"date": new Date(appData.year, month), "value": appData.returns[month]
});
}
}
}
Without getting into too much detail regarding how the amcharts JavaScript Library is used (topic outside the scope of this example), there are three important pieces of code needed to understand for the purposes of this example:
- Where this function is being called.
- What is expected on the only
appData
function parameter. - How is the data in the two series used by the Chart.
CALLING THE JAVASCRIPT FUNCTION
The RazorPage ~\SunFarmSite\Areas\SunFarmViews\Pages\CUSTDSPF.cshtml
has the following code at the end of the file.
<script src="https://cdn.amcharts.com/lib/4/core.js"></script>
<script src="https://cdn.amcharts.com/lib/4/charts.js"></script>
<script src="~/js/Chart.js"></script>
<script>
LoadChart(@Html.Raw(@Model.SALESREC.SalesReturnsChartData));
</script>
The first two script lines inject the JavaScript library into the DOM.
The third script line injects the JavaScript file that has our listed above.
The last script block has the call to our LoadChart
function.
Please note:
- We use @Html.Raw RazorPage helper method to avoid additional Html escaping processing.
- The parameter
@Html.Raw(@Model.SALESREC.SalesReturnsChartData)
is evaluated on the Web server, as part of theHTML
rendering phase, before the response is sent to the Client (Browser). - The string that
@Html.Raw
returns is used verbatim in the script. In this case the syntax describes a JavaScript object. The JavaScript parser will instantiate an object and use it as the parameter toLoadChart
. SalesReturnsChartData
is a C# string property defined in theSALESREC_Model
class, defaulting to"{}"
. If the value for the string property is NOT changed by theSALESREC_Model
, the JavaScript interpreter will instance an empty object.
Probably the easiest way to understand what is expected on the appData
object parameter is to capture one example and show it in its string JSON notation:
{
"year": 1997,
"sales": [
35960.71, 107542.51, 66449.91, 46780.34, 67849.54, 52377.25, 102155.91, 70192.77, 115569.25, 98646.14, 112500.55, 132139.17
],
"returns": [
4805.42, 14669.42, 9301.03, 6372.28, 9502.68, 6991.5, 14102.82, 9375.55, 15596.11, 13298.68, 14903.11, 17813.95
]
}
The object defines three properties:
year
: numeric (expected valid 4 digit year).sales
: an array of twelve months (each valid decimal value representing dollar amount).returns
: an array of twelve months (each valid decimal value representing dollar amount).
Note: our simplified function does not validate the property values given.
Given all this information, the implementation for PopulateChartData
(to update the value of property SalesReturnsChartData
used in step 4 above), is simple:
internal void PopulateChartData()
{
if (SFL_SalesReturns.Count == 0)
return;
decimal[] chartSales = new decimal[12];
decimal[] chartReturns = new decimal[12];
for( int i =0; i < SFL_SalesReturns.Count; i++ )
{
chartSales[i] = SFL_SalesReturns[i].SALES;
chartReturns[i] = Math.Abs( SFL_SalesReturns[i].RETURNS ); // Easier to view positive values.
}
SalesReturnsChartData = "{";
SalesReturnsChartData += $"year: {SFL_SalesReturns[0].YEAR},";
SalesReturnsChartData += $"sales: [{ToCommaSeparatedValueStr(chartSales)}],";
SalesReturnsChartData += $"returns: [{ToCommaSeparatedValueStr(chartReturns)}]";
SalesReturnsChartData += "}";
}
private string ToCommaSeparatedValueStr(decimal[] data)
{
string result = string.Empty;
foreach (var val in data)
{
result += $"{val},";
}
result = result.TrimEnd(',');
return result;
}
Lastly, we proceed to describe how is LoadChart
using appData
object.
This object is only used when describing the series chart data JSON object.
Let’s take one of the series to talk about it. (The other uses the same technique):
salesSeries.data = [];
for (let month = 0; month < MONTHS_YEAR; month++) {
salesSeries.data.push({
"date": new Date(appData.year, month), "value": appData.sales[month]
});
}
The chart series is an array of objects to be plotted as x,y coordinates.
You may notice (in the creation of each series) the code that maps fields with chart data-points:
salesSeries.dataFields.dateX = 'date';
salesSeries.dataFields.valueY = 'value';
Each object in the array has two properties:
- “date” : a JavaScript Date object.
- “value” : a JavaScript floating point numeric value.
The names are not important as long as they match the
series.dataFields
dateX
andvalueY
property values.
We create twelve Date objects, each for every month of the Year. Notice that for the year we use our parameter appData
, property year
. When creating the Date (using new syntax), we pass the year
and an integer value representing the month (0=Jan, 1=Feb, etc). We exclude the day of the month (and the rest of time-of-day information). Date constructor only requires year, month
the rest are optional. This is exactly what we have.
For the value
properties, we simply refer to the value we have from our Subfile (sales or returns on each month, depending on the series we are loading).
The rest of the code in this JavaScript function selects chart styles to make it appealing and allowing the user to get individual point information (by hovering the mouse over the bullets on the chart lines).
Look at the results in next section. This Chart conveys - with little effort - significantly more information to the user. Information we already had in the database but inaccessible, until now.
Results
Before | After |
---|---|