{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from selenium import webdriver\n",
    "from selenium.common.exceptions import NoSuchElementException\n",
    "from neo4j import GraphDatabase\n",
    "\n",
    "uri = \"bolt://localhost:7687\"\n",
    "userName = \"neo4j\"\n",
    "password = \"org\"\n",
    "graphDB_Driver = GraphDatabase.driver(uri, auth=(userName, password)) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "projekte = \"https://dobby.ppi.int/cgi-bin/ad/adgroups.pl?ou=Projekte\"\n",
    "\n",
    "driver = webdriver.Firefox()\n",
    "driver.get(projekte)\n",
    "table = driver.find_element(\"xpath\", \"/html/body/div/div/table[2]\")\n",
    "\n",
    "groupsPpiX = []\n",
    "\n",
    "for row in table.find_elements(\"xpath\", \".//tr[not(position()=1)]\"):\n",
    "    try:\n",
    "        groupId = row.find_element(\"xpath\", \".//td[1]\").text\n",
    "        groupDesc = row.find_element(\"xpath\", \".//td[2]\").text\n",
    "        if groupId[:8] == 'prj_ppix':\n",
    "            groupsPpiX.append([groupId, groupDesc])\n",
    "    except NoSuchElementException:\n",
    "        print(\"NoSuchElementException\")\n",
    "driver.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "driver = webdriver.Firefox()\n",
    "\n",
    "personen = set()\n",
    "\n",
    "for group, groupDesc in groupsPpiX:\n",
    "    driver.get(\"https://dobby.ppi.int/cgi-bin/ad/adgroups.pl?group=\" + group)\n",
    "    table = driver.find_element(\"xpath\", \"/html/body/div/div/table[2]/tbody\")\n",
    "\n",
    "    personenInGruppe = []\n",
    "\n",
    "    for row in table.find_elements(\"xpath\", \".//tr[not(position()=1)]\"):\n",
    "        try:\n",
    "            kuerzel = row.find_element(\"xpath\", \".//td[1]\").text\n",
    "            name = row.find_element(\"xpath\", \".//td[2]\").text\n",
    "            personenInGruppe.append([kuerzel, name])\n",
    "            personen.add(kuerzel)\n",
    "        except NoSuchElementException:\n",
    "            print(\"NoSuchElementException\")\n",
    "\n",
    "    with graphDB_Driver.session() as graphDB_Session:\n",
    "        #Gruppe anlegen\n",
    "        graphDB_Session.run(\"\"\"CREATE (:ADGROUP {name: $name})\"\"\", name=group)\n",
    "\n",
    "        #Personen zur Gruppe verknüpfen\n",
    "        for kuerzel, name in personenInGruppe:\n",
    "            g = graphDB_Session.run(\"\"\"MATCH (u:USER)\n",
    "                                        WHERE u.displayName = $displayName\n",
    "                                        RETURN u.kuerzel\"\"\", displayName=name).values()\n",
    "            if g:\n",
    "                #Person existiert\n",
    "                for item in g:\n",
    "                    if not item[0]:\n",
    "                        #Attribut existiert nicht\n",
    "                        graphDB_Session.run(\"\"\"MATCH (u:USER)\n",
    "                                                WHERE u.displayName = $displayName\n",
    "                                                SET u.kuerzel = $kuerzel\"\"\", \n",
    "                                                displayName=name,\n",
    "                                                kuerzel=kuerzel)\n",
    "            else:\n",
    "                #Person existiert nicht\n",
    "                #print(name, 'existiert nicht')\n",
    "                graphDB_Session.run(\"\"\"CREATE (u:USER {displayName: $displayName, \n",
    "                                                    kuerzel: $kuerzel})\"\"\", \n",
    "                                        displayName=name,\n",
    "                                        kuerzel=kuerzel)\n",
    "            # Gruppenbeziehung anlegen\n",
    "            graphDB_Session.run(\"MATCH (grp:ADGROUP {name: $name}), (u:USER {displayName: $displayName}) MERGE (u)-[:is_member]->(grp)\",\n",
    "                                        name=group,\n",
    "                                        displayName=name)\n",
    "driver.close()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "driver = webdriver.Firefox()\n",
    "\n",
    "with graphDB_Driver.session() as graphDB_Session:\n",
    "    for kuerzel in personen:\n",
    "        driver.get(\"https://dobby.ppi.int/cgi-bin/ad/adgroups.pl?user=\" + kuerzel)\n",
    "        field = driver.find_element(\"xpath\", \"/html/body/div/div/table[2]/tbody/tr[7]/td[2]\")\n",
    "\n",
    "        for team in field.text.split('\\n'):\n",
    "            graphDB_Session.run(\"MERGE (t:TEAMS {name: $name})\", name=team)\n",
    "            graphDB_Session.run(\"MATCH (t:TEAMS {name: $name}), (u:USER {kuerzel: $kuerzel}) MERGE (u)-[:is_member]->(t)\",\n",
    "                                        name=team,\n",
    "                                        kuerzel=kuerzel)\n",
    "driver.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Personen unter Sascha, die nicht in dem Teams Kanal WS_PPI_X sind\n",
    "with graphDB_Driver.session() as graphDB_Session:\n",
    "    g = graphDB_Session.run(\"\"\"MATCH (s:USER {displayName: \"Sascha Däsler\"}) -[:vorgesetzter*]-> (u:USER)\n",
    "                                MATCH (ad:TEAMS {name: \"WS_PPI_X\"})\n",
    "                                WHERE not (u) -[:is_member]-> (ad)\n",
    "                                CALL {with u match (v:USER) -[:vorgesetzter]-> (u) return v.displayName as vorgesetzter}\n",
    "                                RETURN vorgesetzter, u.jobTitle, u.displayName\n",
    "                                ORDER BY vorgesetzter, TOLOWER(u.jobTitle), u.displayName\"\"\").values()\n",
    "g"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Personen unter Sascha, die nicht in der AD Gruppe prj_ppix sind\n",
    "with graphDB_Driver.session() as graphDB_Session:\n",
    "    g = graphDB_Session.run(\"\"\"match (s:USER {displayName: \"Sascha Däsler\"}) -[:vorgesetzter*]-> (u:USER)\n",
    "                                match (ad:ADGROUP {name: \"prj_ppix\"})\n",
    "                                where not (u) -[:is_member]-> (ad)\n",
    "                                call {with u match (v:USER) -[:vorgesetzter]-> (u) return v.displayName as vorgesetzter}\n",
    "                                RETURN vorgesetzter, u.jobTitle, u.displayName\n",
    "                                ORDER BY vorgesetzter, TOLOWER(u.jobTitle), u.displayName\"\"\").values()\n",
    "g"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "venv",
   "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.9"
  },
  "orig_nbformat": 4,
  "vscode": {
   "interpreter": {
    "hash": "28676787f946bb7f76164fe7d563a7ae5bf108f75fcb12ae9589f51e6258b2b8"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}