{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "d671dad7-8331-437c-8801-76aedc8932c7",
   "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",
    "def matchInList(test,qList):\n",
    "    for q in qList:\n",
    "        if test.find(q)>-1:\n",
    "            return True\n",
    "    return False    \n",
    "    \n",
    "def findByKey(key,rows):\n",
    "    for r in rows:\n",
    "        if r['Key']==key:\n",
    "            return r\n",
    "    return None\n",
    "\n",
    "def collapseKeys(arr):\n",
    "    a={}\n",
    "    for q in arr:\n",
    "        for k in q:\n",
    "            try:\n",
    "                a[k].append(q[k])\n",
    "            except KeyError:\n",
    "                a[k]=[q[k]]\n",
    "    return a\n",
    "\n",
    "def getValueByKey(q,key):\n",
    "    try:\n",
    "        return q[key]\n",
    "    except KeyError:\n",
    "        return 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "e847238b-f7e3-40fc-ad73-455c84d4829d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "User: andrej studen CSRF: 4b99e77937c66fdf2f6b31b572b6f462\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "'4b99e77937c66fdf2f6b31b572b6f462'"
      ]
     },
     "execution_count": 2,
     "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-public.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": 62,
   "id": "78041075-9955-4f97-9f29-da0e43d8d15f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "User: andrej studen CSRF: 8b071113c40d8b54c0135d8c441398dc\n",
      "User: andrej studen CSRF: 0e06bb075c40461f6b85dd4b97113f3b\n",
      "User: andrej studen CSRF: 80aac4b30ce6291f8e7578122bc99fdb\n",
      "User: andrej studen CSRF: e54cc3a08dedbb15623653fd721778b4\n",
      "User: andrej studen CSRF: 9cfda252e101be241f0e81203b72b6a9\n",
      "User: andrej studen CSRF: 5d14c363701b34871a8e1da76b2c166f\n",
      "User: andrej studen CSRF: 93f4a00846b77bf5b11e5d52636e3b12\n",
      "User: andrej studen CSRF: 4a8b854df6efcef4f643a6790bc6841d\n",
      "User: andrej studen CSRF: dd0fcfd310aa5b91afd797ef6eb034f7\n",
      "User: andrej studen CSRF: 41b8f9ec5f5865bad6593f55b0b11d4e\n",
      "User: andrej studen CSRF: ddd19c25dd5fde3637b38d7e97c4741a\n",
      "User: andrej studen CSRF: ab81020e86394ba537871b1c2a956343\n",
      "User: andrej studen CSRF: d1b60de848212a7604dbe00d31b5945a\n",
      "User: andrej studen CSRF: 9104500c99d1efdde99ae6433301a59a\n",
      "User: andrej studen CSRF: fe0749afb113904747f8da26cf20cfb0\n",
      "User: andrej studen CSRF: f1ca2f642912e76f4f258410c36affb9\n",
      "User: andrej studen CSRF: dbdc2df598697bdfe03c05639de777cc\n",
      "User: andrej studen CSRF: 93ccbae0e79856b23ed4e3a25c8a3d54\n",
      "User: andrej studen CSRF: c84690e70c5b76711c3122511522b6de\n",
      "User: andrej studen CSRF: 5f52a146ef762b61e9018476e4237a96\n",
      "User: andrej studen CSRF: fb5f1cc1f71869f25b498a97251b003a\n",
      "User: andrej studen CSRF: a8f5bdeb5967eed4c5bb43df83b50de5\n",
      "User: andrej studen CSRF: 1b33a96ee329ef67ba722ce36f544385\n",
      "User: andrej studen CSRF: d5907260343664c6fcc4c3401f271ed2\n",
      "User: andrej studen CSRF: bdb0b501c7fc4d8b7e149ff693c72784\n",
      "User: andrej studen CSRF: 64c2ff4d9b11f533e2627503ed8557d4\n",
      "User: andrej studen CSRF: eab8745c7fba516e1121b8db39c7da09\n",
      "User: andrej studen CSRF: 2deb96a8e503e117656d1265edc5c6d5\n",
      "User: andrej studen CSRF: 09770ee2d8907c368f26100067a9e00c\n",
      "User: andrej studen CSRF: efbb2882db2ce21b9d744966eda98f3b\n",
      "User: andrej studen CSRF: ccce98bc8c2a26dafa42e867c0083e01\n",
      "User: andrej studen CSRF: 2a3ca8aa51af64bf03528f14fc50e9d0\n",
      "User: andrej studen CSRF: a54a236a964a1ca645885cd40a890f9e\n",
      "User: andrej studen CSRF: bdd1c5966f9201d093d98199c99cf262\n",
      "User: andrej studen CSRF: 2b1df78269b2b7c28e4dacae504f6402\n",
      "User: andrej studen CSRF: 3c29afa89eba4325e7a862f5ea183dc6\n",
      "User: andrej studen CSRF: 0caf6e119bc24426996fcc06b32e982f\n",
      "User: andrej studen CSRF: 70e22d069bed3f075851be941a7f257f\n"
     ]
    }
   ],
   "source": [
    "#določi število obiskov posameznega izvajalca\n",
    "db=labkeyDatabaseBrowser.labkeyDB(net)\n",
    "fb=labkeyFileBrowser.labkeyFileBrowser(net)\n",
    "#select a project\n",
    "project='fizEksperimenti'\n",
    "schema='lists'\n",
    "query1='obremenitev'\n",
    "query2='obiski'\n",
    "query3='izvajalci'\n",
    "semFilter={'variable':'semester','value':'2','oper':'eq'}\n",
    "qFilter1=[]\n",
    "ds1=db.selectRows(project,schema,query1,qFilter1)\n",
    "letoFilter={'variable':'leto','value':'2020','oper':'gt'}\n",
    "qFilter2=[letoFilter]\n",
    "ds2=db.selectRows(project,schema,query2,qFilter2)\n",
    "rows1=ds1['rows']\n",
    "rows2=ds2['rows']\n",
    "qFilter3=[]\n",
    "ds3=db.selectRows(project,schema,query3,qFilter3)\n",
    "rows3=ds3['rows']\n",
    "for row in rows1:\n",
    "    v=row['vaja']\n",
    "    s=row['semester']\n",
    "    q=[x['semester'] for x in rows2 if x['vaja']==v]\n",
    "    q1=[x for x in q if x==s]\n",
    "    n=len(q1)\n",
    "    #n*=row['w']\n",
    "    rn=findByKey(row['izvajalec'],rows3)\n",
    "    ime='Neznano'\n",
    "    if not rn==None:\n",
    "        ime=rn['ime']\n",
    "    #print('{} {} {:.2f}/{}'.format(ime,v,n,s))\n",
    "    row['obiski']=n\n",
    "    db.modifyRows('update',project,schema,query1,[row])    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "id": "339b00f2-699f-424a-a885-a0b2acb61a32",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "User: andrej studen CSRF: 5901e176eec301284ea39b702977236e\n",
      "Zdravko Kutnjak [1, 1, 1, 1, 1, 2, 2, 2, 2, 2]\n",
      "Zdravko Kutnjak {1: 6.0, 2: 8.0}\n",
      "User: andrej studen CSRF: 64f923b4c41c1a741676aad79ac61329\n",
      "Tomaž Mertelj {1: 6.0, 2: 8.0}\n",
      "Tomaž Mertelj {1: 6.0, 2: 6.0}\n",
      "User: andrej studen CSRF: ad472543749e43c82fcd815b17c971c0\n",
      "Alenka Mertelj {1: 6.0, 2: 6.0}\n",
      "Alenka Mertelj {1: 6.0, 2: 6.0}\n",
      "User: andrej studen CSRF: 5a2f97144912f5203d9568d5c4be5a19\n",
      "Rok Pestotnik {1: 6.0, 2: 6.0}\n",
      "Rok Pestotnik {1: 18.0, 2: 18.0}\n",
      "User: andrej studen CSRF: c18080c9a682abec09c845065f9b1b49\n",
      "Marko Bračko {1: 18.0, 2: 18.0}\n",
      "Marko Bračko {1: 6.0, 2: 6.0}\n",
      "User: andrej studen CSRF: c02919c780f68d8ea167613580349970\n",
      "Gregor Kramberger {1: 6.0, 2: 6.0}\n",
      "Igor Serša []\n",
      "Igor Serša {1: 9.0, 2: 9.0}\n",
      "User: andrej studen CSRF: e9f229c8b3047bfc2a77f977394aaadf\n",
      "Tomaž Gyergek {1: 9.0, 2: 9.0}\n",
      "Tomaž Gyergek {1: 3.0, 2: 3.0}\n",
      "User: andrej studen CSRF: f2f016604aa4ef2e34a1b260ed5e8309\n",
      "Jernej Kovačič {1: 3.0, 2: 3.0}\n",
      "Jernej Kovačič {1: 3.0, 2: 3.0}\n",
      "User: andrej studen CSRF: e188a3694420ef5c24a17bd8e2b0179e\n",
      "Matjaž Kavčič {1: 3.0, 2: 3.0}\n",
      "Matjaž Kavčič {1: 6.0, 2: 6.0}\n",
      "User: andrej studen CSRF: 9a85519ec008a0d7f0e09a82f47cf51d\n",
      "Iztok Arčon {1: 6.0, 2: 6.0}\n",
      "Iztok Arčon {1: 12.0, 2: 8.0}\n",
      "User: andrej studen CSRF: 6ef1aad7a90dad84166207f74f8ab8f0\n",
      "Erik Zupančič {1: 12.0, 2: 8.0}\n",
      "Erik Zupančič {1: 6.0, 2: 6.0}\n",
      "User: andrej studen CSRF: 8276635b91ccab01cc74c25767290d63\n",
      "Luka Snoj {1: 6.0, 2: 6.0}\n",
      "Bojan Dintinjana []\n",
      "Bojan Dintinjana {1: 4.0, 2: 4.0}\n",
      "User: andrej studen CSRF: 005bc7ccc8a37678c047479d8cebed65\n",
      "Marko Zavrtanik {1: 4.0, 2: 4.0}\n",
      "Matija Milanič []\n",
      "Matija Milanič {1: 1.98, 2: 3.0}\n",
      "User: andrej studen CSRF: f518ff39a0e8cfcd1a46a2a6f7c12048\n",
      "Igor Mandić {1: 1.98, 2: 3.0}\n",
      "Igor Mandić {1: 6.0, 2: 6.0}\n",
      "User: andrej studen CSRF: fa68a1210959ae45b69eaf0ea3e4736c\n",
      "Jan Malec {1: 6.0, 2: 6.0}\n",
      "Jan Malec {1: 5.0, 2: 5.0}\n",
      "User: andrej studen CSRF: ae1b88a1a5247d490807f4fb522f26d7\n",
      "Tanja Goričanec {1: 5.0, 2: 5.0}\n",
      "Tanja Goričanec {1: 5.0, 2: 5.0}\n",
      "User: andrej studen CSRF: 19e46b95d84e38a3ecc7ce016529941d\n",
      "Luka Rogelj {1: 5.0, 2: 5.0}\n",
      "Luka Rogelj {1: 4.0200000000000005}\n",
      "User: andrej studen CSRF: b045629c57e8387b14b14aa46ea2d025\n",
      "Črt Keber {1: 4.0200000000000005}\n",
      "Črt Keber {2: 3.0}\n",
      "User: andrej studen CSRF: 92c8be09b7abe032f5808fd83ebe5c88\n"
     ]
    }
   ],
   "source": [
    "db=labkeyDatabaseBrowser.labkeyDB(net)\n",
    "fb=labkeyFileBrowser.labkeyFileBrowser(net)\n",
    "#select a project\n",
    "project='fizEksperimenti'\n",
    "schema='lists'\n",
    "query='izvajalci'\n",
    "query1='obremenitev'\n",
    "qFilter=[]\n",
    "ds=db.selectRows(project,schema,query,qFilter)\n",
    "ds1=db.selectRows(project,schema,query1,qFilter)\n",
    "rows=ds['rows']\n",
    "rows1=ds1['rows']\n",
    "query2='obracun'\n",
    "ds2=db.selectRows(project,schema,query2,[])\n",
    "db.modifyRows('delete',project,schema,query2,ds2['rows'])\n",
    "for r in rows:\n",
    "    i=r['Key']\n",
    "    print('{} {}'.format(r['ime'],q))\n",
    "    q=[{x['semester']:x['w']*x['obiski']} for x in rows1 if x['izvajalec']==i]\n",
    "    if len(q)==0:\n",
    "        continue\n",
    "    q=collapseKeys(q)\n",
    "    q={x:sum(q[x]) for x in q}\n",
    "    print('{} {}'.format(r['ime'],q))\n",
    "    outRow={'izvajalec':i}\n",
    "    outRow['ure1']=getValueByKey(q,1)/15\n",
    "    outRow['ure2']=getValueByKey(q,2)/15\n",
    "    db.modifyRows('insert',project,schema,query2,[outRow])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b1a5a546-c32f-43ca-8de0-c74311cab53e",
   "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": 5
}