DSM Labs Consistency check.accdb: Difference between revisions

(46 intermediate revisions by 2 users not shown)

Line 1:

The program

:: '''X:\CCMDB\DSM Labs Imports\DSM Labs Consistency check.accdb'''

:: '''{{S:\MED\MED_CCMED}}CCMDB\DSM Labs Imports\DSM Labs Consistency check.accdb'''

generates request extracts and imports lab data as provided by DSM.

Line 11:

== Log ==

=== changes for next version ===

* lock out sending of request if ~~any sent but not vetted records are present to make sure they are not missed; or send all <> incomplete?~~

nothing right now

* ~~fix auto email it says "Hi, \n \n here is a new data request file for the CCMDB export. Could you please generate the lab export and let us know when done? \n \n Thanks!"~~

<!-- if needed un-comment this following:

** ~~why would I change it and to what?~~

{{TT |

_DEV_DSM

* Next version:

** Nothing right now.

}}

end of comment-->

* '''Add a debug.stop after the new-~~lab~~-~~finding part so the import can just continue.'''~~

=== version 2023-09-27 ===

copied to server, notified Pagasa

* removed Trish from email and added Herman

* the error it ~~gives~~ for there already ~~existing a file needs more helpful text~~.

=== version 2023-05-17 ===

not rolled yet

* removed re-connect for PHI, shouldn't be needed any more and was causing error.

=== version 2023-01-12 ===

copied to server

* updated [[created_AdmitDtTm query]] so it won't crash when there are nulls in all three dates

=== version 2022-11-03 ===

* added some labs to mappings as per email from Allan. [[User:Ttenbergen|Ttenbergen]] 10:22, 2022 November 3 (CDT)

=== version 2022-08-29 ===

copied to server [[User:Ttenbergen|Ttenbergen]] 15:48, 2022 August 29 (CDT)

* fixed error that was introduced into an SQL statement in the most recent update. [[User:Ttenbergen|Ttenbergen]] 15:48, 2022 August 29 (CDT)

** specifically, a previous change to query 1_C_requester_cc_maker had added a "where" clause to that query, and VBA code in Sub Export_requestor_file() was trying to add another where clause; changed that to "and" and it fixed it. [[User:Ttenbergen|Ttenbergen]] 12:11, 2022 August 31 (CDT)

=== version 2022-07-07 ===

copied to server [[User:Ttenbergen|Ttenbergen]] 11:49, 2022 July 12 (CDT)

* added queryRunner to get better error management for queries

* updated compact_runner that wasn't working at all

* updated the command in VBA that generates the 1_D_format_limit data to use the new Admit_DtTm, it was still using the old format.

=== version 2022-06-09 ===

copied to server

* updated query ''import_request_matcher'' to do counts and filter to only 0s

* updated query ''1_cc_request_maker'' to only list vetted records

=== version 2022-06-01 ===

copied to server

* cleanup: deleted table ''TEMP'' and queries ''tmp_DSM_D_ID_fix_2020-10-01'', ''s_mapping_lab_updater1'' and ''s_group_updater''; renamed ''s_group_updater1'' to ''s_group_updater''

* Julie had raised a scenario where duplicate data might arrive in the file; confirmed that import process de-duplicates against import file rather than deleting duplicates after

* [[Change to replace Accept DtTm with first Service tmp entry, and Arrive DtTm with first Boarding Loc]]:

** General:

*** updated DB_Connect to also connect [[L TmpV2 table]], since we now need [[Boarding Loc]] and [[Service tmp entry]] to make [[Admit DtTm]]

*** copied in [[created_AdmitDtTm query]] and [[created_FirstService query]] from [[CFE]]

** requester:

*** updated query ''1_C_requester_cc_maker'' to use new Admit_DtTm instead of old Arrive_DtTm

*** confirmed that VBA for sending request doesn't need modification

** importer:

*** confirmed no changes should be required to importer

=== version 2020-09-30 ===

* updated the specs for the importer so it will work with the [[RecordStatus field]] added to requestor in last change. Ttenbergen 14:22, 2020 September 30 (CDT)

=== version 2020-08-13 ===

* Added [[RecordStatus field]] to the DSM requestor to query and file.--[[User:PTorres|PTorres]] 15:43, 2020 August 13 (CDT)

=== version 2020-06-04 ===

* fixed link to this page, it still had link to old page name

* changed import to start at [[Accept_DtTm]] instead of [[Arrive_DtTm]] as it had been; as per various discussions during [[Task Team Meeting - Rolling Agenda and Minutes 2020]]

=== version 2020-05-06 ===

* added "DAO.DBEngine.SetOption dbmaxlocksperfile, 15000" to prevent out-of-diskspace error [https://web.archive.org/web/20200224033850/http://www.accessrepairnrecovery.com/blog/fix-access-file-sharing-lock-count-exceeded-error] (archived version)

* changed query ''2_dedupe_appender_group'' to set DSM_TestCode to 0 rather than "not from group" for records arriving as test; this would have generated a hidden error all along but there would be no consequence to the data, this just cleans up that hidden error

=== version 2019-12-18 ===

* Added several labs as per Allan Garland, incl the ABG labs.

* fixed a bug from last time, re-imported queries I had misunderstood

** s_group_ListNewFound - used when labs first arrive to mark them with date

** s_mapping_lab_NewFound - used when labs first arrive to mark them with date

=== version 2019-12-09 ===

* added automatic reconnecting (on opening the following runs: module DB_Connect, function DB_Connect)

* removed old queries:

** s_group_ListNewFound (replaced by ''s_group_imported_today'')

** s_group_ListNewlyAdded (replaced by ''s_group_imported_today'')

** s_mapping_lab_NewFound (replaced by ''s_mapping_lab_today'')

** s_mapping_lab_ListNewlyAdded (replaced by ''s_mapping_lab_today'')

* improved error messaging when trying to run export a second time the same day, which breaks because it tries to create a directory which already exists.

=== version 2019-05-15 ===

Line 42:

Line 117:

local for now

* adding checks for locations and labs that have not been encountered before. Ran across different field lengths, emailed Alun.Ttenbergen 14:43, 2015 December 1 (CST)

== Related articles ==

[[Category:DSM Labs Extract]]

[[Category:DSM Labs Consistency check.accdb | *]]

@@ Line 1: / Line 1: @@
 The program
-:: '''X:\CCMDB\DSM Labs Imports\DSM Labs Consistency check.accdb'''
+:: '''{{S:\MED\MED_CCMED}}CCMDB\DSM Labs Imports\DSM Labs Consistency check.accdb'''
 generates request extracts and imports lab data as provided by DSM.
@@ Line 11: / Line 11: @@
 == Log ==
 === changes for next version ===
-* lock out sending of request if any sent but not vetted records are present to make sure they are not missed; or send all <> incomplete?
+nothing right now
-* fix auto email it says "Hi, \n \n here is a new data request file for the CCMDB export. Could you please generate the lab export and let us know when done? \n \n Thanks!"
+<!-- if needed un-comment this following:
-** why would I change it and to what?
+{{TT |
+_DEV_DSM
+* Next version:
+** Nothing right now.
+}}
+end of comment-->
-* '''Add a debug.stop after the new-lab-finding part so the import can just continue.'''
+=== version 2023-09-27 ===
+copied to server, notified Pagasa
+* removed Trish from email and added Herman
-* the error it gives for there already existing a file needs more helpful text.
+=== version 2023-05-17 ===
+not rolled yet
+* removed re-connect for PHI, shouldn't be needed any more and was causing error.
+=== version 2023-01-12 ===
+copied to server
+* updated [[created_AdmitDtTm query]] so it won't crash when there are nulls in all three dates
+=== version 2022-11-03 ===
+* added some labs to mappings as per email from Allan. [[User:Ttenbergen|Ttenbergen]] 10:22, 2022 November 3 (CDT)
+=== version 2022-08-29 ===
+copied to server [[User:Ttenbergen|Ttenbergen]] 15:48, 2022 August 29 (CDT)
+* fixed error that was introduced into an SQL statement in the most recent update. [[User:Ttenbergen|Ttenbergen]] 15:48, 2022 August 29 (CDT)
+** specifically, a previous change to query 1_C_requester_cc_maker had added a "where" clause to that query, and VBA code in Sub Export_requestor_file() was trying to add another where clause; changed that to "and" and it fixed it. [[User:Ttenbergen|Ttenbergen]] 12:11, 2022 August 31 (CDT)
+=== version 2022-07-07 ===
+copied to server [[User:Ttenbergen|Ttenbergen]] 11:49, 2022 July 12 (CDT)
+* added queryRunner to get better error management for queries
+* updated compact_runner that wasn't working at all
+* updated the command in VBA that generates the 1_D_format_limit data to use the new Admit_DtTm, it was still using the old format.
+=== version 2022-06-09 ===
+copied to server
+* updated query ''import_request_matcher'' to do counts and filter to only 0s
+* updated query ''1_cc_request_maker'' to only list vetted records
+=== version 2022-06-01 ===
+copied to server
+* cleanup: deleted table ''TEMP'' and queries ''tmp_DSM_D_ID_fix_2020-10-01'', ''s_mapping_lab_updater1'' and ''s_group_updater''; renamed ''s_group_updater1'' to ''s_group_updater''
+* Julie had raised a scenario where duplicate data might arrive in the file; confirmed that import process de-duplicates against import file rather than deleting duplicates after
+* [[Change to replace Accept DtTm with first Service tmp entry, and Arrive DtTm with first Boarding Loc]]:
+** General:
+*** updated DB_Connect to also connect [[L TmpV2 table]], since we now need [[Boarding Loc]] and [[Service tmp entry]] to make [[Admit DtTm]]
+*** copied in [[created_AdmitDtTm query]] and [[created_FirstService query]] from [[CFE]]
+** requester:
+*** updated query ''1_C_requester_cc_maker'' to use new Admit_DtTm instead of old Arrive_DtTm
+*** confirmed that VBA for sending request doesn't need modification
+** importer:
+*** confirmed no changes should be required to importer
+=== version 2020-09-30 ===
+* updated the specs for the importer so it will work with the [[RecordStatus field]] added to requestor in last change. Ttenbergen 14:22, 2020 September 30 (CDT)
+=== version 2020-08-13 ===
+* Added [[RecordStatus field]] to the DSM requestor to query and file.--[[User:PTorres|PTorres]] 15:43, 2020 August 13 (CDT)
+=== version 2020-06-04 ===
+* fixed link to this page, it still had link to old page name
+* changed import to start at [[Accept_DtTm]] instead of [[Arrive_DtTm]] as it had been; as per various discussions during [[Task Team Meeting - Rolling Agenda and Minutes 2020]]
+=== version 2020-05-06 ===
+* added "DAO.DBEngine.SetOption dbmaxlocksperfile, 15000" to prevent out-of-diskspace error [https://web.archive.org/web/20200224033850/http://www.accessrepairnrecovery.com/blog/fix-access-file-sharing-lock-count-exceeded-error] (archived version)
+* changed query ''2_dedupe_appender_group'' to set DSM_TestCode to 0 rather than "not from group" for records arriving as test; this would have generated a hidden error all along but there would be no consequence to the data, this just cleans up that hidden error
+=== version 2019-12-18 ===
+* Added several labs as per Allan Garland, incl the ABG labs.
+* fixed a bug from last time, re-imported queries I had misunderstood
+** s_group_ListNewFound - used when labs first arrive to mark them with date
+** s_mapping_lab_NewFound - used when labs first arrive to mark them with date
+=== version 2019-12-09 ===
+* added automatic reconnecting (on opening the following runs: module DB_Connect, function DB_Connect)
+* removed old queries:
+** s_group_ListNewFound (replaced by ''s_group_imported_today'')
+** s_group_ListNewlyAdded (replaced by ''s_group_imported_today'')
+** s_mapping_lab_NewFound (replaced by ''s_mapping_lab_today'')
+** s_mapping_lab_ListNewlyAdded (replaced by ''s_mapping_lab_today'')
+* improved error messaging when trying to run export a second time the same day, which breaks because it tries to create a directory which already exists.
 === version 2019-05-15 ===
@@ Line 42: / Line 117: @@
 local for now
 * adding checks for locations and labs that have not been encountered before. Ran across different field lengths, emailed Alun.Ttenbergen 14:43, 2015 December 1 (CST)
+== Related articles ==
+{{Related Articles}}
 [[Category:DSM Labs Extract]]
 [[Category:DSM Labs Consistency check.accdb | *]]