phase_2_notes - x-ian/dha-mis-migration GitHub Wiki

code_healthsite: lat & long fiels missing in form of admin frontend switch the field labels for lat / lon on the code_hfacility table and add the 2 fields to the hfaciltiy form in the admin frontend....

Integration into:

• issues & consumptions
• stocks & tranactions -> psm_site_stock_consumpt_adjust (44 - 70)
• column with distance (or travel time?)

MapWinGIS 4.9.3.5 win64: http://mapwingis.codeplex.com/releases/view/615102 4.9.3.6 x86: http://mapwingis.codeplex.com/releases/view/618794 http://download-codeplex.sec.s-msft.com/Download/Release?ProjectName=mapwingis&DownloadId=1525833&FileTime=130936357338670000&Build=21055

Label bugs https://plus.google.com/111423356054890877430/posts/CkZZToCUE38 https://github.com/MapWindow/MapWinGIS/issues/12 http://mapwingis.codeplex.com/discussions/662863

Docs: http://www.mapwindow.org/documentation/mapwingis4.9/index.html https://mapwingis.codeplex.com/wikipage?title=Shapefile%20common%20tasks http://docplayer.net/storage/27/10794207/1506667965/2hXY9FcAFQnBzU8q94T27A/10794207.pdf http://agecon.okstate.edu/gis/files/Practical%20Look%20at%20MapWindows.pdf

MapWinGIS

Private Sub map_Enter()
    Dim objMap As MapWinGIS.map

    Dim ogrLayer As MapWinGIS.ogrLayer
    ogrLayer.OpenFromFile ("c:\test.kml")

    
    Set objMap = Me!map.Object

    
    objMap.ZoomToMaxExtents
    objMap.KnownExtents = keMalawi
    objMap.AddLayerFromFilename
End Sub

<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="http://www.opengis.net/kml/2.2">
<Document>
  <Placemark>
    <name>Zürich</name>
    <description>Zürich</description>
    <Point>
      <coordinates>8.55,47.3666667,0</coordinates>
    </Point>
  </Placemark>
</Document>
</kml>

-- found in stackoverflow.com/questions/13026675
						 CREATE VIEW [dbo].[view_code_hfacility_distances]
						 AS
						 SELECT        a.ID as source_id, a.hfacility_name as source_name, b.ID AS id, b.hfacility_name, b.district, b.hfactype, b.rururb, b.HMIU_code, b.Site_code, b.gps_x, b.gps_y, b.[User], b.TimeStamp, 
						                          b.UpdateUser, b.UpdateTimeStamp, b.Voided, b.VoidedBy, b.VoidedTimeStamp, b.access_timestamp, 
round(geography::Point(a.gps_x, a.gps_y, 4326)
.STDistance(geography::Point(b.gps_x, b.gps_y, 4326)) 
/ 1000,0) as dist_km
						 FROM            dbo.code_hfacility AS a CROSS JOIN
						                          dbo.code_hfacility AS b
						where not(a.gps_x is null) and not(a.gps_y is null) and not(b.gps_x is null) and not(b.gps_y is null) 
						and a.voided=0 and b.voided=0 and a.id <> b.id
						

GO

-- select dbo.distance(34.65306, -15.33056, 34.65361, -15.395); -- matandani - neno 7.5
-- select dbo.distance(34.825, -15.523, 34.65361, -15.395); -- zalewa - neno 26
-- select dbo.distance(-15.523, 34.825, -15.395, 34.65361); -- zalewa - neno 26

-- drop function dbo.Distance;

-- 'stolen from http://www.geodatasource.com/developers/mssql
-- potentially also (in JS) https://stackoverflow.com/questions/27928/calculate-distance-between-two-latitude-longitude-points-haversine-formula

create function dbo.Distance( @lat1 float , @long1 float , @lat2 float , @long2 float)
returns float

as

begin

declare @DegToRad as float
declare @Ans as float
declare @Miles as float

set @DegToRad = 57.29577951
set @Ans = 0
set @Miles = 0

if @lat1 is null or @lat1 = 0 or @long1 is null or @long1 = 0 or @lat2 is
null or @lat2 = 0 or @long2 is null or @long2 = 0

begin

return ( @Miles )

end

set @Ans = SIN(@lat1 / @DegToRad) * SIN(@lat2 / @DegToRad) + COS(@lat1 / @DegToRad ) * COS( @lat2 / @DegToRad ) * COS(ABS(@long2 - @long1 )/@DegToRad)

-- 3959 for miles
-- 6387.7 for km

set @Miles = 6387.7 * ATAN(SQRT(1 - SQUARE(@Ans)) / @Ans)

set @Miles = CEILING(@Miles)

return ( @Miles )

end

http://www.geodatasource.com/developers/vb:

':::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
':::                                                                         :::
':::  This routine calculates the distance between two points (given the     :::
':::  latitude/longitude of those points). It is being used to calculate     :::
':::  the distance between two locations using  GeoDataSource(TM) products   :::
':::                                                                         :::
':::  Definitions:                                                           :::
':::    South latitudes are negative, east longitudes are positive           :::
':::                                                                         :::
':::  Passed to function:                                                    :::
':::    lat1, lon1 = Latitude and Longitude of point 1 (in decimal degrees)  :::
':::    lat2, lon2 = Latitude and Longitude of point 2 (in decimal degrees)  :::
':::    unit = the unit you desire for results                               :::
':::           where: 'M' is statute miles (default)                         :::
':::                  'K' is kilometers                                      :::
':::                  'N' is nautical miles                                  :::
':::                                                                         :::
':::  Worldwide cities and other features databases with latitude longitude  :::
':::  are available at http://www.geodatasource.com	                         :::
':::                                                                         :::
':::  For enquiries, please contact [email protected]                  :::
':::                                                                         :::
':::  Official Web site: http://www.geodatasource.com                        :::
':::                                                                         :::
':::             GeoDataSource.com (C) All Rights Reserved 2017              :::
':::                                                                         :::
':::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::

const pi = 3.14159265358979323846

Function distance(lat1, lon1, lat2, lon2, unit)
  Dim theta, dist
  theta = lon1 - lon2
  dist = sin(deg2rad(lat1)) * sin(deg2rad(lat2)) + cos(deg2rad(lat1)) * cos(deg2rad(lat2)) * cos(deg2rad(theta))
  response.write "dist = " & dist & "<br>"
  dist = acos(dist)
  dist = rad2deg(dist)
  response.write "dist = " & dist & "<br>"
  distance = dist * 60 * 1.1515
  Select Case ucase(unit)
    Case "K"
      distance = distance * 1.609344
    Case "N"
      distance = distance * 0.8684
  End Select
End Function


'::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
':::  This function get the arccos function using arctan function   :::
'::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
Function acos(rad)
  If Abs(rad) <> 1 Then
    acos = pi/2 - Atn(rad / Sqr(1 - rad * rad))
  ElseIf rad = -1 Then
    acos = pi
  End If
End function


'::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
':::  This function converts decimal degrees to radians             :::
'::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
Function deg2rad(Deg)
	deg2rad = cdbl(Deg * pi / 180)
End Function

'::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
':::  This function converts radians to decimal degrees             :::
'::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
Function rad2deg(Rad)
	rad2deg = cdbl(Rad * 180 / pi)
End Function

msgbox(distance(32.9697, -96.80322, 29.46786, -98.53506, "M") & " Miles<br>")
msgbox(distance(32.9697, -96.80322, 29.46786, -98.53506, "K") & " Kilometers<br>")
msgbox(distance(32.9697, -96.80322, 29.46786, -98.53506, "N") & " Nautical Miles<br>")

Using a subform with the query psm_site_stock_consumpt_adjust as SourceObject the below error occurs.

You can't use a pass-through query or non-fixed-column crosstab query as a record source for a subform or subreport. Before you bind the subform or subreport to a crosstab query, set the query's ColumnHeadings property

Caused by psm_relocate_adjust_rep (TRANSFORM Sum(psm_relocate_receive_remove_rep.adjust_units) AS adjust_units SELECT psm_relocate_receive_remove_rep.art_clinic_obs_id, Sum(Nz([adjust_units],0)) AS adjust_units_net FROM psm_relocate_receive_remove_rep GROUP BY psm_relocate_receive_remove_rep.art_clinic_obs_id PIVOT psm_relocate_receive_remove_rep.adjust_cat; )

USE [HIVData-live]
GO

/****** Object:  View [dbo].[view_code_hfacility_distances]    Script Date: 10/24/2017 12:56:53 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

						 CREATE VIEW [dbo].[view_code_hfacility_distances_2]
						 AS
						 
SELECT        
a.ID as from_id, a.hfacility_name as from_name,
b.ID AS to_id, b.hfacility_name as to_name,
iif(
  (not(a.gps_x is null) and not(a.gps_y is null) and not(b.gps_x is null) and not(b.gps_y is null)),
  round(geography::Point(a.gps_x, a.gps_y, 4326).STDistance(geography::Point(b.gps_x, b.gps_y, 4326)) / 1000,0),
  -1)
as dist_km					

FROM dbo.code_hfacility AS a CROSS JOIN dbo.code_hfacility AS b

WHERE a.voided=0 and b.voided=0
						

GO

subdata sheet (similar to issues and consumption) select / bookmark and requery distance calcs

display transactions on demand as a subform select quarter is the base quarter / everything that occured after that date

Facility proximity tool / Stocks & Transactions I’ve spent now a good day to work on the facility proximity tool for ‘Stock & Transactions’ to get the distance for a selected base facility displayed. The initial idea of having a simple (continuous) subform with a grid like the original query psm_site_stock_consumpt_adjust plus a column for the distance isn’t straight forward. This is mainly because a cross tab (transform) query is used (among others also psm_relocate_adjust_rep). And Access doesn’t allow the (static) creation of a subform for these pivots.

I’ve played with a couple of different approaches (like a always visible) popup window for the base facility selection, but I’m not satisfied with this.

If I want to stick to the initial idea, the most promising approach seems to be the (semi-) dynamic creation of a continuous form where the field (and their names) are dynamically adjusted/created. This way the form could be bound to the dynamic column behaviour of the query. This is only necessary for columns like DL34_receipt, DL35_receipt, DL36_receipt, DL37_transits (they vary and depend on the selected quarter).

But before going this path (which should work according to my web research, but increase the implementation complexity), I wanted to get your thoughts on this. Maybe there is another way to achieve the same thing? Maybe I’m overcomplicating stuff?

Support for multiple IPs for one service potentially needed

Currently partners are concepts (Facility attribute - supporting partner)

Used only in quarterly data export (from query builder); maybe a separate export is more suitable. Currently all program report exports are copy/pasted into one XLS with different sheets and then handed over to PEPFAR.

Data is fed into datem (pepfar reporting system)

mapping table to define associations between

• facility (code_hfacility?)
• service (SELECT concept.ID, concept.concept_name FROM concept WHERE (((concept.concept_ID_parent)=293)) ORDER BY concept.concept_name;)
• quarter (code_year_quarter)
• implementing partner (IP) (Concepts - Facility Attributes - Supporting Partner Organization: SELECT concept.ID, concept.concept_name FROM concept WHERE (((concept.concept_ID_parent)=1629)) ORDER BY concept.concept_name)

Mapping Implementing Partners Just for Export from Query Builder Group by IP (similar to Quarters, Sub groups, Periods)? Aggregation level

map_data_element_dashboard for PIU dashbaord

art patients in transit (query builder) concept art program - cohort analysis

calc data element model in its own table

The build-in Charting engine of Access lacks some user interactivity. They are mostly static with the exception of popups for parameter input prior to the generation of the chart. Allowing the chart to be redefined by the user, e.g. changing parameters or adding additional data points isn't readily available.

Ideally DHAMIS could have a graphical charting way (similar to the query builder) where the user can add all possible data elements to the blank chart as needed.

Executive summary: No perfect alternative to the way Access handles charts is available. Using Office-external tools won't expose the full set of (more sophisticated) DHAMIS queries. Therefore to some degree this logic would need to be recreated, either in the external tool or in MSSQL server. Also additional technical knowledge needs to be built. Given the already existing technical capacity of MS Office at DHA, a closer integration of Access and Excel seems reasonable. However it will also require additional development efforts as the default integration isn't working to the degree it is desirable. Also an improved user interactivity will be more difficult to achieve.

Upfront: If an integration is not done natively (e.g. through an ActiveX component or Access addin), external programs often use a generic way to pull data from Access databases. But it appears that the following query structures are problematic:

• queries with parameters
• more complex nested structure
• advanced formulas (Access functions)
• Outer joins
• Crosstabs

To some degree there seems to be a workaround with an additional Access ODBC driver from MS. The default Office 365 installation only allows Office applications to talk to each other. For external applications an additional Office runtime -AccessDatabaseEngine- needs to be downloaded and install from MS). But this seems to have not been updated since the release of Access 2010, so the future direction is unclear. Plus even with this dedicated Access ODBC driver not all Access queries are visible.

The SSRS run as an addon of the MSSQL server (up to MSSQL 2016, from MSQL 2017 onwards it is a separate install) and therefore have access to the DHAMIS backend.

"Microsoft SQL Server Reporting Services (SSRS) is an enterprise reporting platform supporting traditional paper-based reports and interactive reports delivered over the Web or through custom applications. " (https://technet.microsoft.com/en-us/library/cc526677.aspx). It comes with Web-based view to see reports. This could be embedded back into an Access form through a Web browser control.

It also includes a graphical Report Designer. Access report are supposed to be 'importable' by SSRS (via SSDT - MS SQL Server Data Tools?). The assumption is that again the more sophistcated (nested) queries of the frontend won't be easily importable. And linking via ODBC straight to the Access frontend is possible, but won't reveal its full feature set either.

Some MSSQL licenses already include the SSRS; to be verified if DHA already has a proper/full version of this.

PowerBI is (mostly) a cloud-only service. With Power BI Desktop a local Windows installation is possible where Power BI Desktop reports can be shared so users can view and interact with them. the cloud-service needs to be subscribed on a per user basis.

Differences between PowerBI and SSRS: SSRS is meant for "static" reports. Something that will be shown mostly as pre-defined. It's great for reports that should be printed. It usually requires some technical skill when modifying reports. Power BI is intended more to "power users" as well as developers. It can create dashboards with great presentation. It can be easier to create visualizations with correct data, but it might not be great if you want to print something. It's basically the evolution of Powerpivot and Powerview. (https://www.sqlservercentral.com/Forums/1782354/What-is-the-difference-between-SSRS-and-Power-BI?PageIndex=%5B0%5D)

On premise installation of PowerBI. Built on top of SSRS and can pull data from multiple data sources; but for DHAMIS the MSSQL installation is the most relevant. With a superset of features compared to SSRS, but also with additional licensing.

• https://blogs.msdn.microsoft.com/sqlrsteamblog/2017/10/02/sql-server-2017-reporting-services-now-generally-available/
• (https://whitepages.unlimitedviz.com/2017/09/sql-server-reporting-services-vs-power-bi-report-server-whats-the-difference/)

Everybody can install and use a local copy of PowerBI Desktop where analysts and others can create powerful data connections, models and reports: https://docs.microsoft.com/en-us/power-bi/desktop-getting-started

Get Data of PowerBI has same problems like Excel (in fact they may even share the same Query Builder engine). Retrieving more sophisticated Access queries doesn't seem to work. No definitive list of which Access features and functions will break the visibility of queries in PowerBI. Therefore the value of connecting PowerBI Desktop straight to the Access frontend with its 'business logic' doesn't give access to all queries.

Creating an ODBC connection to the Access database (might/will require the Access 2010 (redistributable?) to link an ACCDB to an ODBC datasource) and importing the Access DB through this ODBC connection will make more (all?) queries show up. Most likely also within Excel. But some queries (e.g. art_reg_mort_trend) still fail to load with the message "DataSource.Error: The table has no visible columns and cannot be queried.". Maybe related to the query parameters; others without parameters (like report_site_data_temp) are now usable. But even when it works, LookupColumns are not respected and only the IDs are shown. So this logic would need to be replicated.

All of them with a rather hefty price tag; as in a few dozens to hundreds US-$ per month and user; not further evaluated):

• Crystal Reports: Newer versions of Crystal Reports don't have an ActiveX component anymore (to be verified)
• Cognos Analytics
• Qlik Sense
• Sisense
• Tableau

NEW feature: choose between appending and aggregating reports from query builder by sub groups (currently always appended) and periods (currently always aggregated)

Charts in Access 2016 still look '95-ish'. Investigate in alternative charting engine (Excel, PowerBI, ...). Intention: Leverage existing Access query logic, not duplicate this outside in another environment.

Idea: Charts in Excel are visually more appealing than in Access, therefore trying to leverage this. In general 2 approaches possible:

Recommended way is via "Data - Get Data - From Access DB". But this only shows a few and not all queries. No clear documentation why this happens. But it appears that the following query structures are problematic:

• queries with parameters
• more complex nested structure
• advanced formulas (Access functions)
• Outer joins
• Crosstabs

My assumption is that the Data import of Excel tries to parse and execute queries by itself, rather than relying on Access for this. Hence not every feature of Access is accessible from Excel, but only the ones the query feature of Excel understands on its own.

More promising will be a custom export (dump) of recordsets from Access to Excel via VBA. It shouldn't be too difficult to have a mostly generic VBA procedure pushing data out to either an Excel sheet created on the fly or into a pre-existing Excel template. This would keep the 'full business logic' of DHAMIS in Access. A pre-defined XLS template with pre-defined charts could also help to more easily adjust the look and feel of the charts for specific needs. E.g. a XLS template with one sheet per DHAMIS chart can be used to be updated with the most recent data from DHAMIS. With the downside that now DHAMIS isn't self-contained anymore; the user will jump between Access and Excel.

Note: DoCmd.TransferSpreadsheet (DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "report_site_data_temp", "hallo.xls") doesn't respect LookupColumns or Column Headers. Unlike the Export to XLS from the Ribbon.

If above 'remote control' of Excel isn't desirable, then Excel objects (here mainly charts) can be linked back into Access (via Forms). Basically Excel objects are linked via Windows OLE in. This will allow DHAMIS to continue to use Access as the main frontend while 'under the hood' utilizing Excel and its charting features.

The technical process is to Embedded Unbound Object Frame in an Access Form and basically (OLE?) Link to an Excel file and have its content displayed in the Frame of the Access Form. At least during re-opening of the Form the most recent data gets pulled into Access.

While technically possible, it is unclear how smooth and efficient such an automated process 'from Access to Excel and back' is. It would require additional prototyping if seemed that this is a reasonable approach.

A VBA template to push data to XLS: http://etutorials.org/Microsoft+Products/access/Chapter+12.+Automation/Recipe+12.6+Send+Access+Data+to+Excel+and+Create+an+Excel+Chart/

Open questions:

• Printing
• Dynamic resizing of chart and/or Form

program fnction deliver on polynominal regression maybe similar to interactive charts, to e.g. plot past data points for e.g. a specific regimen and apply a (modifiable) regression

1. projection for total alive and on treatment
2. binary forecast for every regimen group

art_projection_regimen_forecast . XLS ARV_quant_2014_guidelines

binary groups are already started in cponcept dic

forecast in sep table

1. regimen_trend -> total_alive_trend
2. aRV_quant_2014_guidelines -> with 4 panes

quatnfication rules map patient groups binary hierarchy groups in concepts table for forecast pricing for supply_items (through add table)

SaveAsText acForm, "Homepage", "C:\Users\IEUser\Desktop\Homepage.form"

Currently the binary DB files are approx 3 GB. The assumption is that most of its contents are not changing. So instead of transferring the whole content of the files in order update a local offline DB installation, a partial sync (like rsync) might speed up the update process. The default tool robocopy (part of every Win7+ installation) doesn't seem to do such syncs. An additional tool is needed and investigated if this speeds up the process.

E.g. by including/maintaining the short DHAMIS username in ActiveDirectory

Extending the AD schema appears heavyweight. Plus there doesn't seem to be a generic place where to edit such custom attributes (at least not in the DHA default AD configuration). So it seems as if 'creatively' reusing an existing AD attribute like the fields Initials is the easier option. As most attributes are not sync'ed to the local login profile, a direct AD (LDAP) query helps to retrieve these attributes.

Sub getShortusername()
    Set objad = CreateObject("ADSystemInfo")
    Set objUser = GetObject("LDAP://" & objad.username)
    Debug.Print objad.username
Debug.Print CurrentDbUser()

End Sub

Function queryADInitials(username As String)
    Dim initials As String
    
    Set rootDSE = GetObject("LDAP://RootDSE")
    base = "<LDAP://" & rootDSE.Get("defaultNamingContext") & ">"
    'filter on user objects with the given account name
    fltr = "(&(objectClass=user)(objectCategory=Person)" & _
        "(sAMAccountName=" & username & "))"
    'add other attributes according to your requirements
    attr = "initials"
    scope = "subtree"

    Set conn = CreateObject("ADODB.Connection")
    conn.Provider = "ADsDSOObject"
    conn.Open "Active Directory Provider"

    Set cmd = CreateObject("ADODB.Command")
    Set cmd.ActiveConnection = conn
    cmd.CommandText = base & ";" & fltr & ";" & attr & ";" & scope

    Set Rs = cmd.Execute
    Do Until Rs.EOF
    '  Debug.Print Rs.Fields("distinguishedName").value
        initials = Rs.Fields("initials").value
        Rs.MoveNext
    Loop
    Rs.Close
    conn.Close
    queryADInitials = initials
End Function

https://stackoverflow.com/questions/21110232/getting-ad-details-based-on-username

Currently dedicated 6 chars long shortnames are used to created by and updated by columns. Potentially changing these to 1 or 2 bytes ints could reduce the DB size.

Overall minor improvements, e.g. between 10 to 20 % could be observed. But as the adhoc performance tests where done in active live installation, there are most likely additional confounding factors; most likely leading towards a higher variance of the old live server.

Generic Query Builder Export: same / same ART Graphs and figures: similar expect for 6 (pregnant & breast feeding) 3:30 vs 4:45 and 6:30

changes to staff from previous supervision don't changes prevent changes to query design order of districts in query builder scripting of pulling latest live db to offline analyse - more tables - remove HTC report psm_DL_site_sheet_item_report, psm_DL_export lost captions and lookup columns cancel from queries

roles when offline?

Analyse More tables - ART reg in pub priv sector List / Schedules - HTC inventory

publish frontend - enable to work with accdbs and version tag in the name, so that this can be used for the basis of new versions

openqry_check_diflucannmn

file size of DB