{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Requirements\n",
    "\n",
    "- Installed git. More on how to use and install git can be found [here][git]. \n",
    "- A zip of the certificate. Particularly, you'll need the crt and key files.\n",
    "- A labkey access configuration file. A sample for [linux][linuxConfig] or [Windows][windowsConfig]. The file paths point to locations of extracted files from certificate zip on your disk.\n",
    "\n",
    "[git]: https://git-scm.com/\n",
    "[linuxConfig]: https://git0.fmf.uni-lj.si/studen/labkeyInterface/src/master/network-config-sample.json\n",
    "[windowsConfig]: https://git0.fmf.uni-lj.si/studen/labkeyInterface/src/master/network-config-sample.json"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "remoteSourcesURL https://git0.fmf.uni-lj.si/studen/nixSuite/raw/master/remoteResources/resources.json\n",
      "{'labkeyInterface': {'url': 'https://git0.fmf.uni-lj.si/studen/labkeyInterface/archive/master.zip', 'branch': 'master', 'modules': []}, 'irAEMM': {'url': 'https://git0.fmf.uni-lj.si/studen/iraemm/archive/master.zip', 'branch': 'master', 'modules': ['iraemmBrowser']}, 'SlicerLabkeyExtension': {'url': 'https://git0.fmf.uni-lj.si/studen/SlicerLabkeyExtension/archive/SlicerExtensionIndex.zip', 'branch': 'SlicerExtensionIndex', 'modules': ['labkeyBrowser']}, 'limfomiPET': {'url': 'https://git0.fmf.uni-lj.si/studen/limfomiPET/archive/master.zip', 'branch': 'master', 'modules': ['imageBrowser']}, 'parseConfig': {'url': 'https://git0.fmf.uni-lj.si/studen/parseConfig/archive/master.zip', 'branch': 'master', 'modules': []}, 'orthancInterface': {'url': 'https://git0.fmf.uni-lj.si/studen/orthancInterface/archive/master.zip', 'branch': 'master', 'modules': []}}\n"
     ]
    }
   ],
   "source": [
    "#load required libraries\n",
    "import sys\n",
    "import os\n",
    "import SimpleITK\n",
    "import numpy\n",
    "import matplotlib.pyplot\n",
    "import chardet\n",
    "import json\n",
    "\n",
    "#you should get nixSuite via git clone https://git0.fmf.uni-lj.si/studen/nixSuite.git\n",
    "#if you don't put it to $HOME/software/src/, you should update the path\n",
    "nixSuite=os.path.join(os.path.expanduser('~'),'software','src','nixSuite')\n",
    "sys.path.append(os.path.join(nixSuite,'wrapper'))\n",
    "import nixWrapper\n",
    "nixWrapper.loadLibrary('labkeyInterface')\n",
    "import labkeyInterface\n",
    "import labkeyDatabaseBrowser\n",
    "import labkeyFileBrowser\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "User: andrej studen CSRF: 71c3c9c047d735e94769566bc7a30b05\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "'71c3c9c047d735e94769566bc7a30b05'"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#check connectivity. This checks the configuration in $HOME/.labkey/network.json, \n",
    "#where paths to certificates are stored\n",
    "net=labkeyInterface.labkeyInterface()\n",
    "fconfig=os.path.join(os.path.expanduser('~'),'.labkey','labkey-klimt.json')\n",
    "#fconfig=os.path.join(os.path.expanduser('~'),'.labkey','merlin.json')\n",
    "net.init(fconfig)\n",
    "#this reports the certificate used\n",
    "try:\n",
    "    print('Using: {}'.format(net.connectionConfig['SSL']['user']))\n",
    "except KeyError:\n",
    "    pass\n",
    "#This gets a deafult CSRF code; It should report user name plus a long string of random hex numbers\n",
    "net.getCSRF()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "db=labkeyDatabaseBrowser.labkeyDB(net)\n",
    "fb=labkeyFileBrowser.labkeyFileBrowser(net)\n",
    "#select a project\n",
    "project='dinamic_spect/Patients'\n",
    "project='hypoAfrica/Study'\n",
    "dataset='Segmentation'\n",
    "schema='lists'\n",
    "query='crfEntry'\n",
    "#idFilter={'variable':'ParticipantId','value':'VUB_PA3','oper':'eq'}\n",
    "#aliasFilter={'variable':'aliasID','value':'3OBR','oper':'eq'}\n",
    "#empty filter\n",
    "qFilter=[]\n",
    "#qFilter can be a list of filters used in conjugation (logical AND)\n",
    "#qFilter=[aliasFilter]\n",
    "ds=db.selectRows(project,schema,query,qFilter)\n",
    "rows=ds['rows']\n",
    "alias={'1063':'1014','1093':'1015','1094':'1016','1095':'1017','1096':'1018',\n",
    "       '1097':'1019','1098':'1020','1099':'1021','1100':'1022',\n",
    "       '1106':'1023','1114':'1024','1122':'1025',\n",
    "       '1124':'1026','1125':'1027',\n",
    "       '1126':'1028','1127':'1029'}\n",
    "for row in rows:\n",
    "    #row as a dictionary\n",
    "    #print(row)\n",
    "    #update row\n",
    "    #depending on field type adjust newValue\n",
    "    field='crfMonitor'\n",
    "    uid=row[field]\n",
    "    if uid==None:\n",
    "        continue\n",
    "    print(uid)\n",
    "    if int(uid)>1030:\n",
    "        row[field]=int(alias['{}'.format(uid)])\n",
    "    #row['visitId']='OBR'\n",
    "    #print the new row\n",
    "    print(row)\n",
    "    #change the value of the field in database \n",
    "    #if field is not in database, it will NOT be added and no changes will occur\n",
    "    db.modifyRows('update',project,schema,query,[row])    \n",
    "        "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "User: andrej studen CSRF: 8d5073a5a0f98622de543f64ab8319cc\n",
      "User: andrej studen CSRF: 826b45d185e85d1780e89b3aac079a39\n",
      "{'ParticipantId': 'JOHN003', 'followUpDuration': 1, 'lsid': 'urn:lsid:labkey.com:Study.Data-61:5011.JOHN003.243390879.0000', '_labkeyurl_followUpDuration': '/labkey/hypoAfrica/Study/list-details.view?listId=141&pk=1', '_labkeyurl_ParticipantId': '/labkey/hypoAfrica/Study/study-participant.view?participantId=JOHN003', 'crfRef': '1649243390879', 'submissionDate': '2022/04/06 14:00:00', 'SequenceNum': 243390879.0}\n",
      "User: andrej studen CSRF: b0d3f22a0a97552c9877c12e0c15c803\n",
      "b'{\\n  \"rowsAffected\" : 1,\\n  \"queryName\" : \"consentList\",\\n  \"schemaName\" : \"study\",\\n  \"containerPath\" : \"/hypoAfrica/Study\",\\n  \"rows\" : [ {\\n    \"date\" : null,\\n    \"followUpDuration\" : 1,\\n    \"ParticipantVisit\" : \"JOHN003|243390879.0000\",\\n    \"dsrowid\" : 1,\\n    \"CreatedBy\" : 1003,\\n    \"Modified\" : \"2022-11-29 16:16:05.479\",\\n    \"DataSets\" : \"JOHN003\",\\n    \"QCState\" : null,\\n    \"submissionDate\" : \"2022-04-06 14:00:00.000\",\\n    \"ModifiedBy\" : 1003,\\n    \"Created\" : \"2022-11-29 16:16:05.479\",\\n    \"SequenceNum\" : 243390879.0000,\\n    \"lsid\" : \"urn:lsid:labkey.com:Study.Data-14:5010.JOHN003.243390879.0000\",\\n    \"ParticipantId\" : \"JOHN003\",\\n    \"diImportHash\" : null,\\n    \"sourcelsid\" : null,\\n    \"VisitRowId\" : 12,\\n    \"crfRef\" : \"1649243390879\"\\n  } ],\\n  \"command\" : \"insert\"\\n}'\n",
      "{'ParticipantId': 'NLHA001', 'followUpDuration': 3, 'lsid': 'urn:lsid:labkey.com:Study.Data-61:5011.NLHA001.640728995.0000', '_labkeyurl_followUpDuration': '/labkey/hypoAfrica/Study/list-details.view?listId=141&pk=3', '_labkeyurl_ParticipantId': '/labkey/hypoAfrica/Study/study-participant.view?participantId=NLHA001', 'crfRef': '1650640728995', 'submissionDate': '2022/04/22 14:00:00', 'SequenceNum': 640728995.0}\n",
      "User: andrej studen CSRF: 671b0eb80a773813544ab14ee68273d8\n",
      "b'{\\n  \"rowsAffected\" : 1,\\n  \"queryName\" : \"consentList\",\\n  \"schemaName\" : \"study\",\\n  \"containerPath\" : \"/hypoAfrica/Study\",\\n  \"rows\" : [ {\\n    \"date\" : null,\\n    \"followUpDuration\" : 3,\\n    \"ParticipantVisit\" : \"NLHA001|640728995.0000\",\\n    \"dsrowid\" : 2,\\n    \"CreatedBy\" : 1003,\\n    \"Modified\" : \"2022-11-29 16:16:05.977\",\\n    \"DataSets\" : \"NLHA001\",\\n    \"QCState\" : null,\\n    \"submissionDate\" : \"2022-04-22 14:00:00.000\",\\n    \"ModifiedBy\" : 1003,\\n    \"Created\" : \"2022-11-29 16:16:05.977\",\\n    \"SequenceNum\" : 640728995.0000,\\n    \"lsid\" : \"urn:lsid:labkey.com:Study.Data-14:5010.NLHA001.640728995.0000\",\\n    \"ParticipantId\" : \"NLHA001\",\\n    \"diImportHash\" : null,\\n    \"sourcelsid\" : null,\\n    \"VisitRowId\" : 11,\\n    \"crfRef\" : \"1650640728995\"\\n  } ],\\n  \"command\" : \"insert\"\\n}'\n",
      "{'ParticipantId': 'TEST001', 'followUpDuration': 1, 'lsid': 'urn:lsid:labkey.com:Study.Data-61:5011.TEST001.831036294.0000', '_labkeyurl_followUpDuration': '/labkey/hypoAfrica/Study/list-details.view?listId=141&pk=1', '_labkeyurl_ParticipantId': '/labkey/hypoAfrica/Study/study-participant.view?participantId=TEST001', 'crfRef': '1648831036294', 'submissionDate': '2022/04/01 14:00:00', 'SequenceNum': 831036294.0}\n",
      "User: andrej studen CSRF: 4197622f8e01491fbaba7c5f7ce6dfd8\n",
      "b'{\\n  \"rowsAffected\" : 1,\\n  \"queryName\" : \"consentList\",\\n  \"schemaName\" : \"study\",\\n  \"containerPath\" : \"/hypoAfrica/Study\",\\n  \"rows\" : [ {\\n    \"date\" : null,\\n    \"followUpDuration\" : 1,\\n    \"ParticipantVisit\" : \"TEST001|831036294.0000\",\\n    \"dsrowid\" : 3,\\n    \"CreatedBy\" : 1003,\\n    \"Modified\" : \"2022-11-29 16:16:06.428\",\\n    \"DataSets\" : \"TEST001\",\\n    \"QCState\" : null,\\n    \"submissionDate\" : \"2022-04-01 14:00:00.000\",\\n    \"ModifiedBy\" : 1003,\\n    \"Created\" : \"2022-11-29 16:16:06.428\",\\n    \"SequenceNum\" : 831036294.0000,\\n    \"lsid\" : \"urn:lsid:labkey.com:Study.Data-14:5010.TEST001.831036294.0000\",\\n    \"ParticipantId\" : \"TEST001\",\\n    \"diImportHash\" : null,\\n    \"sourcelsid\" : null,\\n    \"VisitRowId\" : 7,\\n    \"crfRef\" : \"1648831036294\"\\n  } ],\\n  \"command\" : \"insert\"\\n}'\n",
      "{'ParticipantId': 'TEST02', 'followUpDuration': 3, 'lsid': 'urn:lsid:labkey.com:Study.Data-61:5011.TEST02.953000497.0000', '_labkeyurl_followUpDuration': '/labkey/hypoAfrica/Study/list-details.view?listId=141&pk=3', '_labkeyurl_ParticipantId': '/labkey/hypoAfrica/Study/study-participant.view?participantId=TEST02', 'crfRef': '1668953000497', 'submissionDate': '2022/11/22 13:00:00', 'SequenceNum': 953000497.0}\n",
      "User: andrej studen CSRF: 38717387d1d774658ac0e25920f0fef2\n",
      "b'{\\n  \"rowsAffected\" : 1,\\n  \"queryName\" : \"consentList\",\\n  \"schemaName\" : \"study\",\\n  \"containerPath\" : \"/hypoAfrica/Study\",\\n  \"rows\" : [ {\\n    \"date\" : null,\\n    \"followUpDuration\" : 3,\\n    \"ParticipantVisit\" : \"TEST02|953000497.0000\",\\n    \"dsrowid\" : 4,\\n    \"CreatedBy\" : 1003,\\n    \"Modified\" : \"2022-11-29 16:16:06.933\",\\n    \"DataSets\" : \"TEST02\",\\n    \"QCState\" : null,\\n    \"submissionDate\" : \"2022-11-22 13:00:00.000\",\\n    \"ModifiedBy\" : 1003,\\n    \"Created\" : \"2022-11-29 16:16:06.933\",\\n    \"SequenceNum\" : 953000497.0000,\\n    \"lsid\" : \"urn:lsid:labkey.com:Study.Data-14:5010.TEST02.953000497.0000\",\\n    \"ParticipantId\" : \"TEST02\",\\n    \"diImportHash\" : null,\\n    \"sourcelsid\" : null,\\n    \"VisitRowId\" : 8,\\n    \"crfRef\" : \"1668953000497\"\\n  } ],\\n  \"command\" : \"insert\"\\n}'\n"
     ]
    }
   ],
   "source": [
    "net1=labkeyInterface.labkeyInterface()\n",
    "fconfig1=os.path.join(os.path.expanduser('~'),'.labkey','labkey-public.json')\n",
    "net1.init(fconfig1)\n",
    "net1.getCSRF()\n",
    "\n",
    "net2=labkeyInterface.labkeyInterface()\n",
    "fconfig2=os.path.join(os.path.expanduser('~'),'.labkey','labkey-klimt.json')\n",
    "net2.init(fconfig2)\n",
    "net2.getCSRF()\n",
    "\n",
    "db1=labkeyDatabaseBrowser.labkeyDB(net1)\n",
    "#select a project\n",
    "project1='hypoAfrica/Study'\n",
    "schema1='study'\n",
    "query1='consentList'\n",
    "\n",
    "db2=labkeyDatabaseBrowser.labkeyDB(net2)\n",
    "#select a project\n",
    "project2='hypoAfrica/Study'\n",
    "schema2='study'\n",
    "query2=query1\n",
    "#idFilter={'variable':'ParticipantId','value':'VUB_PA3','oper':'eq'}\n",
    "#aliasFilter={'variable':'aliasID','value':'3OBR','oper':'eq'}\n",
    "#empty filter\n",
    "qFilter=[]\n",
    "#qFilter can be a list of filters used in conjugation (logical AND)\n",
    "#qFilter=[aliasFilter]\n",
    "ds=db1.selectRows(project1,schema1,query1,qFilter)\n",
    "rows=ds['rows']\n",
    "for row in rows:\n",
    "    #row as a dictionary\n",
    "    print(row)\n",
    "    #update row\n",
    "    #depending on field type adjust newValue\n",
    "    #change the value of the field in database \n",
    "    #if field is not in database, it will NOT be added and no changes will occur\n",
    "    print(db2.modifyRows('insert',project2,schema2,query2,[row]))\n",
    "        "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}