#!/bin/csh -f

# -------------------------------------------
# jrc-ihe
# This script is used to synchronize the Central Archive with the Gazelle database (to get the configurations of the systems SCU and SCP) :
#    - collect DICOM aet, hostnames, and ports
#    - insert them in the pacsdb database (table aet)
#
# Arguments :  NONE
#   example :  ./gazelle_sync_for_Central_Archive.vX.xxxx.csh
# -------------------------------------------

# -------------------------------------------
# Please check and modify the following variables:
# Local path to postgresql installation
 setenv POSTGRESQL /usr/lib/postgresql/9.1      # Set accordingly!
# Local database user name (Central Archive DB)
 setenv POSTGREUSER gazelle                     # Set accordingly!
# Central Archive database
 setenv PACS_DB pacsdb                          # Set accordingly!
# Central Archive database
 setenv PACS_AET_TABLE ae                       # Set accordingly!


# HOSTNAME_GAZELLE : provide here the FQN for the Gazelle system from which the data will be imported.

# BOCHUM
 setenv HOSTNAME_GAZELLE 172.16.0.10         # Set accordingly!
# Distant GAZELLE database name
 setenv POSTGREBASE_GAZELLE gazelle                 # Set accordingly!
# Distant GAZELLE database User
 setenv POSTGREUSER_GAZELLE gazelle                   # Set accordingly!
# Testing Session ID to synchronize with       # Set accordingly!
 setenv SESSION_ID 35


# RENNES 
# setenv HOSTNAME_GAZELLE kujira.irisa.fr         # Set accordingly!
# Distant GAZELLE database name
# setenv POSTGREBASE_GAZELLE ihe-europe-2010                 # Set accordingly!
# Distant GAZELLE database User
# setenv POSTGREUSER_GAZELLE gazelle            # Set accordingly!
# Testing Session ID to synchronize with       # Set accordingly!
# setenv SESSION_ID 25


# DO NOT MODIFY ANY LINE BELOW THIS ONE
#


# -------------------------------------------
# Shell - do not modify below


# Variables declaration
set DATE=`date +%y%m%d%H%M%S`
set DATE_LONG=`date "+%y-%m-%d %H:%M:%S"`
set total_errors=0

if ($#argv != 0) then
    echo "This shell needs none argument - launch :  ./gazelle_sync_for_Central_Archive.csh "
    exit
endif


# ###
# Collect of Dicom configuration informations
echo "";
echo "Collect from database $POSTGREBASE_GAZELLE in progress... "
echo "Hostname : $HOSTNAME_GAZELLE"






echo "--" >& gazelle_dicom_configuration_${DATE}.tmp
echo "-- Synchronization with PACS - Central Archive : List of all found AETITLES depending on actors" >>& gazelle_dicom_configuration_${DATE}.tmp
echo "-- Execution Date : $DATE_LONG" >>& gazelle_dicom_configuration_${DATE}.tmp
echo "--" >>& gazelle_dicom_configuration_${DATE}.tmp
echo "" >>& gazelle_dicom_configuration_${DATE}.tmp
echo "" >>& gazelle_dicom_configuration_${DATE}.tmp


 # ----------------------
 # First, we retrieve and insert in pacsdb :
 # SCU : 'STORAGE COMMITMENT' and 'MOD' - with id(STORAGE COMMITMENT)=6 and id(MOD)=16, id(EC)=18, id(IMPORTER)=71, id(RM)=8

echo "" >>& gazelle_dicom_configuration_${DATE}.tmp
echo "-- SCU : 'STORAGE COMMITMENT' and 'MOD' or 'EC' or 'IMPORTER' or 'RM' - with id(STORAGE COMMITMENT)=6, id(MOD)=16, id(EC)=18, id(IMPORTER)=71, id(RM)=8" >>& gazelle_dicom_configuration_${DATE}.tmp
$POSTGRESQL/bin/psql -U $POSTGREUSER_GAZELLE -h $HOSTNAME_GAZELLE $POSTGREBASE_GAZELLE \
   -c "SELECT dicom_scu.ae_title, host.hostname, dicom_scu.port, dicom_scu.transfer_role FROM tm_system_in_session sis, tm_dicom_scu_configuration dicom_scu, tm_host host, tm_configuration config WHERE config.system_in_session_id = host.system_in_session_id and config.host_id = host.id and sis.id = config.system_in_session_id and sis.testing_session_id = $SESSION_ID and config.id = dicom_scu.id and dicom_scu.sop_class_id = '6' and (config.actor_id = '16' or config.actor_id = '18' or config.actor_id='71' or config.actor_id='8') and config.is_secured IS FALSE and config.approved is TRUE ;" \
   | tail -n +3 | sed 's/ //g' | sed '$d' | sed '$d' | grep -v -e '---------' | awk -F'\|' '{printf("insert into ae (aet, hostname, port, ae_desc, installed) values (\047%s\047 , \047%s\047, \047%s\047, \047Imported From Gazelle\047, false );\n",$1, $2, $3)}' >>& gazelle_dicom_configuration_${DATE}.tmp
# We build a tmp-file with the request results rows. To get a valid format, we take off the 3 first lines, the 2 last lines, the spaces, and the lines which contain '---------'


 # ----------------------
 # In a second time, if they don't exist (-> aet column has a unique constraint), we retrieve and insert others aet in pacsdb : 
 # SCP : 'STORAGE' and 'IM' - with id(STORAGE)=5 and id(IM)=17

echo "" >>& gazelle_dicom_configuration_${DATE}.tmp
echo "-- SCP : 'STORAGE' and 'IM' - with id(STORAGE)=5 and id(IM)=17 or id(RRD)=26 or id(DOSE_INFO_CONSUMER)=104"  >>& gazelle_dicom_configuration_${DATE}.tmp
$POSTGRESQL/bin/psql -U $POSTGREUSER_GAZELLE -h $HOSTNAME_GAZELLE $POSTGREBASE_GAZELLE \
   -c "SELECT dicom_scp.ae_title, host.hostname, dicom_scp.port, dicom_scp.transfer_role FROM tm_system_in_session sis, tm_dicom_scp_configuration dicom_scp, tm_host host, tm_configuration config WHERE config.system_in_session_id = host.system_in_session_id and config.host_id = host.id and sis.id = config.system_in_session_id and sis.testing_session_id = $SESSION_ID and config.id = dicom_scp.id and dicom_scp.sop_class_id = '5' and (config.actor_id = '17' or config.actor_id = '26' or config.actor_id='104')  and config.is_secured IS FALSE and config.approved is TRUE ;" \
   | tail -n +3 | sed 's/ //g' | sed '$d' | sed '$d' | grep -v -e '---------' | awk -F'\|' '{printf("insert into ae (aet, hostname, port, ae_desc, installed) values (\047%s\047 , \047%s\047, \047%s\047, \047Imported From Gazelle\047 , false);\n",$1, $2, $3)}' >>& gazelle_dicom_configuration_${DATE}.tmp
# We build a tmp-file with the request results rows. To get a valid format, we take off the 3 first lines, the 2 last lines, the spaces, and the lines which contain '---------'


 # ----------------------
 # In a third time, if they don't exist (-> aet column has a unique constraint), we retrieve and insert others aet in pacsdb :
 # SCP : 'STORAGE' and 'ID' - with id(STORAGE)=5 and id(ID)=32

echo "" >>& gazelle_dicom_configuration_${DATE}.tmp
echo "-- SCP : 'STORAGE' and 'ID' - with id(STORAGE)=5 and id(ID)=32" >>& gazelle_dicom_configuration_${DATE}.tmp
$POSTGRESQL/bin/psql -U $POSTGREUSER_GAZELLE -h $HOSTNAME_GAZELLE $POSTGREBASE_GAZELLE \
   -c "SELECT dicom_scp.ae_title, host.hostname, dicom_scp.port, dicom_scp.transfer_role FROM tm_system_in_session sis,tm_dicom_scp_configuration dicom_scp, tm_host host, tm_configuration config WHERE config.system_in_session_id = host.system_in_session_id and config.host_id = host.id and sis.id = config.system_in_session_id and sis.testing_session_id = $SESSION_ID and config.id = dicom_scp.id and dicom_scp.sop_class_id = '5' and config.actor_id = '32' and sis.id = config.system_in_session_id and config.is_secured IS FALSE and config.approved is TRUE ;" \
   | tail -n +3 | sed 's/ //g' | sed '$d' | sed '$d' | grep -v -e '---------' | awk -F'\|' '{printf("insert into ae (aet, hostname, port, ae_desc, installed) values (\047%s\047 , \047%s\047, \047%s\047, \047Imported From Gazelle\047 , false);\n",$1, $2, $3)}' >>& gazelle_dicom_configuration_${DATE}.tmp
# We build a tmp-file with the request results rows. To get a valid format, we take off the 3 first lines, the 2 last lines, the spaces, and the lines which contain '---------'


 # ----------------------
 # In a fourth time, if they don't exist (-> aet column has a unique constraint), we retrieve and insert others aet in pacsdb : 
 # SCU : 'QR' and 'ID' - with id(QR)=4 and id(ID)=32

#echo "" >>& gazelle_dicom_configuration_${DATE}.tmp
#echo "-- SCU : 'QR' and 'ID' - with id(QR)=4 and id(ID)=32" >>& gazelle_dicom_configuration_${DATE}.tmp
#$POSTGRESQL/bin/psql -U $POSTGREUSER_GAZELLE -h $HOSTNAME_GAZELLE $POSTGREBASE_GAZELLE \
#   -c "SELECT dicom_scu.ae_title, host.hostname, dicom_scu.port, dicom_scu.transfer_role FROM tm_system_in_session sis, tm_dicom_scu_configuration dicom_scu, tm_host host, tm_configuration config WHERE config.system_in_session_id = host.system_in_session_id and config.id = dicom_scu.id and dicom_scu.sop_class_id = '4' and sis.testing_session_id = $SESSION_ID and config.actor_id = '32' and config.is_secured IS FALSE and config.approved is TRUE ; " \
#   | tail -n +3 | sed 's/ //g' | sed '$d' | sed '$d' | grep -v -e '---------' | awk -F'\|' '{printf("insert into ae (aet, hostname, port, ae_desc) values (\047%s\047 , \047%s\047, \047%s\047, \047Imported From Gazelle\047 );\n",$1, $2, $3)}' >>& gazelle_dicom_configuration_${DATE}.tmp
# We build a tmp-file with the request results rows. To get a valid format, we take off the 3 first lines, the 2 last lines, the spaces, and the lines which contain '---------'


echo "" >>& gazelle_dicom_configuration_${DATE}.tmp
echo "-- Done" >>& gazelle_dicom_configuration_${DATE}.tmp
echo "" >>& gazelle_dicom_configuration_${DATE}.tmp






if ($status == 0) then
    echo "Collect from database $POSTGREBASE_GAZELLE done."
else
    echo "/!\ Errors : Collect from database $POSTGREBASE_GAZELLE"
endif



# ###
# Tests (before loading base) : please, take off the following '#'
#echo "Check content in the gazelle_dicom_configuration_${DATE}.tmp file and press any key to continue...";test_wait=$<


# ###
# Delete all Gazelle Dicom configurations in the Central Archive database, before loading the new configurations

    $POSTGRESQL/bin/psql -U $POSTGREUSER $PACS_DB -c "delete from ae where ae_desc = 'Imported From Gazelle';"


# ###
# Insertion of Gazelle Dicom configuration in the Central Archive database


echo " Insertion of Gazelle Dicom configuration in the Central Archive database - In progress... "

    # Insertion request
    $POSTGRESQL/bin/psql -U $POSTGREUSER $PACS_DB < gazelle_dicom_configuration_${DATE}.tmp

    @ error_current_line=$status
    @ total_errors+=$error_current_line
    if ($error_current_line != 0) then
        echo "Error on line : $line"
    endif



if ($total_errors == 0) then
    echo "Insertion in database $PACS_DB (table $PACS_AET_TABLE) done."
else
    echo ""
    echo "/!\ $total_errors Error(s) during insertions in database $PACS_DB (table $PACS_AET_TABLE)"
endif
echo ""



#Delete the tmp-file
#rm -f gazelle_dicom_configuration_${DATE}.tmp


# Clean table :
# Quick command :  psql -U gazelle pacsdb -c "DELETE FROM ae where ae_desc='Imported From Gazelle';"
