# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
# # # #                                                                       # # # #
# # #                            V Q  D B  U T I L S                            # # #
# # # #                                                                       # # # #
# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
#   V Q D B U T I L S  - S C E N A R I O S    #  PS-Script Library  #  01 04 2022   #
# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
#
# VqDbUtils V1.0.0.265 : 10/01/2022 
# VqDbUtils V1.0.0.284 : 01/04/2022
# VqDbUtils V1.0.0.295 : 19/09/2022
#
# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
# # # #     S C R I P T S     # # # # # # # # # # # # # # # # # # # # # # # # # # # #
# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # 
# 
# == == == Database koppelen # #
# == == == Verwijderde foto's terughalen # #
# == == == foto's Type DOC verwijderen # #
# == == == Patientenlijsten maken # #
# == == == Dump Patienten Info # #
# == == == Exporteer de patientnummers (INTERNE PATIENTNUMMERS) # #
# == == == Hernummer patienten EXTERNE NUMMERS # #
# == == == Zoek alle zwart-wit foto's = Intra X-Ray =
# == == == Zoek alle zwart-wit foto's = OPG = (IMP = OPG in VQ)
# == == == Zoek alle zwart-wit foto's = Ceph =
# == == == Zoek alle zwart-wit foto's = BWR / BWL=
# == == == Set C.B.G waardes
# == == == Set Filter waardes (0-4, off,A-D)
# == == == Set BWR / BWL to BWR1 / BWL1
# == == == Verwijder patienten
# == == == Verwijder LEGE patienten
# == == == Compare images (equals or left side orphans)

# Patienten ID's aanpassen naar 30 Char : 
.\VqDbUtils.exe RunScript /scriptfn="scripts\ExpandPatientIDs.pas"

# Patienten ID's aanpassen Toevoegen CPR 'min'-teken (10 > 6-4) (DK/ZW/NO): 
.\VqDbUtils.exe RunScript /scriptfn="scripts\AddCprDash.pas"

# Patienten ID's aanpassen Toevoegen nullen aan PatIds tot 6 Char  (NL) : 
#   PrependWidth=6;    ==>> Line 2 : Aanpassing aantal characters 
.\VqDbUtils.exe RunScript /scriptfn="scripts\AddLeadingZeros_6.pas"

# Patienten ID's aanpassen CPR aanpassen > 6-4  (DK/ZW/NO) : 
.\VqDbUtils.exe RunScript /scriptfn="scripts\FixCprid.pas"

# Patienten ID's aanpassen 'min'-tekebn verwijderen (6-4 > 10) (DK/ZW/NO): 
.\VqDbUtils.exe RunScript /scriptfn="scripts\RemoveCprDash.pas"

# Patienten ID's aanpassen verwijderen nullen van PatIds (NL) : 
.\VqDbUtils RunScript /scriptfn="scripts\RemoveLeadingZeros.pas"

# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # 
# #    End-Of-Block : SCRIPTS   # # # # # # # # # # # # # # # # # # # # # # # # # # # 
# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # 

# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
# FOR HELPSCREEN SCROL TO THE BOTTOM
# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
# INDEX


















#
# == == == Database koppelen # #
# == == == Verwijderde foto's terughalen # #
# == == == foto's Type DOC verwijderen # #
# == == == Patientenlijsten maken # #
# == == == Dump Patienten Info # #
# == == == Exporteer de patientnummers (INTERNE PATIENTNUMMERS) # #
# == == == Hernummer patienten EXTERNE NUMMERS # #
# == == == Zoek alle zwart-wit foto's = Intra X-Ray =
# == == == Zoek alle zwart-wit foto's = OPG = (IMP = OPG in VQ)
# == == == Zoek alle zwart-wit foto's = Ceph =
# == == == Zoek alle zwart-wit foto's = BWR / BWL=
# == == == Set C.B.G waardes
# == == == Set Filter waardes (0-4, off,A-D)
# == == == Set BWR / BWL to BWR1 / BWL1
# == == == Verwijder patienten
# == == == Verwijder LEGE patienten
# == == == Compare images (equals or left side orphans)

#
#
#
#
# == == == Database koppelen # #
#
# MSSQL database koppelen Basis config
.\vqdbutils.exe WriteSqlDbCfg /out=sqldb.cfg /server="server\services" /database=database_naam
# uitgebreidde configuratie : 
.\vqdbutils.exe WriteSqlDbCfg /out=sqldb.cfg /Filesroot="\\server\fileshare" /server="server\services" /database=database_naam /authentication="sqlserver" /user="dba" /password="password"
# het bestand SQLdb.cfg is het [SqlServerDac]-deel en het [Dbase]-deel uit de storageEngine.INI


get-content sql_db.cfg |Out-GridView   # MS-SQL Database configuratie 

#
# paradox database koppelen
.\vqdbutils.exe WriteParadoxDbCfg /out=pdx_db.cfg /dbpath=d:\visidata\shareddata\pdx
get-content pdx_db.cfg |Out-GridView   # PARADOX Database configuratie 
#
# Firebird database koppelen
.\vqdbutils.exe WriteFirebirdDbCfg /out=fb_db.cfg /server="convertor/30500" /database="J:\Temp\CVT -a- Not test-converted\CVT#2978 Peerboom NovaX\Firebird SQL\VISIQUICK.FDB"
.\vqdbutils.exe WriteFirebirdDbCfg /out=fb_db.cfg /server="127.0.0.1/30500" /database=D:\Citodent\Les\Firebird\VISIQUICK.FDB
.\vqdbutils.exe WriteFirebirdDbCfg /out=fb_db.cfg /server="127.0.0.1/30500" /database=D:\Conversie\current\TEST\CVT#2246_VISIQUICK.FDB
get-content fb_db.cfg    # FIREBIRD Database configuratie 
#
# =============================================================================

# 
# == == == Verwijderde foto's terughalen # #
.\VqDbUtils.exe UndeleteImage /db=fb_db.cfg /clinic=1 /patientlink=509232 /vnum=4

# ==
#

#
# == == == Patientenlijsten maken # #
#
# Maak een renum list INTERNE NUMMERS
.\VqDbUtils.exe patrenumberlist /db=fb_db.cfg /dentid=1 /inc=0 >patrenum.txt
# Lees de informatie uit 
get-content patrenum.txt |Out-GridView   # patientnummers HERNUMMEREN oud-nieuw
# ==
# Exporteer de patientnummers (ZICHTBAAR IN ZOEKLIJST)
.\VqDbutils.exe dumppatientIDlist /db=pdx_db.cfg /out=patientnrlist.txt
.\VqDbutils.exe dumppatientIDlist /db=fb_db.cfg /out=patientnrlist.txt
.\VqDbutils.exe dumppatientIDlist /db=sqldb.cfg /out=patientnrlist.txt
.\VqDbutils.exe dumppatientIDlist / dentist=1 /out=patientnrlist.txt



#
# Lees de informatie uit 
get-content patientnrlist.txt |Out-GridView   # patientnummers HERNUMMEREN oud-nieuw
# ==
#

# Dump Patienten Info
.\VqDbutils.exe dumppatientlist /db=pdx_db.cfg /out=patientdumplist_afterdelete.txt
# Lees de informatie uit 
get-content patientdumplist.txt |Out-GridView   
# ==
#

# Exporteer de patientnummers (INTERNE PATIENTNUMMERS)
.\VqDbutils.exe dumppatientlist /db=fb_db.cfg /out=patientlist.txt
.\VqDbutils.exe dumppatientlist /db=pdx_db.cfg /out=patientlist.txt
#
# Lees de informatie uit 
get-content patientlist.txt |Out-GridView   # patientnummers HERNUMMEREN oud-nieuw
# ==
# =============================================================================

#
# Hernummer patienten EXTERNE NUMMERS
# /fn [/db] [/clinicid] [/newidfirst] [/stripzeros] [/fromdt] [/todt]
# /fn                           filename of translation list (srcid,dstid))
# /db                           Database configuration file of database
# /clinicid                     clinic ID (default 1)
# /newidfirst                   New patient id first in translation list (default false)
# /stripzeros                   Strip leading zeroes of patient id's in translation list (default false)
# /fromdt                       From date/time yyyy-mm-dd hh:nn:ss (default empty)
# /todt                         To date/time yyyy-mm-dd hh:nn:ss(default empty)
# 
# === # kan niet onder powershell # === #
# INP # : vereist bevestiging met 'Y'  (copy con yes <= Y<= Ctrl-Z)
# CMD # : VQDbUtils MergePatients /db=fb_db.cfg /Fn=PatRenumList.csv


#
# Zoek alle zwart-wit foto's = Intra X-Ray =
.\VqDbutils.exe FindClinicImages /db=pdx_db.cfg /out=images_xr.txt /gray /pocketindex=xr
#
# Zoek alle zwart-wit foto's = PAN / OPG = (IMP = OPG in VQ)
.\VqDbutils.exe FindClinicImages /db=pdx_db.cfg /out=images_imp.txt /gray /pocketindex=imp
.\VqDbutils.exe FindClinicImages /db=pdx_db.cfg /out=images_imp.txt /gray /toothpos=pan
#
# Zoek alle zwart-wit foto's = Ceph =
.\VqDbutils.exe FindClinicImages /db=fb_db.cfg /out=images_Ceph.txt /clinic=1 /gray /pocketindex=ceph
#
# Zoek alle zwart-wit foto's = BWR / BWL=
.\VqDbutils.exe FindClinicImages /db=fb_db.cfg /out=images_bwr1.txt /clinic=1 /gray /toothpos=bwr
.\VqDbutils.exe FindClinicImages /db=fb_db.cfg /out=images_bwl1.txt /clinic=1 /gray /toothpos=bwl

.\VqDbutils.exe FindClinicImages /db=pdx_db.cfg /out=images.txt /clinic=1
#
get-content images.txt # |Out-GridView   # OUTPUT : Image manipulation


# Set C.B.G waardes
.\VqDbutils.exe PatchImageProps /db=fb_db.cfg /images=images.txt /brightness=25 /contrast=54 /gamma="-0.5"

# Set Filter waardes (0-4, 0=off,1=A,...,4=D)
.\VqDbutils.exe PatchImageProps /images=images.txt /superfilter=1

# Set BWR / BWL to BWR1 / BWL1 
.\VqDbUtils PatchImageProps /images=images_bwr1.txt /toothpos=bwr1
.\VqDbUtils PatchImageProps /images=images_bwl1.txt /toothpos=bwl1

#
# Verwijder patienten
.\VqDbutils.exe deletepatients /db=fb_db.cfg /patients=patientdellist.txt
.\VqDbutils.exe deletepatients /db=pdx_db.cfg /patients=patientdellist.txt
# Verwijder lege patienten
.\VqDbUtils.exe DeleteEmptyPatients /db=fb_db.cfg /clinicid=1

# Verwijder Alle patienten en een Clinic
.\vqdbutils.exe dumppatientidlist /dentid=2 /out=ClinicPatList_2.lst
.\vqdbutils.exe deletepatients /clinicid=2 /patients=c:\temp\ClinicPatList_2.lst 
.\vqdbutils.exe deleteclinic /clinicid=2



.\VqDbUtils.exe RemoveDeletedImages /db=fb_db.cfg


# Compare images (equals or left side orphans)
vqdbutils compareimages /out [/db] /dbother [/clinic] [/type] [/mode]

/out                          Filename of image id's output file (with images id's from /db)                                      
/db                           Filename of database configuration (left side)    
/dbother                      Filename of database configuration (right side)   
/clinic                       ID of clinic (default 1)                          
/type                         Compare type: 'equals','leftorphans' (default 'equals')                                             
/mode                         Compare mode: 'guid' , 'time' (Created date), 'id' , 'vnum' Default: 'guid')                         


VqDbutils WriteFirebirdDbCfg /out=fbdb_CL.cfg /server="localhost/30500" /database="d:\VisiData\FirebirDb\Visiquick.FDB"
VqDbutils WriteFirebirdDbCfg /out=fbdb_CR.cfg /server="localhost/30500" /database="d:\VisiData\FirebirDb\Visiquick.FDB"
VqDbutils CompareImages /out=imagelist.txt /db=fdb_CL.cfg /dbother=fbdb_CR.cfg /clinic=1 /type=leftorphans




#
# Lees de informatie uit 
get-content patientnrlist.txt | Out-GridView    # patientnummers (ZICHTBAAR IN ZOEKLIJST)
get-content patientlist.txt  | Out-GridView     # patientnummers (INTERNE PATIENTNUMMERS)
get-content patientdellist.txt  | Out-GridView  # patientnummers TE VERWIJDEREN

# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
#                                                                                   #
#                                   P R O C E D U R E S                             #
#                                                                                   #
# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #

# => Verwijder (een groep) patienten
#   ============================================================================
#   0) Koppel de database
#   1) run commando : Exporteer de patientnummers (ZICHTBAAR IN ZOEKLIJST)
#   2) pas deze aan zodat je alleen de patienten overhoud die verwijderd moeten worden. 
#   3) Sla op onder een andere naam : patientdellist.txt
#   4) Run commando Verwijder patienten
#   ============================================================================


# => Hernummer (een groep) patienten
#   ============================================================================
#   0) Koppel de database
#   1) run commando : Exporteer de patientnummers (ZICHTBAAR IN ZOEKLIJST)
#   2) kopieer dit bestand naar een CSV-bestand en lees in in EXCEL
#   2) pas deze aan met in de 2e kolom de NIEUWE EXTERNE patientnummers
#   3) Sla op onder een andere naam : PatRenumList.csv
#   4) Run commando Hernummer patienten EXTERNE NUMMERS
#   ============================================================================


# Scenario : Verander de B- en C- en G-waardes van foto's
# 1) zoek de gemiddelde waarde. 
# neem enkele oude, maar 3/4 laatst genomen. (ivm huidige instellingen X-Ray Tube / Scanner)

# 2) zoek alle ZW afbeeldingen Intra en BW
VqDbUtils.exe FindClinicImages /db=fb_db.cfg /out=images_xr.txt /gray /pocketindex=xr
VqDbUtils.exe FindClinicImages /db=fb_db.cfg /out=images_bw.txt /clinic=1 /gray /toothpos=bw

# 3) pas alle ZW afbeeldingen Intra en BW aan naar de gevonden waarden
# reset to Default : VqDbutils.exe PatchImageProps /images=images_bw.txt /brightness=50 /contrast=50 /gamma="0"
VqDbutils.exe PatchImageProps /db=fb_db.cfg /images=images_bw.txt /brightness=65 /contrast=65 /gamma="0.5"
VqDbutils.exe PatchImageProps /db=fb_db.cfg /images=images_xr.txt /brightness=65 /contrast=65 /gamma="0.5"



# => Vergelijk de patienten tussen een Paradox en een Firebird database na conversie
#   ===========================================================================
#   ## ==>>  Vergelijking op EXTERN ID, na omnummering NIET bruikbaar.  <<== ##
#   ===========================================================================
Clear-Host
# Defieer de I/O Text files
$PX_pat = "patientdumplist_Paradox.txt"                  # Paradox Pat-list
$FB_pat = "patientdumplist_Firebird.txt"                 # Firebird Pat-list
$pdx_db = "d:\visidata\shareddata\pdx"                   # Paradox DB
$fb_db = "D:\VisiData\les\Firebird\visiquick.FDB"        # Firebird DB
$view_title = $PX_pat + " < = > " + $FB_pat              # zet de view titel
#
# Paradox db-file
Remove-Item pdx_db.cfg
.\vqdbutils.exe WriteParadoxDbCfg /out=pdx_db.cfg /dbpath=$pdx_db
get-content pdx_db.cfg    # PARADOX Database configuratie 
# Firebird db-file
Remove-Item fb_db.cfg
.\vqdbutils.exe WriteFirebirdDbCfg /out=fb_db.cfg /server="127.0.0.1/30500" /database=$fb_db
get-content fb_db.cfg    # FIREBIRD Database configuratie 
#
# = = = = = = = = = = 
# Dump Patienten Info
.\VqDbutils.exe dumppatientlist /db=pdx_db.cfg /out=patientdumplist_Paradox.txt
# Dump Patienten Info
.\VqDbutils.exe dumppatientlist /db=fb_db.cfg /out=patientdumplist_Firebird.txt
#
# = = = = = = = = = = 
# Lees de inhoud in
$strReference = Get-Content $PX_pat
$strDifference = Get-Content $FB_pat
#
# = = = = = = = = = = 
# vergelijk de inhoud
Compare-Object $strReference $strDifference |Out-File VQ_Conv_Pdx-Fb_Compate_Patients.txt
Get-content VQ_Conv_Pdx-Fb_Compate_Patients.txt |Out-GridView -Title $view_title
#   ============================================================================

# => 
#   ===========================================================================
#   ## ==>>                                                             <<== ##
#   ===========================================================================
# Script 
#   1) Omschrijving
#   2) Omschrijving
#   3) Omschrijving
#   4) Omschrijving
#   ============================================================================


# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
# #                      E N D   O F   P R O C E D U R E S                        # #
  # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # 



# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
#                                                                                   #
#                           H E L P S R C E E N   U T I L I T Y                     #
#                                                                                   #
# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
# DO NOT RUN ANY LINE BELOW                                                         #
# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #

VqDbUtils.exe FUNCTIONNAME  V1.0.0.321

AnalysePhotoTable             Analyse photo table (Paradox database only)       
AnalysePhotoTables            Analyse photo tables (Paradox database only)      
CheckPdxFilesHeader           Check directory for Db/Mb file headers            
ClearImage                    Clears image data, but does not delete the image (
                              For testing purposes)                             
CompareImageLists             Compare image lists                               
CompareImages                 Compare images (equals or left side orphans)      
CompareReplication            Compare images of 2 replication servers           
ConvertDatToTiff              Convert Remote X-Ray image to Tiff                
CopyVqInfo                    Copy VqInfo Records (Paradox database only)       
CreateMissingPatients         Create missing patients                           
DeleteClinic                  Delete clinic                                     
DeleteEmptyPatients           Delete empty patients                             
DeleteImage                   Delete image without adding it to deleted image gu
                              id list                                           
DeleteImages                  Delete images                                     
DeleteOldNci3DImages          Deleting old files (created by RewriteNci3DImages)
DeletePatientRecord           Delete patient record (no images)                 
DeletePatients                Delete patients                                   
DoConversion                  Convert VisiQuick database                        
DumpDcmTags                   Dump Dicom file tags                              
DumpImageBlob                 Dumps image blob to file                          
DumpImageList                 Dump image list to file                           
DumpImgInfo                   Dump image info                                   
DumpInfoFieldsStream          Dump contents of a image info record Ext blob     
DumpPatFieldsStream           Dump contents of a patient record Ext blob        
DumpPatInfo                   Dump patient info                                 
DumpPatientIdList             Dumps patient ID list of a clinic                 
DumpPatientList               Dumps patient list of a clinic                    
DumpRemotePatList             Dump remote patient list                          
DumpTiffTags                  Dump Tiff tags                                    
DumpVqdFile                   Dump contents of a Vqd file                       
ExpandPhotoTables             Expand photo tables (Paradox database only)       
FilterRenumberList            Filter patient renumber list                      
FindClinicImages              Find clinic images and write to file (PatientID,Ph
                              otoID)                                            
FixCtFolderNames              Rename/merge CT patient folders                   
GetVqExeFn                    Get VisiQuick exe filename from registry          
InsertedImagesToImageList     InsertedImages to Image list (VqDbConvert)        
MergeImageLists               Merge image lists                                 
MergePatients                 Merge patients (inline)                           
MergeRenumberList             Merge patient renumber list (doubles)             
PatRenumberList               Generate patient renumber list                    
PatchImageProps               Patch image properties                            
PatchPhotoTable               Patch patient photo table number (Paradox database
                               only)                                            
PatchPhotoTables              Patch patients photo table number (Paradox databas
                              e only)                                           
PatchSavedViews               Patch patient saved views                         
RebalancePhotoTable           Rebalance photo table (Paradox database only)     
RemoveDeletedImages           Remove deleted images                             
RenumberFDB                   Renumber FDB folder                               
ResizeImagesFixed             Resizing images according to resize rules         
RestoreDamagedDb              Copy patient and/or image properties from source t
                              o dest DB                                         
RewriteNci3DImages            Rewrite Nci 3D images                             
RewriteOwandy3DImages         Rewrite Owandy 3D Images                          
RunScript                     Run script                                        
SavePvFileToXml               Save persistent values file to XML                
SetImageGuids                 Set image guids when empty                        
StripPatientLinkSpaces        Strip spaces from patient link OD                 
TestOpenPatients              Opens a patient or all patients of a clinic       
UndeleteImage                 Undelete image                                    
WriteConversionCfg            Saves DB conversion configuration to file         
WriteFirebirdDbCfg            Save Firebird DB configuration to file            
WriteParadoxDbCfg             Save ParadoxDb configuration to file              
WriteRestoreDamagedDbProfile  Create and initialize profile for RestoreDamagedDB
                               procedure                                        
WriteSqlDbCfg                 Save SqlDb configuration to file                  
WriteXdbCfg                   Save XDB DB configuration to file                 
        
# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #