smtplib.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "ecec97c2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "5<2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "d78a2afc",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "5>2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "db3e302e",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import pymysql\n",
    "import datetime"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "14651a0f",
   "metadata": {},
   "outputs": [],
   "source": [
    "def create_ip_subnet(ip_list):\n",
    "#     json_=request.get_json()\n",
    "#     ip_list=json_['ip_list']\n",
    "#     customername=json_['customername']\n",
    "    customername=\"Sattrix\"\n",
    "    data=pd.DataFrame()\n",
    "    duplicate_ip=[]\n",
    "    db = pymysql.connect(host=\"localhost\", user=\"root\", password=\"Sattrix@123\", database=\"NewEvol\")\n",
    "    for i in ip_list:\n",
    "        today_date = datetime.datetime.now()\n",
    "        tdate = today_date.strftime(\"%d-%m-%Y %H:%M:%S\")\n",
    "        i=i.strip()\n",
    "        ip_subnet = i.rfind('.')\n",
    "        slash = i.rfind('/')\n",
    "        subnet_end = int(i[(slash) + 1:])\n",
    "        cur=db.cursor()\n",
    "        query='''select CustomerName from subnetMapping where IP_Subnet='{}' '''.format(i)\n",
    "        cur.execute(query)\n",
    "        ip_available=cur.fetchall()\n",
    "        if not ip_available:\n",
    "            if subnet_end == 24:\n",
    "                pattern = '''^{}.(?:[1-9]|[1-9][0-9]|[1][0-9][0-9]|[2][0-4][0-9]|25[0-4])$'''.format(i[:ip_subnet])\n",
    "                dataframe = [\n",
    "                    {\n",
    "                        \"Department_Name\": \"Python\",\n",
    "                        \"Modification_Date\":tdate,\n",
    "                        \"End_IP\": i[:ip_subnet] + '.254',\n",
    "                        \"IP_Subnet\": i,\n",
    "                        \"Start_IP\": i[:ip_subnet] + '.0',\n",
    "                        \"CustomerName\":customername ,\n",
    "                        \"regex\": pattern\n",
    "                    }\n",
    "                ]\n",
    "                ip_rows = pd.DataFrame(dataframe)\n",
    "                data = pd.concat([ip_rows, data], ignore_index=True)\n",
    "            if subnet_end == 25:\n",
    "                pattern = '''^{}.(?:12[8-9]|1[3-9][0-9]|2[0-4][0-9]|25[0-4])$'''.format(i[:ip_subnet])\n",
    "                dataframe = [\n",
    "                    {\n",
    "                        \"Department_Name\": \"Python\",\n",
    "                        \"Modification_Date\": tdate,\n",
    "                        \"End_IP\": i[:ip_subnet] + '.254',\n",
    "                        \"IP_Subnet\": i,\n",
    "                        \"Start_IP\": i[:ip_subnet] + '.128',\n",
    "                        \"CustomerName\": customername,\n",
    "                        \"regex\": pattern\n",
    "                    }\n",
    "                ]\n",
    "                ip_rows = pd.DataFrame(dataframe)\n",
    "                data = pd.concat([ip_rows, data], ignore_index=True)\n",
    "            if subnet_end == 26:\n",
    "                pattern = '''^{}.(?:19[2-9]|2[0-4][0-9]|25[0-4])$'''.format(i[:ip_subnet])\n",
    "                dataframe = [\n",
    "                    {\n",
    "                        \"Department_Name\": \"Python\",\n",
    "                        \"Modification_Date\": tdate,\n",
    "                        \"End_IP\": i[:ip_subnet] + '.254',\n",
    "                        \"IP_Subnet\": i,\n",
    "                        \"Start_IP\": i[:ip_subnet] + '.192',\n",
    "                        \"CustomerName\": customername,\n",
    "                        \"regex\": pattern\n",
    "                    }\n",
    "                ]\n",
    "                ip_rows = pd.DataFrame(dataframe)\n",
    "                data = pd.concat([ip_rows, data], ignore_index=True)\n",
    "            if subnet_end == 27:\n",
    "                pattern = '''^{}.(?:22[4-9]|2[3-4][0-9]|25[0-4])$'''.format(i[:ip_subnet])\n",
    "                dataframe = [\n",
    "                    {\n",
    "                        \"Department_Name\": \"Python\",\n",
    "                        \"Modification_Date\": tdate,\n",
    "                        \"End_IP\": i[:ip_subnet] + '.254',\n",
    "                        \"IP_Subnet\": i,\n",
    "                        \"Start_IP\": i[:ip_subnet] + '.224',\n",
    "                        \"CustomerName\":customername,\n",
    "                        \"regex\": pattern\n",
    "                    }\n",
    "                ]\n",
    "                ip_rows = pd.DataFrame(dataframe)\n",
    "                data = pd.concat([ip_rows, data], ignore_index=True)\n",
    "            if subnet_end == 28:\n",
    "                pattern = '''^{}.(?:[2][4][0-9]|25[0-4])$'''.format(i[:ip_subnet])\n",
    "                dataframe = [\n",
    "                    {\n",
    "                        \"Department_Name\": \"Python\",\n",
    "                        \"Modification_Date\": tdate,\n",
    "                        \"End_IP\": i[:ip_subnet] + '.254',\n",
    "                        \"IP_Subnet\": i,\n",
    "                        \"Start_IP\": i[:ip_subnet] + '.240',\n",
    "                        \"CustomerName\": customername,\n",
    "                        \"regex\": pattern\n",
    "                    }\n",
    "                ]\n",
    "                ip_rows = pd.DataFrame(dataframe)\n",
    "                data = pd.concat([ip_rows, data], ignore_index=True)\n",
    "            if subnet_end == 29:\n",
    "                pattern = '''^{}.(?:[2][4][8-9]|25[0-4])$'''.format(i[:ip_subnet])\n",
    "                dataframe = [\n",
    "                    {\n",
    "                        \"Department_Name\": \"Python\",\n",
    "                        \"Modification_Date\": tdate,\n",
    "                        \"End_IP\": i[:ip_subnet] + '.254',\n",
    "                        \"IP_Subnet\": i,\n",
    "                        \"Start_IP\": i[:ip_subnet] + '.248',\n",
    "                        \"CustomerName\": customername,\n",
    "                        \"regex\": pattern\n",
    "                    }\n",
    "                ]\n",
    "                ip_rows = pd.DataFrame(dataframe)\n",
    "                data = pd.concat([ip_rows, data], ignore_index=True)\n",
    "            if subnet_end == 30:\n",
    "                pattern = '''^{}.(?:25[2-4])$'''.format(i[:ip_subnet])\n",
    "                dataframe = [\n",
    "                    {\n",
    "                        \"Department_Name\": \"Python\",\n",
    "                        \"Modification_Date\": tdate,\n",
    "                        \"End_IP\": i[:ip_subnet] + '.254',\n",
    "                        \"IP_Subnet\": i,\n",
    "                        \"Start_IP\": i[:ip_subnet] + '.252',\n",
    "                        \"CustomerName\": customername,\n",
    "                        \"regex\": pattern\n",
    "                    }\n",
    "                ]\n",
    "                ip_rows = pd.DataFrame(dataframe)\n",
    "                data = pd.concat([ip_rows, data], ignore_index=True)\n",
    "        else:\n",
    "            duplicate_ip.append(i)\n",
    "    if data.empty and len(duplicate_ip)>0:\n",
    "        return ({\"status\": 200, \"message\": \"data already available\", \"data\": duplicate_ip})\n",
    "        # print('++++++++++++++++++++++++++++++++++++++++++++++++',l)\n",
    "    if len(duplicate_ip)<0 and not data.empty:\n",
    "        data.to_sql('subnetMapping', con=mydb, if_exists='append', index=False)\n",
    "        return jsonify({\"status\": 200, \"message\": \"data added successfully\", \"data\": data})\n",
    "        # return data\n",
    "    if len(duplicate_ip)>0 and not data.empty:\n",
    "        data.to_sql('subnetMapping', con=mydb, if_exists='append', index=False)\n",
    "        return ({\"status\": 200, \"message\": \"data added successfully\", \"Data Already Available\": duplicate_ip})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "3a935566",
   "metadata": {},
   "outputs": [],
   "source": [
    "f=create_ip_subnet([\"192.168.21.1/25\",\"192.168.52.1/29\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "2fb1ce63",
   "metadata": {},
   "outputs": [],
   "source": [
    "f"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "d9fdab3d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "()\n",
      "()\n",
      "  Department_Name    Modification_Date          End_IP        IP_Subnet  \\\n",
      "0          Python  08-09-2023 12:53:13  192.168.52.254  192.168.52.1/29   \n",
      "1          Python  08-09-2023 12:53:13  192.168.21.254  192.168.21.1/25   \n",
      "\n",
      "         Start_IP CustomerName  \\\n",
      "0  192.168.52.248      Sattrix   \n",
      "1  192.168.21.128      Sattrix   \n",
      "\n",
      "                                               regex  \n",
      "0               ^192.168.52.(?:[2][4][8-9]|25[0-4])$  \n",
      "1  ^192.168.21.(?:12[8-9]|1[3-9][0-9]|2[0-4][0-9]...  \n",
      "4\n"
     ]
    }
   ],
   "source": [
    "ip_list=[\"192.168.21.1/25\",\"192.168.52.1/29\"]\n",
    "customername=\"Sattrix\"\n",
    "data=pd.DataFrame()\n",
    "duplicate_ip=[]\n",
    "db = pymysql.connect(host=\"localhost\", user=\"root\", password=\"Sattrix@123\", database=\"NewEvol\")\n",
    "for i in ip_list:\n",
    "    today_date = datetime.datetime.now()\n",
    "    tdate = today_date.strftime(\"%d-%m-%Y %H:%M:%S\")\n",
    "    i=i.strip()\n",
    "    ip_subnet = i.rfind('.')\n",
    "    slash = i.rfind('/')\n",
    "    subnet_end = int(i[(slash) + 1:])\n",
    "    cur=db.cursor()\n",
    "    query='''select CustomerName from subnetMapping where IP_Subnet='{}' '''.format(i)\n",
    "    cur.execute(query)\n",
    "    ip_available=cur.fetchall()\n",
    "    print(ip_available)\n",
    "    if not ip_available:\n",
    "        if subnet_end == 24:\n",
    "            pattern = '''^{}.(?:[1-9]|[1-9][0-9]|[1][0-9][0-9]|[2][0-4][0-9]|25[0-4])$'''.format(i[:ip_subnet])\n",
    "            dataframe = [\n",
    "                {\n",
    "                    \"Department_Name\": \"Python\",\n",
    "                    \"Modification_Date\":tdate,\n",
    "                    \"End_IP\": i[:ip_subnet] + '.254',\n",
    "                    \"IP_Subnet\": i,\n",
    "                    \"Start_IP\": i[:ip_subnet] + '.0',\n",
    "                    \"CustomerName\":customername ,\n",
    "                    \"regex\": pattern\n",
    "                }\n",
    "            ]\n",
    "            ip_rows = pd.DataFrame(dataframe)\n",
    "            data = pd.concat([ip_rows, data], ignore_index=True)\n",
    "        if subnet_end == 25:\n",
    "            pattern = '''^{}.(?:12[8-9]|1[3-9][0-9]|2[0-4][0-9]|25[0-4])$'''.format(i[:ip_subnet])\n",
    "            dataframe = [\n",
    "                {\n",
    "                    \"Department_Name\": \"Python\",\n",
    "                    \"Modification_Date\": tdate,\n",
    "                    \"End_IP\": i[:ip_subnet] + '.254',\n",
    "                    \"IP_Subnet\": i,\n",
    "                    \"Start_IP\": i[:ip_subnet] + '.128',\n",
    "                    \"CustomerName\": customername,\n",
    "                    \"regex\": pattern\n",
    "                }\n",
    "            ]\n",
    "            ip_rows = pd.DataFrame(dataframe)\n",
    "            data = pd.concat([ip_rows, data], ignore_index=True)\n",
    "        if subnet_end == 26:\n",
    "            pattern = '''^{}.(?:19[2-9]|2[0-4][0-9]|25[0-4])$'''.format(i[:ip_subnet])\n",
    "            dataframe = [\n",
    "                {\n",
    "                    \"Department_Name\": \"Python\",\n",
    "                    \"Modification_Date\": tdate,\n",
    "                    \"End_IP\": i[:ip_subnet] + '.254',\n",
    "                    \"IP_Subnet\": i,\n",
    "                    \"Start_IP\": i[:ip_subnet] + '.192',\n",
    "                    \"CustomerName\": customername,\n",
    "                    \"regex\": pattern\n",
    "                }\n",
    "            ]\n",
    "            ip_rows = pd.DataFrame(dataframe)\n",
    "            data = pd.concat([ip_rows, data], ignore_index=True)\n",
    "        if subnet_end == 27:\n",
    "            pattern = '''^{}.(?:22[4-9]|2[3-4][0-9]|25[0-4])$'''.format(i[:ip_subnet])\n",
    "            dataframe = [\n",
    "                {\n",
    "                    \"Department_Name\": \"Python\",\n",
    "                    \"Modification_Date\": tdate,\n",
    "                    \"End_IP\": i[:ip_subnet] + '.254',\n",
    "                    \"IP_Subnet\": i,\n",
    "                    \"Start_IP\": i[:ip_subnet] + '.224',\n",
    "                    \"CustomerName\":customername,\n",
    "                    \"regex\": pattern\n",
    "                }\n",
    "            ]\n",
    "            ip_rows = pd.DataFrame(dataframe)\n",
    "            data = pd.concat([ip_rows, data], ignore_index=True)\n",
    "        if subnet_end == 28:\n",
    "            pattern = '''^{}.(?:[2][4][0-9]|25[0-4])$'''.format(i[:ip_subnet])\n",
    "            dataframe = [\n",
    "                {\n",
    "                    \"Department_Name\": \"Python\",\n",
    "                    \"Modification_Date\": tdate,\n",
    "                    \"End_IP\": i[:ip_subnet] + '.254',\n",
    "                    \"IP_Subnet\": i,\n",
    "                    \"Start_IP\": i[:ip_subnet] + '.240',\n",
    "                    \"CustomerName\": customername,\n",
    "                    \"regex\": pattern\n",
    "                }\n",
    "            ]\n",
    "            ip_rows = pd.DataFrame(dataframe)\n",
    "            data = pd.concat([ip_rows, data], ignore_index=True)\n",
    "        if subnet_end == 29:\n",
    "            pattern = '''^{}.(?:[2][4][8-9]|25[0-4])$'''.format(i[:ip_subnet])\n",
    "            dataframe = [\n",
    "                {\n",
    "                    \"Department_Name\": \"Python\",\n",
    "                    \"Modification_Date\": tdate,\n",
    "                    \"End_IP\": i[:ip_subnet] + '.254',\n",
    "                    \"IP_Subnet\": i,\n",
    "                    \"Start_IP\": i[:ip_subnet] + '.248',\n",
    "                    \"CustomerName\": customername,\n",
    "                    \"regex\": pattern\n",
    "                }\n",
    "            ]\n",
    "            ip_rows = pd.DataFrame(dataframe)\n",
    "            data = pd.concat([ip_rows, data], ignore_index=True)\n",
    "        if subnet_end == 30:\n",
    "            pattern = '''^{}.(?:25[2-4])$'''.format(i[:ip_subnet])\n",
    "            dataframe = [\n",
    "                {\n",
    "                    \"Department_Name\": \"Python\",\n",
    "                    \"Modification_Date\": tdate,\n",
    "                    \"End_IP\": i[:ip_subnet] + '.254',\n",
    "                    \"IP_Subnet\": i,\n",
    "                    \"Start_IP\": i[:ip_subnet] + '.252',\n",
    "                    \"CustomerName\": customername,\n",
    "                    \"regex\": pattern\n",
    "                }\n",
    "            ]\n",
    "            ip_rows = pd.DataFrame(dataframe)\n",
    "            data = pd.concat([ip_rows, data], ignore_index=True)\n",
    "    else:\n",
    "        duplicate_ip.append(i)\n",
    "print(data)\n",
    "if data.empty and len(duplicate_ip)>0:\n",
    "#     return ({\"status\": 200, \"message\": \"data already available\", \"data\": duplicate_ip})\n",
    "    print('1')\n",
    "if len(duplicate_ip)<0 and not data.empty:\n",
    "#     data.to_sql('subnetMapping', con=mydb, if_exists='append', index=False)\n",
    "#     return jsonify({\"status\": 200, \"message\": \"data added successfully\", \"data\": data})\n",
    "#     return data\n",
    "    print(\"2\")\n",
    "if len(duplicate_ip)>0 and data.empty:\n",
    "#     data.to_sql('subnetMapping', con=mydb, if_exists='append', index=False)\n",
    "#     return ({\"status\": 200, \"message\": \"data added successfully\", \"Data Already Available\": duplicate_ip})\n",
    "    print(\"3\")\n",
    "else:\n",
    "    print(\"4\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "f8757e0a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "not len(duplicate_ip)>0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "5a3e3c9e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# duplicate_ip=[1,]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7df545a2",
   "metadata": {},
   "outputs": [],
   "source": [
    "not len(duplicate_ip)<0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9346feb6",
   "metadata": {},
   "outputs": [],
   "source": [
    "not data.empty"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ec8d754d",
   "metadata": {},
   "outputs": [],
   "source": [
    "not len(duplicate_ip)>0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "666d2e6b",
   "metadata": {},
   "outputs": [],
   "source": [
    "not data.empty"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "395a4c48",
   "metadata": {},
   "outputs": [],
   "source": [
    "duplicate_ip=[2,5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "957b5276",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "321eeafb",
   "metadata": {},
   "outputs": [],
   "source": [
    "len(duplicate_ip) <=0 and not data.empty"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "318f4690",
   "metadata": {},
   "outputs": [],
   "source": [
    "len(duplicate_ip) > 0 and not data.empty"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e977ed59",
   "metadata": {},
   "outputs": [],
   "source": [
    "data.empty and len(duplicate_ip) >=0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c6f63b57",
   "metadata": {},
   "outputs": [],
   "source": [
    "db = pymysql.connect(host=\"localhost\", user=\"root\", password=\"Sattrix@123\", database=\"NewEvol\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c8282da6",
   "metadata": {},
   "outputs": [],
   "source": [
    "data=pd.read_sql('''select Modification_Date,IP_Subnet ,Start_IP, End_IP, CustomerName from subnetMapping ''',con=db)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fbd19465",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a7c6be26",
   "metadata": {},
   "outputs": [],
   "source": [
    "data=pd.read_sql('''select Modification_Date,IP_Subnet ,Start_IP, End_IP, CustomerName from subnetMapping order by Modification_Date DESC ''',con=db)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f87923b6",
   "metadata": {},
   "outputs": [],
   "source": [
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0e927f97",
   "metadata": {},
   "outputs": [],
   "source": [
    "from pandasql import sqldf\n",
    "n=sqldf('''select * from data group by CustomerName''')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "21a89ced",
   "metadata": {},
   "outputs": [],
   "source": [
    "n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8c8da714",
   "metadata": {},
   "outputs": [],
   "source": [
    "len(duplicate_ip)>0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "54f71814",
   "metadata": {},
   "outputs": [],
   "source": [
    "len(duplicate_ip)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "080a003a",
   "metadata": {},
   "outputs": [],
   "source": [
    "len(duplicate_ip)<0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d807590a",
   "metadata": {},
   "outputs": [],
   "source": [
    "5>0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3d8caefc",
   "metadata": {},
   "outputs": [],
   "source": [
    "list_ip=[[\"125.165.721.130\",\"125.165.721.225\"],[\"192.168.25.11\",\"192.168.25.124\"]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "db9d8421",
   "metadata": {},
   "outputs": [],
   "source": [
    "for i in list_ip:\n",
    "    if '125' in i:\n",
    "        print('hello')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1d684b55",
   "metadata": {},
   "outputs": [],
   "source": [
    "i[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "342572e5",
   "metadata": {},
   "outputs": [],
   "source": [
    "int(ipendvalue)-int(ipstartvalue)<10"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e0ef661e",
   "metadata": {},
   "outputs": [],
   "source": [
    "ipendvalue=124\n",
    "ipstartvalue=11"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "99019cef",
   "metadata": {},
   "outputs": [],
   "source": [
    "(\"Initial Access\", \"Execution\", \"Persistence\", \"Privilege Escalation\", \"Defense Evasion\",\n",
    "                   \"Credential Access\", \"Discovery\", \"Lateral Movement\", \"Collection\", \"Command and Control\",\n",
    "                   \"Exfiltration\", \"Impact\",\"Resource Development\",\"Reconnaissance\")== (\"Initial Access\", \"Execution\", \"Persistence\", \"Privilege Escalation\", \"Defense Evasion\",\n",
    "                   \"Credential Access\", \"Discovery\", \"Lateral Movement\", \"Collection\", \"Command and Control\",\n",
    "                   \"Exfiltration\", \"Impact\",\"Resource Development\",\"Reconnaissance\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "88ec5c87",
   "metadata": {},
   "outputs": [],
   "source": [
    "d=pd.read_csv('demo_inci.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7e6855c8",
   "metadata": {},
   "outputs": [],
   "source": [
    "d"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4e7dfddf",
   "metadata": {},
   "outputs": [],
   "source": [
    "d.reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b2d26e13",
   "metadata": {},
   "outputs": [],
   "source": [
    "d[d.src.str.match('^45.112.178.18$')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4cdc0eef",
   "metadata": {},
   "outputs": [],
   "source": [
    " db = pymysql.connect(host=\"localhost\", user=\"root\", password=\"Sattrix@123\", database=\"NewEvol\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7792e047",
   "metadata": {},
   "outputs": [],
   "source": [
    "cur=db.cursor()\n",
    "cur.execute('''delete from subnetMapping where Modification_Date like \"%29-08-2023%\" ''')\n",
    "db.commit()"
   ]
  },
  {
   "cell_type": "raw",
   "id": "8358f42c",
   "metadata": {},
   "source": [
    "cur.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f090640c",
   "metadata": {},
   "outputs": [],
   "source": [
    "cur.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e854198a",
   "metadata": {},
   "outputs": [],
   "source": [
    "field=\"192.168\"\n",
    "limit_value=10\n",
    "query = \"select Modification_Date,Department_Name,End_IP,IP_Subnet,Start_IP,CustomerName from subnetMapping where (Modification_Date like '%{}%' OR Department_Name like '%{}%' OR End_IP like '%{}%' OR IP_Subnet like '%{}%' OR Start_IP like '%{}%' OR CustomerName like '%{}%' ) Order by Modification_Date desc limit {}, 10\".format(\n",
    "                field,field,field,field,field,field,limit_value)\n",
    "cur.execute(query)\n",
    "cur.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2b133211",
   "metadata": {},
   "outputs": [],
   "source": [
    " MAX(CASE WHEN Technique_Name = 'Initial Access' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT Alert_Id FROM vw_GetAlertIdByMitre WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Initial Access\" AND CustomerName='{}' GROUP BY RuleId ORDER BY StartTime DESC) as sub1),0) ) END) AS Initial_Access,\n",
    "            MAX(CASE WHEN Technique_Name = 'Execution' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT Alert_Id FROM vw_GetAlertIdByMitre WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Execution\" AND CustomerName='{}' GROUP BY RuleId ORDER BY StartTime DESC) as sub2),0) ) END) AS Execution,\n",
    "            MAX(CASE WHEN Technique_Name = 'Persistence' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT Alert_Id FROM vw_GetAlertIdByMitre WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Persistence\" AND CustomerName='{}' GROUP BY RuleId ORDER BY StartTime DESC) as sub3),0) ) END) AS Persistence,\n",
    "            MAX(CASE WHEN Technique_Name = 'Privilege Escalation' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT Alert_Id FROM vw_GetAlertIdByMitre WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Privilege Escalation\" AND CustomerName='{}' GROUP BY RuleId ORDER BY StartTime DESC) as sub4),0) ) END)  AS Privilege_Escalation,\n",
    "            MAX(CASE WHEN Technique_Name = 'Defense Evasion' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT Alert_Id FROM vw_GetAlertIdByMitre WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Defense Evasion\" AND CustomerName='{}' GROUP BY RuleId ORDER BY StartTime DESC) as sub5),0) ) END)  AS Defense_Evasion,\n",
    "            MAX(CASE WHEN Technique_Name = 'Credential Access' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT Alert_Id FROM vw_GetAlertIdByMitre WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Credential Access\" CustomerName='{}' GROUP BY RuleId ORDER BY StartTime DESC) as sub6),0) ) END)  AS Credential_Access,\n",
    "            MAX(CASE WHEN Technique_Name = 'Discovery' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT Alert_Id FROM vw_GetAlertIdByMitre WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Discovery\" CustomerName='{}' GROUP BY RuleId ORDER BY StartTime DESC) as sub7),0) ) END)  AS Discovery,\n",
    "            MAX(CASE WHEN Technique_Name = 'Lateral Movement' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT Alert_Id FROM vw_GetAlertIdByMitre WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Lateral Movement\" CustomerName='{}' GROUP BY RuleId ORDER BY StartTime DESC) as sub8),0) ) END)  AS Lateral_Movement,\n",
    "            MAX(CASE WHEN Technique_Name = 'Collection' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT Alert_Id FROM vw_GetAlertIdByMitre WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Collection\" CustomerName='{}' GROUP BY RuleId ORDER BY StartTime DESC) as sub9),0) ) END)  AS Collection,\n",
    "            MAX(CASE WHEN Technique_Name = 'Command and Control' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT Alert_Id FROM vw_GetAlertIdByMitre WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Command and Control\" CustomerName='{}' GROUP BY RuleId ORDER BY StartTime DESC) as sub10),0) ) END)  AS Command_and_Control,\n",
    "            MAX(CASE WHEN Technique_Name = 'Exfiltration' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT Alert_Id FROM vw_GetAlertIdByMitre WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Exfiltration\" CustomerName='{}' GROUP BY RuleId ORDER BY StartTime DESC) as sub11),0) ) END)  AS Exfiltration,\n",
    "            MAX(CASE WHEN Technique_Name = 'Impact' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT Alert_Id FROM vw_GetAlertIdByMitre WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Impact\" CustomerName='{}' GROUP BY RuleId ORDER BY StartTime DESC) as sub12),0) ) END)  AS Impact,\n",
    "            MAX(CASE WHEN Technique_Name = 'Resource Development' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT Alert_Id FROM vw_GetAlertIdByMitre WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Resource Development\" CustomerName='{}' GROUP BY RuleId ORDER BY StartTime DESC) as sub13),0) ) END)  AS Resource_Development,\n",
    "            MAX(CASE WHEN Technique_Name = 'Reconnaissance' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT Alert_Id FROM vw_GetAlertIdByMitre WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Reconnaissance\" CustomerName='{}' GROUP BY RuleId ORDER BY StartTime DESC) as sub14),0) ) END)  AS Reconnaissance\n",
    "\n",
    "                 FROM Rule_MitreTechnique where (UserId={} OR RuleGroup IN ({}))\n",
    "                 GROUP BY Technique_Name,Sub_Technique\n",
    "                 ORDER BY {} ,{}\"\"\"."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fbf75266",
   "metadata": {},
   "outputs": [],
   "source": [
    "d='''MAX(CASE WHEN Technique_Name = 'Credential Access' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT Alert_Id FROM vw_GetAlertIdByMitre WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Credential Access\" AND CustomerName='{}' GROUP BY RuleId ORDER BY StartTime DESC) as sub6),0) ) END)  AS Credential_Access,'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "46a5c03a",
   "metadata": {},
   "outputs": [],
   "source": [
    "R='''MAX(CASE WHEN Technique_Name = 'Credential Access' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT Alert_Id FROM vw_GetAlertIdByMitre WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Credential Access\" CustomerName='{}' GROUP BY RuleId ORDER BY StartTime DESC) as sub6),0) ) END)  AS Credential_Access,'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d9f95d49",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "r=R.split(' ')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "107663ff",
   "metadata": {},
   "outputs": [],
   "source": [
    "d"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5d18ff93",
   "metadata": {},
   "outputs": [],
   "source": [
    "for i in d:\n",
    "    if i not in r:\n",
    "        print(i)\n",
    "    else:\n",
    "        continue"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "52ba39ad",
   "metadata": {},
   "outputs": [],
   "source": [
    "i==d"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b3eb2ca5",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(r)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "65b86f3c",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(d)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9b9abe10",
   "metadata": {},
   "outputs": [],
   "source": [
    "MAX(CASE WHEN Technique_Name = 'Initial Access' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT Alert_Id FROM vw_GetAlertIdByMitre WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Initial Access\" AND CustomerName='{}' GROUP BY RuleId ORDER BY StartTime DESC) as sub1),0) ) END) AS Initial_Access,\n",
    "                                        MAX(CASE WHEN Technique_Name = 'Execution' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT Alert_Id FROM vw_GetAlertIdByMitre WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Execution\" AND CustomerName='{}' GROUP BY RuleId ORDER BY StartTime DESC) as sub2),0) ) END) AS Execution,\n",
    "                                        MAX(CASE WHEN Technique_Name = 'Persistence' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT Alert_Id FROM vw_GetAlertIdByMitre WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Persistence\" AND CustomerName='{}' GROUP BY RuleId ORDER BY StartTime DESC) as sub3),0) ) END) AS Persistence,\n",
    "                                        MAX(CASE WHEN Technique_Name = 'Privilege Escalation' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT Alert_Id FROM vw_GetAlertIdByMitre WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Privilege Escalation\" AND CustomerName='{}' GROUP BY RuleId ORDER BY StartTime DESC) as sub4),0) ) END)  AS Privilege_Escalation,\n",
    "                                        MAX(CASE WHEN Technique_Name = 'Defense Evasion' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT Alert_Id FROM vw_GetAlertIdByMitre WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Defense Evasion\" AND CustomerName='{}' GROUP BY RuleId ORDER BY StartTime DESC) as sub5),0) ) END)  AS Defense_Evasion,\n",
    "                                        MAX(CASE WHEN Technique_Name = 'Credential Access' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT Alert_Id FROM vw_GetAlertIdByMitre WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Credential Access\" CustomerName='{}' GROUP BY RuleId ORDER BY StartTime DESC) as sub6),0) ) END)  AS Credential_Access,\n",
    "                                        MAX(CASE WHEN Technique_Name = 'Discovery' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT Alert_Id FROM vw_GetAlertIdByMitre WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Discovery\" CustomerName='{}' GROUP BY RuleId ORDER BY StartTime DESC) as sub7),0) ) END)  AS Discovery,\n",
    "                                        MAX(CASE WHEN Technique_Name = 'Lateral Movement' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT Alert_Id FROM vw_GetAlertIdByMitre WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Lateral Movement\" CustomerName='{}' GROUP BY RuleId ORDER BY StartTime DESC) as sub8),0) ) END)  AS Lateral_Movement,\n",
    "                                        MAX(CASE WHEN Technique_Name = 'Collection' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT Alert_Id FROM vw_GetAlertIdByMitre WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Collection\" CustomerName='{}' GROUP BY RuleId ORDER BY StartTime DESC) as sub9),0) ) END)  AS Collection,\n",
    "                                        MAX(CASE WHEN Technique_Name = 'Command and Control' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT Alert_Id FROM vw_GetAlertIdByMitre WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Command and Control\" CustomerName='{}' GROUP BY RuleId ORDER BY StartTime DESC) as sub10),0) ) END)  AS Command_and_Control,\n",
    "                                        MAX(CASE WHEN Technique_Name = 'Exfiltration' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT Alert_Id FROM vw_GetAlertIdByMitre WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Exfiltration\" CustomerName='{}' GROUP BY RuleId ORDER BY StartTime DESC) as sub11),0) ) END)  AS Exfiltration,\n",
    "                                        MAX(CASE WHEN Technique_Name = 'Impact' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT Alert_Id FROM vw_GetAlertIdByMitre WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Impact\" CustomerName='{}' GROUP BY RuleId ORDER BY StartTime DESC) as sub12),0) ) END)  AS Impact,\n",
    "                                        MAX(CASE WHEN Technique_Name = 'Resource Development' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT Alert_Id FROM vw_GetAlertIdByMitre WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Resource Development\" CustomerName='{}' GROUP BY RuleId ORDER BY StartTime DESC) as sub13),0) ) END)  AS Resource_Development,\n",
    "                                        MAX(CASE WHEN Technique_Name = 'Reconnaissance' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT Alert_Id FROM vw_GetAlertIdByMitre WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Reconnaissance\" CustomerName='{}' GROUP BY RuleId ORDER BY StartTime DESC) as sub14),0) ) END)  AS Reconnaissance\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "837aec61",
   "metadata": {},
   "outputs": [],
   "source": [
    "d=[\"Modification_Date like '%%' AND Department_Name like '%%' AND End_IP like '%%' AND  IP_Subnet like '%%' AND Start_IP like '%%' AND CustomerName like '%hsc%'\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ce328865",
   "metadata": {},
   "outputs": [],
   "source": [
    "''.join(d)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "473e283d",
   "metadata": {},
   "outputs": [],
   "source": [
    "db = pymysql.connect(host=\"10.172.194.43\", user=\"root\", password=\"Sattrix@123\", database=\"NewEvol\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "33c34bc0",
   "metadata": {},
   "outputs": [],
   "source": [
    "custname=\"SATTRIX\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ca1b3f3d",
   "metadata": {},
   "outputs": [],
   "source": [
    "result = pd.read_sql(\"\"\" SELECT\n",
    "                MAX(CASE WHEN Technique_Name = 'Initial Access' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT * FROM vw_GetAlertIdByMitre WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Initial Access\" AND CustomerName='{}' ORDER BY StartTime DESC) as sub1),0) ) END) AS Initial_Access,\n",
    "                MAX(CASE WHEN Technique_Name = 'Execution' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT * FROM vw_GetAlertIdByMitre WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Execution\" AND CustomerName='{}' ORDER BY StartTime DESC ) as sub2),0) ) END) AS Execution,\n",
    "                MAX(CASE WHEN Technique_Name = 'Persistence' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT * FROM vw_GetAlertIdByMitre WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Persistence\" AND CustomerName='{}' ORDER BY StartTime DESC) as sub3),0) ) END) AS Persistence,\n",
    "                MAX(CASE WHEN Technique_Name = 'Privilege Escalation' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT * FROM vw_GetAlertIdByMitre WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Privilege Escalation\" AND CustomerName='{}' ORDER BY StartTime DESC) as sub4),0) ) END)  AS Privilege_Escalation,\n",
    "                MAX(CASE WHEN Technique_Name = 'Defense Evasion' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT * FROM vw_GetAlertIdByMitre WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Defense Evasion\" AND CustomerName='{}' ORDER BY StartTime DESC) as sub5),0) ) END)  AS Defense_Evasion,\n",
    "                MAX(CASE WHEN Technique_Name = 'Credential Access' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT *  FROM vw_GetAlertIdByMitre WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Credential Access\" AND CustomerName='{}' ORDER BY StartTime DESC) as sub6),0) ) END)  AS Credential_Access,\n",
    "                MAX(CASE WHEN Technique_Name = 'Discovery' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT * FROM vw_GetAlertIdByMitre WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Discovery\" AND CustomerName='{}' ORDER BY StartTime DESC) as sub7),0) ) END)  AS Discovery,\n",
    "                MAX(CASE WHEN Technique_Name = 'Lateral Movement' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT * FROM vw_GetAlertIdByMitre WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Lateral Movement\" AND CustomerName='{}' ORDER BY StartTime DESC) as sub8),0) ) END)  AS Lateral_Movement,\n",
    "                MAX(CASE WHEN Technique_Name = 'Collection' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT * FROM vw_GetAlertIdByMitre WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Collection\" AND CustomerName='{}' ORDER BY StartTime DESC) as sub9),0) ) END)  AS Collection,\n",
    "                MAX(CASE WHEN Technique_Name = 'Command and Control' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT * FROM vw_GetAlertIdByMitre WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Command and Control\" AND CustomerName='{}' ORDER BY StartTime DESC) as sub10),0) ) END)  AS Command_and_Control,\n",
    "                MAX(CASE WHEN Technique_Name = 'Exfiltration' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT * FROM vw_GetAlertIdByMitre WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Exfiltration\" AND CustomerName='{}' ORDER BY StartTime DESC) as sub11),0) ) END)  AS Exfiltration,\n",
    "                MAX(CASE WHEN Technique_Name = 'Impact' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT * FROM vw_GetAlertIdByMitre WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Impact\" AND CustomerName='{}' ORDER BY StartTime DESC) as sub12),0) ) END)  AS Impact,\n",
    "                MAX(CASE WHEN Technique_Name = 'Resource Development' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT * FROM vw_GetAlertIdByMitre WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Resource Development\" AND CustomerName='{}' ORDER BY StartTime DESC) as sub13),0) ) END)  AS Resource_Development,\n",
    "                MAX(CASE WHEN Technique_Name = 'Reconnaissance' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT * FROM vw_GetAlertIdByMitre WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Reconnaissance\" AND CustomerName='{}' ORDER BY StartTime DESC) as sub14),0) ) END)  AS Reconnaissance\n",
    "    \n",
    "    \n",
    "                     FROM Rule_MitreTechnique\n",
    "                     GROUP BY Technique_Name,Sub_Technique\"\"\".format(custname, custname, custname, custname, custname,\n",
    "                                                                     custname, custname, custname, custname, custname,\n",
    "                                                                     custname, custname, custname, custname), con=db)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "57486ff1",
   "metadata": {},
   "outputs": [],
   "source": [
    "result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "100962fc",
   "metadata": {},
   "outputs": [],
   "source": [
    "Defense_Evasion = result[\"Defense_Evasion\"].dropna()\n",
    "Initial_Access = result[\"Initial_Access\"].dropna()\n",
    "Execution = result[\"Execution\"].dropna()\n",
    "Persistence = result[\"Persistence\"].dropna()\n",
    "Privilege_Escalation = result[\"Privilege_Escalation\"].dropna()\n",
    "Credential_Access = result[\"Credential_Access\"].dropna()\n",
    "Discovery = result[\"Discovery\"].dropna()\n",
    "Lateral_Movement = result[\"Lateral_Movement\"].dropna()\n",
    "Collection = result[\"Collection\"].dropna()\n",
    "Command_and_Control = result[\"Command_and_Control\"].dropna()\n",
    "Exfiltration = result[\"Exfiltration\"].dropna()\n",
    "Impact = result[\"Impact\"].dropna()\n",
    "Resource_Development = result[\"Resource_Development\"].dropna()\n",
    "Reconnaissance = result[\"Reconnaissance\"].dropna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2f24725b",
   "metadata": {},
   "outputs": [],
   "source": [
    "Credential_Access"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "98c932ea",
   "metadata": {},
   "outputs": [],
   "source": [
    "df1 = pd.DataFrame(Defense_Evasion).reset_index(drop=True)\n",
    "df2 = pd.DataFrame(Initial_Access).reset_index(drop=True)\n",
    "df3 = pd.DataFrame(Execution).reset_index(drop=True)\n",
    "df4 = pd.DataFrame(Persistence).reset_index(drop=True)\n",
    "df5 = pd.DataFrame(Privilege_Escalation).reset_index(drop=True)\n",
    "df6 = pd.DataFrame(Credential_Access).reset_index(drop=True)\n",
    "df7 = pd.DataFrame(Discovery).reset_index(drop=True)\n",
    "df8 = pd.DataFrame(Lateral_Movement).reset_index(drop=True)\n",
    "df9 = pd.DataFrame(Collection).reset_index(drop=True)\n",
    "df10 = pd.DataFrame(Command_and_Control).reset_index(drop=True)\n",
    "df11 = pd.DataFrame(Exfiltration).reset_index(drop=True)\n",
    "df12 = pd.DataFrame(Impact).reset_index(drop=True)\n",
    "df13 = pd.DataFrame(Resource_Development).reset_index(drop=True)\n",
    "df14 = pd.DataFrame(Reconnaissance).reset_index(drop=True)\n",
    "\n",
    "dd1 = df1.join(df2)\n",
    "dd2 = dd1.join(df3)\n",
    "dd3 = dd2.join(df4)\n",
    "dd4 = dd3.join(df5)\n",
    "dd5 = dd4.join(df6)\n",
    "dd6 = dd5.join(df7)\n",
    "dd7 = dd6.join(df8)\n",
    "dd8 = dd7.join(df9)\n",
    "dd9 = dd8.join(df10)\n",
    "dd10 = dd9.join(df11)\n",
    "dd11 = dd10.join(df12)\n",
    "dd12 = dd11.join(df13)\n",
    "dd13 = dd12.join(df14)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2bc5e7ac",
   "metadata": {},
   "outputs": [],
   "source": [
    "dd13"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2930c713",
   "metadata": {},
   "outputs": [],
   "source": [
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2fee1cdc",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.read_sql(\"\"\" SELECT MAX(CASE WHEN Technique_Name = 'Initial Access' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT * FROM vw_GetAlertIdByMitre WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Initial Access\" AND CustomerName='{}' ORDER BY StartTime DESC) as sub1),0) ) END) AS Initial_Access,\n",
    "                FROM Rule_MitreTechnique\n",
    "                     GROUP BY Technique_Name,Sub_Technique\"\"\".format(custname), con=db)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ea2b3dc0",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.read_sql(\"\"\" SELECT\n",
    "                MAX(CASE WHEN Technique_Name = 'Initial Access' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT * FROM vw_GetAlertIdByMitre WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Initial Access\" AND CustomerName='{}' ORDER BY StartTime DESC) as sub1),0) ) END) AS Initial_Access,\n",
    "                MAX(CASE WHEN Technique_Name = 'Execution' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT * FROM vw_GetAlertIdByMitre WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Execution\" AND CustomerName='{}' ORDER BY StartTime DESC ) as sub2),0) ) END) AS Execution,\n",
    "                MAX(CASE WHEN Technique_Name = 'Persistence' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT * FROM vw_GetAlertIdByMitre WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Persistence\" AND CustomerName='{}' ORDER BY StartTime DESC) as sub3),0) ) END) AS Persistence,\n",
    "                MAX(CASE WHEN Technique_Name = 'Privilege Escalation' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT * FROM vw_GetAlertIdByMitre WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Privilege Escalation\" AND CustomerName='{}' ORDER BY StartTime DESC) as sub4),0) ) END)  AS Privilege_Escalation,\n",
    "                MAX(CASE WHEN Technique_Name = 'Defense Evasion' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT * FROM vw_GetAlertIdByMitre WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Defense Evasion\" AND CustomerName='{}' ORDER BY StartTime DESC) as sub5),0) ) END)  AS Defense_Evasion,\n",
    "                MAX(CASE WHEN Technique_Name = 'Credential Access' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT *  FROM vw_GetAlertIdByMitre WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Credential Access\" AND CustomerName='{}' ORDER BY StartTime DESC) as sub6),0) ) END)  AS Credential_Access,\n",
    "                MAX(CASE WHEN Technique_Name = 'Discovery' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT * FROM vw_GetAlertIdByMitre WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Discovery\" AND CustomerName='{}' ORDER BY StartTime DESC) as sub7),0) ) END)  AS Discovery,\n",
    "                MAX(CASE WHEN Technique_Name = 'Lateral Movement' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT * FROM vw_GetAlertIdByMitre WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Lateral Movement\" AND CustomerName='{}' ORDER BY StartTime DESC) as sub8),0) ) END)  AS Lateral_Movement,\n",
    "                MAX(CASE WHEN Technique_Name = 'Collection' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT * FROM vw_GetAlertIdByMitre WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Collection\" AND CustomerName='{}' ORDER BY StartTime DESC) as sub9),0) ) END)  AS Collection,\n",
    "                MAX(CASE WHEN Technique_Name = 'Command and Control' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT * FROM vw_GetAlertIdByMitre WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Command and Control\" AND CustomerName='{}' ORDER BY StartTime DESC) as sub10),0) ) END)  AS Command_and_Control,\n",
    "                MAX(CASE WHEN Technique_Name = 'Exfiltration' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT * FROM vw_GetAlertIdByMitre WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Exfiltration\" AND CustomerName='{}' ORDER BY StartTime DESC) as sub11),0) ) END)  AS Exfiltration,\n",
    "                MAX(CASE WHEN Technique_Name = 'Impact' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT * FROM vw_GetAlertIdByMitre WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Impact\" AND CustomerName='{}' ORDER BY StartTime DESC) as sub12),0) ) END)  AS Impact,\n",
    "                MAX(CASE WHEN Technique_Name = 'Resource Development' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT * FROM vw_GetAlertIdByMitre WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Resource Development\" AND CustomerName='{}' ORDER BY StartTime DESC) as sub13),0) ) END)  AS Resource_Development,\n",
    "                MAX(CASE WHEN Technique_Name = 'Reconnaissance' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT * FROM vw_GetAlertIdByMitre WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Reconnaissance\" AND CustomerName='{}' ORDER BY StartTime DESC) as sub14),0) ) END)  AS Reconnaissance\n",
    "    \n",
    "    \n",
    "                     FROM Rule_MitreTechnique\n",
    "                     GROUP BY Technique_Name,Sub_Technique\"\"\".format(custname, custname, custname, custname, custname,\n",
    "                                                                     custname, custname, custname, custname, custname,\n",
    "                                                                     custname, custname, custname, custname), con=db)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "eccffe3c",
   "metadata": {},
   "outputs": [],
   "source": [
    "d=pd.read_sql('''select * from vw_GetAlertIdByMitre order by StartTime DESC''',con=db)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "16c93aac",
   "metadata": {},
   "outputs": [],
   "source": [
    "r"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "046150a7",
   "metadata": {},
   "outputs": [],
   "source": [
    "r=pd.read_sql('''select * from Rule_MitreTechnique''',con=db)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5ad06b33",
   "metadata": {},
   "outputs": [],
   "source": [
    "d"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ce493695",
   "metadata": {},
   "outputs": [],
   "source": [
    "result = sqldf(\"\"\" SELECT\n",
    "                MAX(CASE WHEN Technique_Name = 'Initial Access' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT * FROM d WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Initial Access\" AND CustomerName='{}' group by RuleId),0) ) END) AS Initial_Access,\n",
    "                MAX(CASE WHEN Technique_Name = 'Execution' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT * FROM d WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Execution\" AND CustomerName='{}' group by RuleId ) as sub2),0) ) END) AS Execution,\n",
    "                MAX(CASE WHEN Technique_Name = 'Persistence' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT * FROM d WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Persistence\" AND CustomerName='{}' group by RuleId) as sub3),0) ) END) AS Persistence,\n",
    "                MAX(CASE WHEN Technique_Name = 'Privilege Escalation' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM (SELECT * FROM d WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Privilege Escalation\" AND CustomerName='{}' group by RuleId) as sub4),0) ) END)  AS Privilege_Escalation,\n",
    "                MAX(CASE WHEN Technique_Name = 'Defense Evasion' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT * FROM d WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Defense Evasion\" AND CustomerName='{}' group by RuleId) as sub5),0) ) END)  AS Defense_Evasion,\n",
    "                MAX(CASE WHEN Technique_Name = 'Credential Access' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT *  FROM d WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Credential Access\" AND CustomerName='{}' group by RuleId) as sub6),0) ) END)  AS Credential_Access,\n",
    "                MAX(CASE WHEN Technique_Name = 'Discovery' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT * FROM d WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Discovery\" AND CustomerName='{}' group by RuleId) as sub7),0) ) END)  AS Discovery,\n",
    "                MAX(CASE WHEN Technique_Name = 'Lateral Movement' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT * FROM d WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Lateral Movement\" AND CustomerName='{}' group by RuleId) as sub8),0) ) END)  AS Lateral_Movement,\n",
    "                MAX(CASE WHEN Technique_Name = 'Collection' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT * FROM d WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Collection\" AND CustomerName='{}' group by RuleId) as sub9),0) ) END)  AS Collection,\n",
    "                MAX(CASE WHEN Technique_Name = 'Command and Control' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT * FROM d WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Command and Control\" AND CustomerName='{}' group by RuleId) as sub10),0) ) END)  AS Command_and_Control,\n",
    "                MAX(CASE WHEN Technique_Name = 'Exfiltration' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT * FROM d WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Exfiltration\" AND CustomerName='{}' group by RuleId) as sub11),0) ) END)  AS Exfiltration,\n",
    "                MAX(CASE WHEN Technique_Name = 'Impact' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT * FROM d WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Impact\" AND CustomerName='{}' group by RuleId) as sub12),0) ) END)  AS Impact,\n",
    "                MAX(CASE WHEN Technique_Name = 'Resource Development' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT * FROM d WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Resource Development\" AND CustomerName='{}' group by RuleId) as sub13),0) ) END)  AS Resource_Development,\n",
    "                MAX(CASE WHEN Technique_Name = 'Reconnaissance' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT * FROM d WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Reconnaissance\" AND CustomerName='{}' group by RuleId) as sub14),0) ) END)  AS Reconnaissance\n",
    "    \n",
    "    \n",
    "                     FROM r\n",
    "                     GROUP BY Technique_Name,Sub_Technique\"\"\".format(custname, custname, custname, custname, custname,\n",
    "                                                                     custname, custname, custname, custname, custname,\n",
    "                                                                     custname, custname, custname, custname))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "95b85a13",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.read_sql('''(SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM (select * from vw_GetAlertIdByMitre order by StartTime DESC) as sub1''',con=db)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ff2f4260",
   "metadata": {},
   "outputs": [],
   "source": [
    " result = pd.read_sql(\"\"\" SELECT\n",
    "                MAX(CASE WHEN Technique_Name = 'Initial Access' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT Alert_Id FROM vw_GetAlertIdByMitre WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Initial Access\" AND CustomerName='{}' GROUP BY RuleId ORDER BY StartTime DESC) as sub1),0) ) END) AS Initial_Access,\n",
    "                MAX(CASE WHEN Technique_Name = 'Execution' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT Alert_Id FROM vw_GetAlertIdByMitre WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Execution\" AND CustomerName='{}' GROUP BY RuleId ORDER BY StartTime DESC ) as sub2),0) ) END) AS Execution,\n",
    "                MAX(CASE WHEN Technique_Name = 'Persistence' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT Alert_Id FROM vw_GetAlertIdByMitre WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Persistence\" AND CustomerName='{}' GROUP BY RuleId ORDER BY StartTime DESC) as sub3),0) ) END) AS Persistence,\n",
    "                MAX(CASE WHEN Technique_Name = 'Privilege Escalation' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT Alert_Id FROM vw_GetAlertIdByMitre WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Privilege Escalation\" AND CustomerName='{}' GROUP BY RuleId ORDER BY StartTime DESC) as sub4),0) ) END)  AS Privilege_Escalation,\n",
    "                MAX(CASE WHEN Technique_Name = 'Defense Evasion' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT Alert_Id FROM vw_GetAlertIdByMitre WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Defense Evasion\" AND CustomerName='{}' GROUP BY RuleId ORDER BY StartTime DESC) as sub5),0) ) END)  AS Defense_Evasion,\n",
    "                MAX(CASE WHEN Technique_Name = 'Credential Access' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT Alert_Id , RuleId FROM vw_GetAlertIdByMitre WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Credential Access\" AND CustomerName='{}' GROUP BY RuleId ORDER BY StartTime DESC) as sub6),0) ) END)  AS Credential_Access,\n",
    "                MAX(CASE WHEN Technique_Name = 'Discovery' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT Alert_Id FROM vw_GetAlertIdByMitre WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Discovery\" AND CustomerName='{}' GROUP BY RuleId ORDER BY StartTime DESC) as sub7),0) ) END)  AS Discovery,\n",
    "                MAX(CASE WHEN Technique_Name = 'Lateral Movement' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT Alert_Id FROM vw_GetAlertIdByMitre WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Lateral Movement\" AND CustomerName='{}' GROUP BY RuleId ORDER BY StartTime DESC) as sub8),0) ) END)  AS Lateral_Movement,\n",
    "                MAX(CASE WHEN Technique_Name = 'Collection' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT Alert_Id FROM vw_GetAlertIdByMitre WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Collection\" AND CustomerName='{}' GROUP BY RuleId ORDER BY StartTime DESC) as sub9),0) ) END)  AS Collection,\n",
    "                MAX(CASE WHEN Technique_Name = 'Command and Control' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT Alert_Id FROM vw_GetAlertIdByMitre WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Command and Control\" AND CustomerName='{}' GROUP BY RuleId ORDER BY StartTime DESC) as sub10),0) ) END)  AS Command_and_Control,\n",
    "                MAX(CASE WHEN Technique_Name = 'Exfiltration' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT Alert_Id FROM vw_GetAlertIdByMitre WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Exfiltration\" AND CustomerName='{}' GROUP BY RuleId ORDER BY StartTime DESC) as sub11),0) ) END)  AS Exfiltration,\n",
    "                MAX(CASE WHEN Technique_Name = 'Impact' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT Alert_Id FROM vw_GetAlertIdByMitre WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Impact\" AND CustomerName='{}' GROUP BY RuleId ORDER BY StartTime DESC) as sub12),0) ) END)  AS Impact,\n",
    "                MAX(CASE WHEN Technique_Name = 'Resource Development' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT Alert_Id FROM vw_GetAlertIdByMitre WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Resource Development\" AND CustomerName='{}' GROUP BY RuleId ORDER BY StartTime DESC) as sub13),0) ) END)  AS Resource_Development,\n",
    "                MAX(CASE WHEN Technique_Name = 'Reconnaissance' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT Alert_Id FROM vw_GetAlertIdByMitre WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Reconnaissance\" AND CustomerName='{}' GROUP BY RuleId ORDER BY StartTime DESC) as sub14),0) ) END)  AS Reconnaissance\n",
    "    \n",
    "    \n",
    "                     FROM Rule_MitreTechnique\n",
    "                     GROUP BY Technique_Name,Sub_Technique\"\"\".format(custname, custname, custname, custname, custname,\n",
    "                                                                     custname, custname, custname, custname, custname,\n",
    "                                                                     custname, custname, custname, custname), con=db)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6f0ee104",
   "metadata": {},
   "outputs": [],
   "source": [
    "result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f9a7311d",
   "metadata": {},
   "outputs": [],
   "source": [
    "result.to_string()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "23f259cb",
   "metadata": {},
   "outputs": [],
   "source": [
    "d=pd.read_sql('''select * from vw_GetAlertIdByMitre order by StartTime DESC''',con=db)\n",
    "sqldf(\"select * from d group by RuleId\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e4a57d90",
   "metadata": {},
   "outputs": [],
   "source": [
    "d"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dff48a4a",
   "metadata": {},
   "outputs": [],
   "source": [
    "db = pymysql.connect(host=\"10.172.194.43\", user=\"root\", password=\"Sattrix@123\", database=\"NewEvol\")\n",
    "d=pd.read_sql('''select * from vw_GetAlertIdByMitre group by RuleId order by StartTime DESC''',con=db)\n",
    "d"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "36924932",
   "metadata": {},
   "outputs": [],
   "source": [
    "d=pd.read_sql('''select * from vw_GetAlertIdByMitre order by StartTime DESC''',con=db)\n",
    "sqldf(\"select * from d group by RuleId\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "111649e0",
   "metadata": {},
   "outputs": [],
   "source": [
    "select l.* from table l inner join (select m_id, max(timestamp) as latest from table group by m_id)\n",
    "r\n",
    "  on l.timestamp = r.latest and l.m_id = r.m_id\n",
    "order by timestamp desc"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "120d0358",
   "metadata": {},
   "outputs": [],
   "source": [
    "d=pd.read_sql('''select * from table vw_GetAlertIdByMitre inner join (select RuleId, max(StartTime) as\n",
    "                latest from table group by RuleId)r on StartTime =r.latest and RuleId=r.RuleId\n",
    "                order by StartTime DESC''',con=db)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "81814cd7",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.read_sql('''select Alert_Id,StartTime from(SELECT RuleId, Alert_Id,StartTime FROM \n",
    "                (SELECT * FROM vw_GetAlertIdByMitre ORDER BY StartTime DESC) \n",
    "                as d order BY d.StartTime DESC) as r group by RuleId''',con=db)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "28bf4b0d",
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT RuleId, Alert_Id FROM (SELECT * FROM vw_GetAlertIdByMitre ORDER BY StartTime DESC) as new GROUP BY RuleId ;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f1cfdc98",
   "metadata": {},
   "outputs": [],
   "source": [
    "'''SELECT\n",
    "                MAX(CASE WHEN Technique_Name = 'Initial Access' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT Alert_Id FROM vw_GetAlertIdByMitre WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Initial Access\" AND CustomerName='{}' GROUP BY RuleId ORDER BY StartTime DESC) as sub1),0) ) END) AS Initial_Access,\n",
    "                MAX(CASE WHEN Technique_Name = 'Execution' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT Alert_Id FROM vw_GetAlertIdByMitre WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Execution\" AND CustomerName='{}' GROUP BY RuleId ORDER BY StartTime DESC ) as sub2),0) ) END) AS Execution,\n",
    "                MAX(CASE WHEN Technique_Name = 'Persistence' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT Alert_Id FROM vw_GetAlertIdByMitre WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Persistence\" AND CustomerName='{}' GROUP BY RuleId ORDER BY StartTime DESC) as sub3),0) ) END) AS Persistence,\n",
    "                MAX(CASE WHEN Technique_Name = 'Privilege Escalation' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT Alert_Id FROM vw_GetAlertIdByMitre WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Privilege Escalation\" AND CustomerName='{}' GROUP BY RuleId ORDER BY StartTime DESC) as sub4),0) ) END)  AS Privilege_Escalation,\n",
    "                MAX(CASE WHEN Technique_Name = 'Defense Evasion' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT Alert_Id FROM vw_GetAlertIdByMitre WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Defense Evasion\" AND CustomerName='{}'  GROUP BY RuleId ORDER BY StartTime DESC) as sub5),0) ) END)  AS Defense_Evasion,\n",
    "                MAX(CASE WHEN Technique_Name = 'Credential Access' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT Alert_Id , RuleId FROM vw_GetAlertIdByMitre WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Credential Access\" AND CustomerName='{}'   GROUP BY RuleId ORDER BY StartTime DESC) as sub6),0) ) END)  AS Credential_Access,\n",
    "                MAX(CASE WHEN Technique_Name = 'Discovery' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT Alert_Id FROM vw_GetAlertIdByMitre WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Discovery\" AND CustomerName='{}' GROUP BY RuleId ORDER BY StartTime DESC) as sub7),0) ) END)  AS Discovery,\n",
    "                MAX(CASE WHEN Technique_Name = 'Lateral Movement' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT Alert_Id FROM vw_GetAlertIdByMitre WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Lateral Movement\" AND CustomerName='{}' GROUP BY RuleId ORDER BY StartTime DESC) as sub8),0) ) END)  AS Lateral_Movement,\n",
    "                MAX(CASE WHEN Technique_Name = 'Collection' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT Alert_Id FROM vw_GetAlertIdByMitre WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Collection\" AND CustomerName='{}' GROUP BY RuleId ORDER BY StartTime DESC) as sub9),0) ) END)  AS Collection,\n",
    "                MAX(CASE WHEN Technique_Name = 'Command and Control' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT Alert_Id FROM vw_GetAlertIdByMitre WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Command and Control\" AND CustomerName='{}' GROUP BY RuleId ORDER BY StartTime DESC) as sub10),0) ) END)  AS Command_and_Control,\n",
    "                MAX(CASE WHEN Technique_Name = 'Exfiltration' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT Alert_Id FROM vw_GetAlertIdByMitre WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Exfiltration\" AND CustomerName='{}' GROUP BY RuleId ORDER BY StartTime DESC) as sub11),0) ) END)  AS Exfiltration,\n",
    "                MAX(CASE WHEN Technique_Name = 'Impact' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT Alert_Id FROM vw_GetAlertIdByMitre WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Impact\" AND CustomerName='{}' GROUP BY RuleId ORDER BY StartTime DESC) as sub12),0) ) END)  AS Impact,\n",
    "                MAX(CASE WHEN Technique_Name = 'Resource Development' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT Alert_Id FROM vw_GetAlertIdByMitre WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Resource Development\" AND CustomerName='{}' GROUP BY RuleId ORDER BY StartTime DESC) as sub13),0) ) END)  AS Resource_Development,\n",
    "                MAX(CASE WHEN Technique_Name = 'Reconnaissance' THEN CONCAT(Sub_Technique,'#',IFNULL((SELECT GROUP_CONCAT(Alert_Id ORDER BY Alert_Id) AS Alert_Id FROM(SELECT Alert_Id FROM vw_GetAlertIdByMitre WHERE MitreSubName = Sub_Technique AND Rule_MITREName = \"Reconnaissance\" AND CustomerName='{}' GROUP BY RuleId ORDER BY StartTime DESC) as sub14),0) ) END)  AS Reconnaissance\n",
    "    \n",
    "    \n",
    "                     FROM Rule_MitreTechnique\n",
    "                     GROUP BY Technique_Name,Sub_Technique'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "620ecb0e",
   "metadata": {},
   "outputs": [],
   "source": [
    "'''SELECT MAX(CASE WHEN Technique_Name = 'Initial Access' THEN CONCAT(Sub_Technique,'#',IFNULL(SELECT MAX(Alert_Id)\n",
    ",RuleId,StartTime  FROM (select * from NewEvol.vw_GetAlertIdByMitre WHERE  Rule_MITREName = \"Credential Access\"\n",
    "AND CustomerName='SATTRIX' order by StartTime DESC) as d  group BY d.RuleId),0) ) END) AS Initial_Access'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "2200590c",
   "metadata": {},
   "outputs": [],
   "source": [
    "db = pymysql.connect(host=\"10.172.194.43\", user=\"root\", password=\"Sattrix@123\", database=\"NewEvol\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "bf361705",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/tmp/ipykernel_35252/3707728540.py:1: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.\n",
      "  result = pd.read_sql(\"\"\" SELECT ROW_NUMBER(start=0) Over (Order by LogsCreateDate  DESC) As SR, logs_adminmodule.UserId, User.UserName, logs_adminmodule.LogsCreateDate,logs_adminmodule.LogsDetails,logs_adminmodule.TableAffected FROM logs_adminmodule\n"
     ]
    },
    {
     "ename": "DatabaseError",
     "evalue": "Execution failed on sql ' SELECT ROW_NUMBER(start=0) Over (Order by LogsCreateDate  DESC) As SR, logs_adminmodule.UserId, User.UserName, logs_adminmodule.LogsCreateDate,logs_adminmodule.LogsDetails,logs_adminmodule.TableAffected FROM logs_adminmodule\n                                       INNER JOIN `User` ON logs_adminmodule.UserId = User.UserId\n                                       order by UserId DESC limit 0,10': (1064, \"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'start=0) Over (Order by LogsCreateDate  DESC) As SR, logs_adminmodule.UserId, Us' at line 1\")",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mProgrammingError\u001b[0m                          Traceback (most recent call last)",
      "File \u001b[0;32m~/.local/lib/python3.10/site-packages/pandas/io/sql.py:2202\u001b[0m, in \u001b[0;36mSQLiteDatabase.execute\u001b[0;34m(self, sql, params)\u001b[0m\n\u001b[1;32m   2201\u001b[0m \u001b[38;5;28;01mtry\u001b[39;00m:\n\u001b[0;32m-> 2202\u001b[0m     \u001b[43mcur\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mexecute\u001b[49m\u001b[43m(\u001b[49m\u001b[43msql\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[38;5;241;43m*\u001b[39;49m\u001b[43margs\u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m   2203\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m cur\n",
      "File \u001b[0;32m~/.local/lib/python3.10/site-packages/pymysql/cursors.py:153\u001b[0m, in \u001b[0;36mCursor.execute\u001b[0;34m(self, query, args)\u001b[0m\n\u001b[1;32m    151\u001b[0m query \u001b[38;5;241m=\u001b[39m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mmogrify(query, args)\n\u001b[0;32m--> 153\u001b[0m result \u001b[38;5;241m=\u001b[39m \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m_query\u001b[49m\u001b[43m(\u001b[49m\u001b[43mquery\u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m    154\u001b[0m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_executed \u001b[38;5;241m=\u001b[39m query\n",
      "File \u001b[0;32m~/.local/lib/python3.10/site-packages/pymysql/cursors.py:322\u001b[0m, in \u001b[0;36mCursor._query\u001b[0;34m(self, q)\u001b[0m\n\u001b[1;32m    321\u001b[0m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_clear_result()\n\u001b[0;32m--> 322\u001b[0m \u001b[43mconn\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mquery\u001b[49m\u001b[43m(\u001b[49m\u001b[43mq\u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m    323\u001b[0m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_do_get_result()\n",
      "File \u001b[0;32m~/.local/lib/python3.10/site-packages/pymysql/connections.py:558\u001b[0m, in \u001b[0;36mConnection.query\u001b[0;34m(self, sql, unbuffered)\u001b[0m\n\u001b[1;32m    557\u001b[0m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_execute_command(COMMAND\u001b[38;5;241m.\u001b[39mCOM_QUERY, sql)\n\u001b[0;32m--> 558\u001b[0m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_affected_rows \u001b[38;5;241m=\u001b[39m \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m_read_query_result\u001b[49m\u001b[43m(\u001b[49m\u001b[43munbuffered\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43munbuffered\u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m    559\u001b[0m \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_affected_rows\n",
      "File \u001b[0;32m~/.local/lib/python3.10/site-packages/pymysql/connections.py:822\u001b[0m, in \u001b[0;36mConnection._read_query_result\u001b[0;34m(self, unbuffered)\u001b[0m\n\u001b[1;32m    821\u001b[0m     result \u001b[38;5;241m=\u001b[39m MySQLResult(\u001b[38;5;28mself\u001b[39m)\n\u001b[0;32m--> 822\u001b[0m     \u001b[43mresult\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mread\u001b[49m\u001b[43m(\u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m    823\u001b[0m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_result \u001b[38;5;241m=\u001b[39m result\n",
      "File \u001b[0;32m~/.local/lib/python3.10/site-packages/pymysql/connections.py:1200\u001b[0m, in \u001b[0;36mMySQLResult.read\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m   1199\u001b[0m \u001b[38;5;28;01mtry\u001b[39;00m:\n\u001b[0;32m-> 1200\u001b[0m     first_packet \u001b[38;5;241m=\u001b[39m \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mconnection\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m_read_packet\u001b[49m\u001b[43m(\u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m   1202\u001b[0m     \u001b[38;5;28;01mif\u001b[39;00m first_packet\u001b[38;5;241m.\u001b[39mis_ok_packet():\n",
      "File \u001b[0;32m~/.local/lib/python3.10/site-packages/pymysql/connections.py:772\u001b[0m, in \u001b[0;36mConnection._read_packet\u001b[0;34m(self, packet_type)\u001b[0m\n\u001b[1;32m    771\u001b[0m         \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_result\u001b[38;5;241m.\u001b[39munbuffered_active \u001b[38;5;241m=\u001b[39m \u001b[38;5;28;01mFalse\u001b[39;00m\n\u001b[0;32m--> 772\u001b[0m     \u001b[43mpacket\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mraise_for_error\u001b[49m\u001b[43m(\u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m    773\u001b[0m \u001b[38;5;28;01mreturn\u001b[39;00m packet\n",
      "File \u001b[0;32m~/.local/lib/python3.10/site-packages/pymysql/protocol.py:221\u001b[0m, in \u001b[0;36mMysqlPacket.raise_for_error\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m    220\u001b[0m     \u001b[38;5;28mprint\u001b[39m(\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124merrno =\u001b[39m\u001b[38;5;124m\"\u001b[39m, errno)\n\u001b[0;32m--> 221\u001b[0m \u001b[43merr\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mraise_mysql_exception\u001b[49m\u001b[43m(\u001b[49m\u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m_data\u001b[49m\u001b[43m)\u001b[49m\n",
      "File \u001b[0;32m~/.local/lib/python3.10/site-packages/pymysql/err.py:143\u001b[0m, in \u001b[0;36mraise_mysql_exception\u001b[0;34m(data)\u001b[0m\n\u001b[1;32m    142\u001b[0m     errorclass \u001b[38;5;241m=\u001b[39m InternalError \u001b[38;5;28;01mif\u001b[39;00m errno \u001b[38;5;241m<\u001b[39m \u001b[38;5;241m1000\u001b[39m \u001b[38;5;28;01melse\u001b[39;00m OperationalError\n\u001b[0;32m--> 143\u001b[0m \u001b[38;5;28;01mraise\u001b[39;00m errorclass(errno, errval)\n",
      "\u001b[0;31mProgrammingError\u001b[0m: (1064, \"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'start=0) Over (Order by LogsCreateDate  DESC) As SR, logs_adminmodule.UserId, Us' at line 1\")",
      "\nThe above exception was the direct cause of the following exception:\n",
      "\u001b[0;31mDatabaseError\u001b[0m                             Traceback (most recent call last)",
      "Cell \u001b[0;32mIn[17], line 1\u001b[0m\n\u001b[0;32m----> 1\u001b[0m result \u001b[38;5;241m=\u001b[39m \u001b[43mpd\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mread_sql\u001b[49m\u001b[43m(\u001b[49m\u001b[38;5;124;43m\"\"\"\u001b[39;49m\u001b[38;5;124;43m SELECT ROW_NUMBER(start=0) Over (Order by LogsCreateDate  DESC) As SR, logs_adminmodule.UserId, User.UserName, logs_adminmodule.LogsCreateDate,logs_adminmodule.LogsDetails,logs_adminmodule.TableAffected FROM logs_adminmodule\u001b[39;49m\n\u001b[1;32m      2\u001b[0m \u001b[38;5;124;43m                                       INNER JOIN `User` ON logs_adminmodule.UserId = User.UserId\u001b[39;49m\n\u001b[1;32m      3\u001b[0m \u001b[38;5;124;43m                                       order by UserId DESC limit 0,10\u001b[39;49m\u001b[38;5;124;43m\"\"\"\u001b[39;49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mcon\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mdb\u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m      4\u001b[0m result\n",
      "File \u001b[0;32m~/.local/lib/python3.10/site-packages/pandas/io/sql.py:635\u001b[0m, in \u001b[0;36mread_sql\u001b[0;34m(sql, con, index_col, coerce_float, params, parse_dates, columns, chunksize, dtype_backend, dtype)\u001b[0m\n\u001b[1;32m    633\u001b[0m \u001b[38;5;28;01mwith\u001b[39;00m pandasSQL_builder(con) \u001b[38;5;28;01mas\u001b[39;00m pandas_sql:\n\u001b[1;32m    634\u001b[0m     \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;28misinstance\u001b[39m(pandas_sql, SQLiteDatabase):\n\u001b[0;32m--> 635\u001b[0m         \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[43mpandas_sql\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mread_query\u001b[49m\u001b[43m(\u001b[49m\n\u001b[1;32m    636\u001b[0m \u001b[43m            \u001b[49m\u001b[43msql\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m    637\u001b[0m \u001b[43m            \u001b[49m\u001b[43mindex_col\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mindex_col\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m    638\u001b[0m \u001b[43m            \u001b[49m\u001b[43mparams\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mparams\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m    639\u001b[0m \u001b[43m            \u001b[49m\u001b[43mcoerce_float\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mcoerce_float\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m    640\u001b[0m \u001b[43m            \u001b[49m\u001b[43mparse_dates\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mparse_dates\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m    641\u001b[0m \u001b[43m            \u001b[49m\u001b[43mchunksize\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mchunksize\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m    642\u001b[0m \u001b[43m            \u001b[49m\u001b[43mdtype_backend\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mdtype_backend\u001b[49m\u001b[43m,\u001b[49m\u001b[43m  \u001b[49m\u001b[38;5;66;43;03m# type: ignore[arg-type]\u001b[39;49;00m\n\u001b[1;32m    643\u001b[0m \u001b[43m            \u001b[49m\u001b[43mdtype\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mdtype\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m    644\u001b[0m \u001b[43m        \u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m    646\u001b[0m     \u001b[38;5;28;01mtry\u001b[39;00m:\n\u001b[1;32m    647\u001b[0m         _is_table_name \u001b[38;5;241m=\u001b[39m pandas_sql\u001b[38;5;241m.\u001b[39mhas_table(sql)\n",
      "File \u001b[0;32m~/.local/lib/python3.10/site-packages/pandas/io/sql.py:2266\u001b[0m, in \u001b[0;36mSQLiteDatabase.read_query\u001b[0;34m(self, sql, index_col, coerce_float, parse_dates, params, chunksize, dtype, dtype_backend)\u001b[0m\n\u001b[1;32m   2255\u001b[0m \u001b[38;5;28;01mdef\u001b[39;00m \u001b[38;5;21mread_query\u001b[39m(\n\u001b[1;32m   2256\u001b[0m     \u001b[38;5;28mself\u001b[39m,\n\u001b[1;32m   2257\u001b[0m     sql,\n\u001b[0;32m   (...)\u001b[0m\n\u001b[1;32m   2264\u001b[0m     dtype_backend: DtypeBackend \u001b[38;5;241m|\u001b[39m Literal[\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mnumpy\u001b[39m\u001b[38;5;124m\"\u001b[39m] \u001b[38;5;241m=\u001b[39m \u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mnumpy\u001b[39m\u001b[38;5;124m\"\u001b[39m,\n\u001b[1;32m   2265\u001b[0m ) \u001b[38;5;241m-\u001b[39m\u001b[38;5;241m>\u001b[39m DataFrame \u001b[38;5;241m|\u001b[39m Iterator[DataFrame]:\n\u001b[0;32m-> 2266\u001b[0m     cursor \u001b[38;5;241m=\u001b[39m \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mexecute\u001b[49m\u001b[43m(\u001b[49m\u001b[43msql\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mparams\u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m   2267\u001b[0m     columns \u001b[38;5;241m=\u001b[39m [col_desc[\u001b[38;5;241m0\u001b[39m] \u001b[38;5;28;01mfor\u001b[39;00m col_desc \u001b[38;5;129;01min\u001b[39;00m cursor\u001b[38;5;241m.\u001b[39mdescription]\n\u001b[1;32m   2269\u001b[0m     \u001b[38;5;28;01mif\u001b[39;00m chunksize \u001b[38;5;129;01mis\u001b[39;00m \u001b[38;5;129;01mnot\u001b[39;00m \u001b[38;5;28;01mNone\u001b[39;00m:\n",
      "File \u001b[0;32m~/.local/lib/python3.10/site-packages/pandas/io/sql.py:2214\u001b[0m, in \u001b[0;36mSQLiteDatabase.execute\u001b[0;34m(self, sql, params)\u001b[0m\n\u001b[1;32m   2211\u001b[0m     \u001b[38;5;28;01mraise\u001b[39;00m ex \u001b[38;5;28;01mfrom\u001b[39;00m \u001b[38;5;21;01minner_exc\u001b[39;00m\n\u001b[1;32m   2213\u001b[0m ex \u001b[38;5;241m=\u001b[39m DatabaseError(\u001b[38;5;124mf\u001b[39m\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mExecution failed on sql \u001b[39m\u001b[38;5;124m'\u001b[39m\u001b[38;5;132;01m{\u001b[39;00msql\u001b[38;5;132;01m}\u001b[39;00m\u001b[38;5;124m'\u001b[39m\u001b[38;5;124m: \u001b[39m\u001b[38;5;132;01m{\u001b[39;00mexc\u001b[38;5;132;01m}\u001b[39;00m\u001b[38;5;124m\"\u001b[39m)\n\u001b[0;32m-> 2214\u001b[0m \u001b[38;5;28;01mraise\u001b[39;00m ex \u001b[38;5;28;01mfrom\u001b[39;00m \u001b[38;5;21;01mexc\u001b[39;00m\n",
      "\u001b[0;31mDatabaseError\u001b[0m: Execution failed on sql ' SELECT ROW_NUMBER(start=0) Over (Order by LogsCreateDate  DESC) As SR, logs_adminmodule.UserId, User.UserName, logs_adminmodule.LogsCreateDate,logs_adminmodule.LogsDetails,logs_adminmodule.TableAffected FROM logs_adminmodule\n                                       INNER JOIN `User` ON logs_adminmodule.UserId = User.UserId\n                                       order by UserId DESC limit 0,10': (1064, \"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'start=0) Over (Order by LogsCreateDate  DESC) As SR, logs_adminmodule.UserId, Us' at line 1\")"
     ]
    }
   ],
   "source": [
    "result = pd.read_sql(\"\"\" SELECT ROW_NUMBER() Over (Order by LogsCreateDate  DESC) As SR, logs_adminmodule.UserId, User.UserName, logs_adminmodule.LogsCreateDate,logs_adminmodule.LogsDetails,logs_adminmodule.TableAffected FROM logs_adminmodule\n",
    "                                       INNER JOIN `User` ON logs_adminmodule.UserId = User.UserId\n",
    "                                       order by UserId DESC limit 0,10\"\"\", con=db)\n",
    "result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c5ed7b88",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "dbba8f3c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>UserId</th>\n",
       "      <th>UserName</th>\n",
       "      <th>LogsCreateDate</th>\n",
       "      <th>LogsDetails</th>\n",
       "      <th>TableAffected</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>405</td>\n",
       "      <td>naresh kumar</td>\n",
       "      <td>2023-09-07 14:31:09</td>\n",
       "      <td>User Updated</td>\n",
       "      <td>User</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>405</td>\n",
       "      <td>naresh kumar</td>\n",
       "      <td>2023-09-05 14:20:04</td>\n",
       "      <td>User Updated</td>\n",
       "      <td>User</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>405</td>\n",
       "      <td>naresh kumar</td>\n",
       "      <td>2023-09-05 16:24:49</td>\n",
       "      <td>User Updated</td>\n",
       "      <td>User</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>405</td>\n",
       "      <td>naresh kumar</td>\n",
       "      <td>2023-09-05 16:29:00</td>\n",
       "      <td>User Updated</td>\n",
       "      <td>User</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>405</td>\n",
       "      <td>naresh kumar</td>\n",
       "      <td>2023-09-06 14:16:46</td>\n",
       "      <td>User Updated</td>\n",
       "      <td>User</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>405</td>\n",
       "      <td>naresh kumar</td>\n",
       "      <td>2023-09-06 14:52:07</td>\n",
       "      <td>User Updated</td>\n",
       "      <td>User</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>405</td>\n",
       "      <td>naresh kumar</td>\n",
       "      <td>2023-09-07 11:55:49</td>\n",
       "      <td>User Updated</td>\n",
       "      <td>User</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>405</td>\n",
       "      <td>naresh kumar</td>\n",
       "      <td>2023-09-07 11:56:38</td>\n",
       "      <td>User Updated</td>\n",
       "      <td>User</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>405</td>\n",
       "      <td>naresh kumar</td>\n",
       "      <td>2023-09-07 11:57:09</td>\n",
       "      <td>User Updated</td>\n",
       "      <td>User</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>405</td>\n",
       "      <td>naresh kumar</td>\n",
       "      <td>2023-09-07 11:58:15</td>\n",
       "      <td>User Updated</td>\n",
       "      <td>User</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   UserId      UserName      LogsCreateDate   LogsDetails TableAffected\n",
       "0     405  naresh kumar 2023-09-07 14:31:09  User Updated          User\n",
       "1     405  naresh kumar 2023-09-05 14:20:04  User Updated          User\n",
       "2     405  naresh kumar 2023-09-05 16:24:49  User Updated          User\n",
       "3     405  naresh kumar 2023-09-05 16:29:00  User Updated          User\n",
       "4     405  naresh kumar 2023-09-06 14:16:46  User Updated          User\n",
       "5     405  naresh kumar 2023-09-06 14:52:07  User Updated          User\n",
       "6     405  naresh kumar 2023-09-07 11:55:49  User Updated          User\n",
       "7     405  naresh kumar 2023-09-07 11:56:38  User Updated          User\n",
       "8     405  naresh kumar 2023-09-07 11:57:09  User Updated          User\n",
       "9     405  naresh kumar 2023-09-07 11:58:15  User Updated          User"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "407db7f0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>UserId</th>\n",
       "      <th>UserName</th>\n",
       "      <th>LogsCreateDate</th>\n",
       "      <th>LogsDetails</th>\n",
       "      <th>TableAffected</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>405</td>\n",
       "      <td>naresh kumar</td>\n",
       "      <td>2023-09-07 14:31:09.000000</td>\n",
       "      <td>User Updated</td>\n",
       "      <td>User</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>405</td>\n",
       "      <td>naresh kumar</td>\n",
       "      <td>2023-09-05 14:20:04.000000</td>\n",
       "      <td>User Updated</td>\n",
       "      <td>User</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>405</td>\n",
       "      <td>naresh kumar</td>\n",
       "      <td>2023-09-05 16:24:49.000000</td>\n",
       "      <td>User Updated</td>\n",
       "      <td>User</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>405</td>\n",
       "      <td>naresh kumar</td>\n",
       "      <td>2023-09-05 16:29:00.000000</td>\n",
       "      <td>User Updated</td>\n",
       "      <td>User</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>405</td>\n",
       "      <td>naresh kumar</td>\n",
       "      <td>2023-09-06 14:16:46.000000</td>\n",
       "      <td>User Updated</td>\n",
       "      <td>User</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>405</td>\n",
       "      <td>naresh kumar</td>\n",
       "      <td>2023-09-06 14:52:07.000000</td>\n",
       "      <td>User Updated</td>\n",
       "      <td>User</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>405</td>\n",
       "      <td>naresh kumar</td>\n",
       "      <td>2023-09-07 11:55:49.000000</td>\n",
       "      <td>User Updated</td>\n",
       "      <td>User</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>405</td>\n",
       "      <td>naresh kumar</td>\n",
       "      <td>2023-09-07 11:56:38.000000</td>\n",
       "      <td>User Updated</td>\n",
       "      <td>User</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>405</td>\n",
       "      <td>naresh kumar</td>\n",
       "      <td>2023-09-07 11:57:09.000000</td>\n",
       "      <td>User Updated</td>\n",
       "      <td>User</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>405</td>\n",
       "      <td>naresh kumar</td>\n",
       "      <td>2023-09-07 11:58:15.000000</td>\n",
       "      <td>User Updated</td>\n",
       "      <td>User</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   UserId      UserName              LogsCreateDate   LogsDetails  \\\n",
       "0     405  naresh kumar  2023-09-07 14:31:09.000000  User Updated   \n",
       "1     405  naresh kumar  2023-09-05 14:20:04.000000  User Updated   \n",
       "2     405  naresh kumar  2023-09-05 16:24:49.000000  User Updated   \n",
       "3     405  naresh kumar  2023-09-05 16:29:00.000000  User Updated   \n",
       "4     405  naresh kumar  2023-09-06 14:16:46.000000  User Updated   \n",
       "5     405  naresh kumar  2023-09-06 14:52:07.000000  User Updated   \n",
       "6     405  naresh kumar  2023-09-07 11:55:49.000000  User Updated   \n",
       "7     405  naresh kumar  2023-09-07 11:56:38.000000  User Updated   \n",
       "8     405  naresh kumar  2023-09-07 11:57:09.000000  User Updated   \n",
       "9     405  naresh kumar  2023-09-07 11:58:15.000000  User Updated   \n",
       "\n",
       "  TableAffected  \n",
       "0          User  \n",
       "1          User  \n",
       "2          User  \n",
       "3          User  \n",
       "4          User  \n",
       "5          User  \n",
       "6          User  \n",
       "7          User  \n",
       "8          User  \n",
       "9          User  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from pandasql import sqldf\n",
    "sqldf('''select * from result''')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "0c2239c1",
   "metadata": {},
   "outputs": [],
   "source": [
    "def report_notification_alert(report_id, report_name, report_notification,customerName,save_location):\n",
    "    if report_notification == 1:\n",
    "        try:\n",
    "            EmailConfigHost = 'gmail'\n",
    "            EmailConfigPort = 587\n",
    "            EmailFromId = 'nareshsisodiya1996@gmail.com'\n",
    "            EmailPassword = 'yszbenixyrdrioei'\n",
    "#             emailto = ['sanchit.shewade@sattrixsoftware.com', 'shriram.toradmal@sattrixsoftware.com']\n",
    "            emailto=['naresh_kumar@sattrixsoftware.com','jyoti_pradhan@sattrixsoftware.com']\n",
    "            EmailSubject = 'Report_Alert'\n",
    "            EmailTypeDescr = \"Report Generated Successfully For Report Name {}  \\nThank's & Regards, \\n {}\".format(report_name,{EmailFromId})\n",
    "            msg = MIMEMultipart()\n",
    "            msg['From'] = EmailFromId\n",
    "            msg['To'] = \", \".join(emailto)\n",
    "            msg['Subject'] = EmailSubject\n",
    "            msg.attach(MIMEText(EmailTypeDescr))\n",
    "            mailserver = smtplib.SMTP(EmailConfigHost, EmailConfigPort)\n",
    "            mailserver.ehlo()\n",
    "            mailserver.starttls()\n",
    "            mailserver.ehlo()\n",
    "            print(\"Hello\")\n",
    "            mailserver.login(EmailFromId, EmailPassword)\n",
    "            print(\"Login to Email\")\n",
    "            mailserver.sendmail(EmailFromId, emailto, msg.as_string())\n",
    "            #print(\"Email has been sent.\")\n",
    "            # Should be mailServer.quit(), but that crashes...\n",
    "            mailserver.close()\n",
    "        except smtplib.SMTPDataError as e:\n",
    "            print(\"error while sending email: {}\".format(e))\n",
    "\n",
    "    if report_notification == 2:\n",
    "        dict3 = {'Report_Id': str(report_id), 'Report_Name': report_name,\"customerName\":customerName[1:-1]}\n",
    "        #print(dict3)\n",
    "        # dict3=json.dumps(dict3)\n",
    "        url = 'https://192.168.21.198:8082/api/rulereport'\n",
    "        response = requests.post(url, json=dict3,verify=False)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "166b9974",
   "metadata": {},
   "outputs": [
    {
     "ename": "gaierror",
     "evalue": "[Errno -3] Temporary failure in name resolution",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mgaierror\u001b[0m                                  Traceback (most recent call last)",
      "Cell \u001b[0;32mIn[38], line 6\u001b[0m\n\u001b[1;32m      4\u001b[0m customerName\u001b[38;5;241m=\u001b[39m\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mSATTRIX\u001b[39m\u001b[38;5;124m\"\u001b[39m\n\u001b[1;32m      5\u001b[0m save_location\u001b[38;5;241m=\u001b[39m\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124m/opt/NewEvol/Current/NewEvol_Solr/Flask_App/report_pdf/Snehal_report1_1688965612487.pdf\u001b[39m\u001b[38;5;124m\"\u001b[39m\n\u001b[0;32m----> 6\u001b[0m \u001b[43mreport_notification_alert\u001b[49m\u001b[43m(\u001b[49m\u001b[43mreport_id\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mreport_name\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mreport_notification\u001b[49m\u001b[43m,\u001b[49m\u001b[43mcustomerName\u001b[49m\u001b[43m,\u001b[49m\u001b[43msave_location\u001b[49m\u001b[43m)\u001b[49m\n",
      "Cell \u001b[0;32mIn[37], line 17\u001b[0m, in \u001b[0;36mreport_notification_alert\u001b[0;34m(report_id, report_name, report_notification, customerName, save_location)\u001b[0m\n\u001b[1;32m     15\u001b[0m msg[\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mSubject\u001b[39m\u001b[38;5;124m'\u001b[39m] \u001b[38;5;241m=\u001b[39m EmailSubject\n\u001b[1;32m     16\u001b[0m msg\u001b[38;5;241m.\u001b[39mattach(MIMEText(EmailTypeDescr))\n\u001b[0;32m---> 17\u001b[0m mailserver \u001b[38;5;241m=\u001b[39m \u001b[43msmtplib\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mSMTP\u001b[49m\u001b[43m(\u001b[49m\u001b[43mEmailConfigHost\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mEmailConfigPort\u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m     18\u001b[0m mailserver\u001b[38;5;241m.\u001b[39mehlo()\n\u001b[1;32m     19\u001b[0m mailserver\u001b[38;5;241m.\u001b[39mstarttls()\n",
      "File \u001b[0;32m/usr/lib/python3.10/smtplib.py:255\u001b[0m, in \u001b[0;36mSMTP.__init__\u001b[0;34m(self, host, port, local_hostname, timeout, source_address)\u001b[0m\n\u001b[1;32m    252\u001b[0m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_auth_challenge_count \u001b[38;5;241m=\u001b[39m \u001b[38;5;241m0\u001b[39m\n\u001b[1;32m    254\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m host:\n\u001b[0;32m--> 255\u001b[0m     (code, msg) \u001b[38;5;241m=\u001b[39m \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mconnect\u001b[49m\u001b[43m(\u001b[49m\u001b[43mhost\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mport\u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m    256\u001b[0m     \u001b[38;5;28;01mif\u001b[39;00m code \u001b[38;5;241m!=\u001b[39m \u001b[38;5;241m220\u001b[39m:\n\u001b[1;32m    257\u001b[0m         \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mclose()\n",
      "File \u001b[0;32m/usr/lib/python3.10/smtplib.py:341\u001b[0m, in \u001b[0;36mSMTP.connect\u001b[0;34m(self, host, port, source_address)\u001b[0m\n\u001b[1;32m    339\u001b[0m     port \u001b[38;5;241m=\u001b[39m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mdefault_port\n\u001b[1;32m    340\u001b[0m sys\u001b[38;5;241m.\u001b[39maudit(\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124msmtplib.connect\u001b[39m\u001b[38;5;124m\"\u001b[39m, \u001b[38;5;28mself\u001b[39m, host, port)\n\u001b[0;32m--> 341\u001b[0m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39msock \u001b[38;5;241m=\u001b[39m \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m_get_socket\u001b[49m\u001b[43m(\u001b[49m\u001b[43mhost\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mport\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mtimeout\u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m    342\u001b[0m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mfile \u001b[38;5;241m=\u001b[39m \u001b[38;5;28;01mNone\u001b[39;00m\n\u001b[1;32m    343\u001b[0m (code, msg) \u001b[38;5;241m=\u001b[39m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mgetreply()\n",
      "File \u001b[0;32m/usr/lib/python3.10/smtplib.py:312\u001b[0m, in \u001b[0;36mSMTP._get_socket\u001b[0;34m(self, host, port, timeout)\u001b[0m\n\u001b[1;32m    310\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mdebuglevel \u001b[38;5;241m>\u001b[39m \u001b[38;5;241m0\u001b[39m:\n\u001b[1;32m    311\u001b[0m     \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_print_debug(\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mconnect: to\u001b[39m\u001b[38;5;124m'\u001b[39m, (host, port), \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39msource_address)\n\u001b[0;32m--> 312\u001b[0m \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[43msocket\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mcreate_connection\u001b[49m\u001b[43m(\u001b[49m\u001b[43m(\u001b[49m\u001b[43mhost\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mport\u001b[49m\u001b[43m)\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mtimeout\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m    313\u001b[0m \u001b[43m                                \u001b[49m\u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43msource_address\u001b[49m\u001b[43m)\u001b[49m\n",
      "File \u001b[0;32m/usr/lib/python3.10/socket.py:824\u001b[0m, in \u001b[0;36mcreate_connection\u001b[0;34m(address, timeout, source_address)\u001b[0m\n\u001b[1;32m    822\u001b[0m host, port \u001b[38;5;241m=\u001b[39m address\n\u001b[1;32m    823\u001b[0m err \u001b[38;5;241m=\u001b[39m \u001b[38;5;28;01mNone\u001b[39;00m\n\u001b[0;32m--> 824\u001b[0m \u001b[38;5;28;01mfor\u001b[39;00m res \u001b[38;5;129;01min\u001b[39;00m \u001b[43mgetaddrinfo\u001b[49m\u001b[43m(\u001b[49m\u001b[43mhost\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mport\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[38;5;241;43m0\u001b[39;49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mSOCK_STREAM\u001b[49m\u001b[43m)\u001b[49m:\n\u001b[1;32m    825\u001b[0m     af, socktype, proto, canonname, sa \u001b[38;5;241m=\u001b[39m res\n\u001b[1;32m    826\u001b[0m     sock \u001b[38;5;241m=\u001b[39m \u001b[38;5;28;01mNone\u001b[39;00m\n",
      "File \u001b[0;32m/usr/lib/python3.10/socket.py:955\u001b[0m, in \u001b[0;36mgetaddrinfo\u001b[0;34m(host, port, family, type, proto, flags)\u001b[0m\n\u001b[1;32m    952\u001b[0m \u001b[38;5;66;03m# We override this function since we want to translate the numeric family\u001b[39;00m\n\u001b[1;32m    953\u001b[0m \u001b[38;5;66;03m# and socket type values to enum constants.\u001b[39;00m\n\u001b[1;32m    954\u001b[0m addrlist \u001b[38;5;241m=\u001b[39m []\n\u001b[0;32m--> 955\u001b[0m \u001b[38;5;28;01mfor\u001b[39;00m res \u001b[38;5;129;01min\u001b[39;00m \u001b[43m_socket\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mgetaddrinfo\u001b[49m\u001b[43m(\u001b[49m\u001b[43mhost\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mport\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mfamily\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[38;5;28;43mtype\u001b[39;49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mproto\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mflags\u001b[49m\u001b[43m)\u001b[49m:\n\u001b[1;32m    956\u001b[0m     af, socktype, proto, canonname, sa \u001b[38;5;241m=\u001b[39m res\n\u001b[1;32m    957\u001b[0m     addrlist\u001b[38;5;241m.\u001b[39mappend((_intenum_converter(af, AddressFamily),\n\u001b[1;32m    958\u001b[0m                      _intenum_converter(socktype, SocketKind),\n\u001b[1;32m    959\u001b[0m                      proto, canonname, sa))\n",
      "\u001b[0;31mgaierror\u001b[0m: [Errno -3] Temporary failure in name resolution"
     ]
    }
   ],
   "source": [
    "report_id=123\n",
    "report_name=\"chart and table\"\n",
    "report_notification=1\n",
    "customerName=\"SATTRIX\"\n",
    "save_location=\"/opt/NewEvol/Current/NewEvol_Solr/Flask_App/report_pdf/Snehal_report1_1688965612487.pdf\"\n",
    "report_notification_alert(report_id, report_name, report_notification,customerName,save_location)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "46744e24",
   "metadata": {},
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'os' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "Cell \u001b[0;32mIn[42], line 95\u001b[0m\n\u001b[1;32m     93\u001b[0m         \u001b[38;5;28;01mexcept\u001b[39;00m \u001b[38;5;167;01mException\u001b[39;00m \u001b[38;5;28;01mas\u001b[39;00m e:\n\u001b[1;32m     94\u001b[0m              \u001b[38;5;28;01mreturn\u001b[39;00m jsonify({\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mmsg\u001b[39m\u001b[38;5;124m\"\u001b[39m:\u001b[38;5;28mstr\u001b[39m(e)})\n\u001b[0;32m---> 95\u001b[0m \u001b[43mreport_notification_alert\u001b[49m\u001b[43m(\u001b[49m\u001b[43mreport_id\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mreport_name\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mreport_notification\u001b[49m\u001b[43m,\u001b[49m\u001b[43mcustomerName\u001b[49m\u001b[43m,\u001b[49m\u001b[43msave_location\u001b[49m\u001b[43m)\u001b[49m\n",
      "Cell \u001b[0;32mIn[42], line 45\u001b[0m, in \u001b[0;36mreport_notification_alert\u001b[0;34m(report_id, report_name, report_notification, customerName, save_location)\u001b[0m\n\u001b[1;32m     40\u001b[0m     mime_type \u001b[38;5;241m=\u001b[39m \u001b[38;5;124m'\u001b[39m\u001b[38;5;124mapplication/octet-stream\u001b[39m\u001b[38;5;124m'\u001b[39m\n\u001b[1;32m     44\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m pdf_file_path\u001b[38;5;241m.\u001b[39mendswith(\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124m.pdf\u001b[39m\u001b[38;5;124m\"\u001b[39m):\n\u001b[0;32m---> 45\u001b[0m     attachment \u001b[38;5;241m=\u001b[39m MIMEBase(mime_type, \u001b[38;5;124m'\u001b[39m\u001b[38;5;124mpdf\u001b[39m\u001b[38;5;124m'\u001b[39m, filename\u001b[38;5;241m=\u001b[39m\u001b[43mos\u001b[49m\u001b[38;5;241m.\u001b[39mpath\u001b[38;5;241m.\u001b[39mbasename(pdf_file_path))\n\u001b[1;32m     46\u001b[0m \u001b[38;5;28;01melif\u001b[39;00m pdf_file_path\u001b[38;5;241m.\u001b[39mendswith(\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124m.xlsx\u001b[39m\u001b[38;5;124m\"\u001b[39m) \u001b[38;5;129;01mor\u001b[39;00m pdf_file_path\u001b[38;5;241m.\u001b[39mendswith(\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mxls\u001b[39m\u001b[38;5;124m'\u001b[39m):\n\u001b[1;32m     47\u001b[0m     attachment \u001b[38;5;241m=\u001b[39m MIMEBase(mime_type, \u001b[38;5;124m'\u001b[39m\u001b[38;5;124mxlsx\u001b[39m\u001b[38;5;124m'\u001b[39m, filename\u001b[38;5;241m=\u001b[39mos\u001b[38;5;241m.\u001b[39mpath\u001b[38;5;241m.\u001b[39mbasename(pdf_file_path))\n",
      "\u001b[0;31mNameError\u001b[0m: name 'os' is not defined"
     ]
    }
   ],
   "source": [
    "import matplotlib.pyplot as plt\n",
    "import pandas as pd\n",
    "import smtplib\n",
    "from email.mime.multipart import MIMEMultipart\n",
    "from email.mime.text import MIMEText\n",
    "import mimetypes\n",
    "from email.mime.base import MIMEBase\n",
    "from email import encoders\n",
    "from datetime import datetime\n",
    "report_id=123\n",
    "report_name=\"chart and table\"\n",
    "report_notification=1\n",
    "customerName=\"SATTRIX\"\n",
    "save_location=\"/opt/NewEvol/Current/NewEvol_Solr/Flask_App/report_pdf/Snehal_report1_1688965612487.pdf\"\n",
    "def report_notification_alert(report_id, report_name, report_notification,customerName,save_location):\n",
    "    if report_notification == 1:\n",
    "        try:\n",
    "            EmailConfigHost = 'smtp.office365.com'\n",
    "            EmailConfigPort = 587\n",
    "            EmailFromId = 'nareshsisodiya1996@gmail.com'\n",
    "            EmailPassword = 'yszbenixyrdrioei'\n",
    "            pdf_file_path=save_location\n",
    "#             mysql=pymysql.connect()\n",
    "#             cur = mysql.connection.cursor()\n",
    "#             cur.execute(\"SELECT Report_EmailTo FROM Report_ListInfo WHERE Report_Id={}\".format(report_id))\n",
    "            emailto=['naresh_kumar@sattrixsoftware.com','jyoti_pradhan@sattrixsoftware.com']\n",
    "#             emailto=emailto[\"Report_EmailTo\"].split(\",\") #['sanchit.shewade@sattrixsoftware.com', 'shriram.toradmal@sattrixsoftware.com']\n",
    "            EmailSubject = 'Report_Alert'\n",
    "            EmailTypeDescr = \"Report Generated Successfully For Report Name {}  \\nThank You\".format(report_name)\n",
    "            with open(pdf_file_path, 'rb') as f:\n",
    "                pdf_content = f.read()\n",
    "\n",
    " \n",
    "\n",
    "            mime_type, encoding = mimetypes.guess_type(pdf_file_path)\n",
    "\n",
    " \n",
    "\n",
    "            if mime_type is None:\n",
    "                mime_type = 'application/octet-stream'\n",
    "\n",
    " \n",
    "\n",
    "            if pdf_file_path.endswith(\".pdf\"):\n",
    "                attachment = MIMEBase(mime_type, 'pdf', filename=os.path.basename(pdf_file_path))\n",
    "            elif pdf_file_path.endswith(\".xlsx\") or pdf_file_path.endswith('xls'):\n",
    "                attachment = MIMEBase(mime_type, 'xlsx', filename=os.path.basename(pdf_file_path))\n",
    "            else:\n",
    "                attachment = MIMEBase(mime_type, 'csv', filename=os.path.basename(pdf_file_path))\n",
    "\n",
    " \n",
    "\n",
    "            attachment.set_payload(pdf_content)\n",
    "\n",
    " \n",
    "\n",
    "            encoders.encode_base64(attachment)\n",
    "\n",
    " \n",
    "\n",
    "            email = MIMEMultipart()\n",
    "\n",
    " \n",
    "\n",
    "            email['From'] = EmailFromId\n",
    "            email['To'] =','.join(emailto)\n",
    "            email['Subject'] = EmailSubject\n",
    "            email.attach(MIMEText(EmailTypeDescr))\n",
    "            email.attach(attachment)\n",
    "\n",
    " \n",
    "\n",
    "            mailserver = smtplib.SMTP(EmailConfigHost, EmailConfigPort)\n",
    "            mailserver.ehlo()\n",
    "            mailserver.starttls()\n",
    "            mailserver.ehlo()\n",
    "            mailserver.login(EmailFromId, EmailPassword)\n",
    "            mailserver.sendmail(EmailFromId, emailto, email.as_string())\n",
    "            mailserver.close()\n",
    "            print(\"success\")\n",
    "            return jsonify({\"msg\":\"email send successfully\"})\n",
    "        except smtplib.SMTPDataError as e:\n",
    "            return jsonify({\"msg\":str(e)})\n",
    "\n",
    " \n",
    "\n",
    "    if report_notification == 2:\n",
    "        try:\n",
    "            dict3 = {'Report_Id': str(report_id), 'Report_Name': report_name,\"customerName\":customerName[1:-1]}\n",
    "            url = 'https://192.168.21.198:8082/api/rulereport'\n",
    "            response = requests.post(url, json=dict3,verify=False)\n",
    "            print(dict3)\n",
    "        except Exception as e:\n",
    "             return jsonify({\"msg\":str(e)})\n",
    "report_notification_alert(report_id, report_name, report_notification,customerName,save_location)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "69173c87",
   "metadata": {},
   "outputs": [
    {
     "ename": "SMTPAuthenticationError",
     "evalue": "(535, b'5.7.139 Authentication unsuccessful, the user credentials were incorrect. [PN2PR01CA0109.INDPRD01.PROD.OUTLOOK.COM 2023-09-08T10:43:42.420Z 08DBAF47353F7433]')",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mSMTPAuthenticationError\u001b[0m                   Traceback (most recent call last)",
      "Cell \u001b[0;32mIn[43], line 17\u001b[0m\n\u001b[1;32m     15\u001b[0m mailserver\u001b[38;5;241m.\u001b[39mstarttls()\n\u001b[1;32m     16\u001b[0m mailserver\u001b[38;5;241m.\u001b[39mehlo()\n\u001b[0;32m---> 17\u001b[0m \u001b[43mmailserver\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mlogin\u001b[49m\u001b[43m(\u001b[49m\u001b[43mEmailFromId\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mEmailPassword\u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m     19\u001b[0m mailserver\u001b[38;5;241m.\u001b[39msendmail(EmailFromId, emailto, msg\u001b[38;5;241m.\u001b[39mas_string())\n\u001b[1;32m     21\u001b[0m \u001b[38;5;66;03m# Should be mailServer.quit(), but that crashes...\u001b[39;00m\n",
      "File \u001b[0;32m/usr/lib/python3.10/smtplib.py:750\u001b[0m, in \u001b[0;36mSMTP.login\u001b[0;34m(self, user, password, initial_response_ok)\u001b[0m\n\u001b[1;32m    747\u001b[0m         last_exception \u001b[38;5;241m=\u001b[39m e\n\u001b[1;32m    749\u001b[0m \u001b[38;5;66;03m# We could not login successfully.  Return result of last attempt.\u001b[39;00m\n\u001b[0;32m--> 750\u001b[0m \u001b[38;5;28;01mraise\u001b[39;00m last_exception\n",
      "File \u001b[0;32m/usr/lib/python3.10/smtplib.py:739\u001b[0m, in \u001b[0;36mSMTP.login\u001b[0;34m(self, user, password, initial_response_ok)\u001b[0m\n\u001b[1;32m    737\u001b[0m method_name \u001b[38;5;241m=\u001b[39m \u001b[38;5;124m'\u001b[39m\u001b[38;5;124mauth_\u001b[39m\u001b[38;5;124m'\u001b[39m \u001b[38;5;241m+\u001b[39m authmethod\u001b[38;5;241m.\u001b[39mlower()\u001b[38;5;241m.\u001b[39mreplace(\u001b[38;5;124m'\u001b[39m\u001b[38;5;124m-\u001b[39m\u001b[38;5;124m'\u001b[39m, \u001b[38;5;124m'\u001b[39m\u001b[38;5;124m_\u001b[39m\u001b[38;5;124m'\u001b[39m)\n\u001b[1;32m    738\u001b[0m \u001b[38;5;28;01mtry\u001b[39;00m:\n\u001b[0;32m--> 739\u001b[0m     (code, resp) \u001b[38;5;241m=\u001b[39m \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mauth\u001b[49m\u001b[43m(\u001b[49m\n\u001b[1;32m    740\u001b[0m \u001b[43m        \u001b[49m\u001b[43mauthmethod\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[38;5;28;43mgetattr\u001b[39;49m\u001b[43m(\u001b[49m\u001b[38;5;28;43mself\u001b[39;49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mmethod_name\u001b[49m\u001b[43m)\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m    741\u001b[0m \u001b[43m        \u001b[49m\u001b[43minitial_response_ok\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43minitial_response_ok\u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m    742\u001b[0m     \u001b[38;5;66;03m# 235 == 'Authentication successful'\u001b[39;00m\n\u001b[1;32m    743\u001b[0m     \u001b[38;5;66;03m# 503 == 'Error: already authenticated'\u001b[39;00m\n\u001b[1;32m    744\u001b[0m     \u001b[38;5;28;01mif\u001b[39;00m code \u001b[38;5;129;01min\u001b[39;00m (\u001b[38;5;241m235\u001b[39m, \u001b[38;5;241m503\u001b[39m):\n",
      "File \u001b[0;32m/usr/lib/python3.10/smtplib.py:662\u001b[0m, in \u001b[0;36mSMTP.auth\u001b[0;34m(self, mechanism, authobject, initial_response_ok)\u001b[0m\n\u001b[1;32m    660\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m code \u001b[38;5;129;01min\u001b[39;00m (\u001b[38;5;241m235\u001b[39m, \u001b[38;5;241m503\u001b[39m):\n\u001b[1;32m    661\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m (code, resp)\n\u001b[0;32m--> 662\u001b[0m \u001b[38;5;28;01mraise\u001b[39;00m SMTPAuthenticationError(code, resp)\n",
      "\u001b[0;31mSMTPAuthenticationError\u001b[0m: (535, b'5.7.139 Authentication unsuccessful, the user credentials were incorrect. [PN2PR01CA0109.INDPRD01.PROD.OUTLOOK.COM 2023-09-08T10:43:42.420Z 08DBAF47353F7433]')"
     ]
    }
   ],
   "source": [
    "# EmailConfigHost = 'smtp.office365.com'\n",
    "EmailConfigPort = 587\n",
    "EmailFromId = 'nareshsisodiya1996@gmail.com'\n",
    "EmailPassword = 'yszbenixyrdrioei'\n",
    "emailto=['naresh_kumar@sattrixsoftware.com','jyoti_pradhan@sattrixsoftware.com']\n",
    "EmailSubject = 'Report_Alert'\n",
    "EmailTypeDescr = \"Report Generated Successfully For Report Name {} \\nThank You\".format(report_name)\n",
    "msg = MIMEMultipart()\n",
    "msg['From'] = EmailFromId\n",
    "msg['To'] = emailto\n",
    "msg['Subject'] = EmailSubject\n",
    "msg.attach(MIMEText(EmailTypeDescr))\n",
    "mailserver = smtplib.SMTP(EmailConfigHost, EmailConfigPort)\n",
    "mailserver.ehlo()\n",
    "mailserver.starttls()\n",
    "mailserver.ehlo()\n",
    "mailserver.login(EmailFromId, EmailPassword)\n",
    "\n",
    "mailserver.sendmail(EmailFromId, emailto, msg.as_string())\n",
    "\n",
    "# Should be mailServer.quit(), but that crashes...\n",
    "mailserver.close()\n",
    "logger.debug(\"Email sent !!\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "a7863a50",
   "metadata": {},
   "outputs": [
    {
     "ename": "ModuleNotFoundError",
     "evalue": "No module named 'pretty_html_table'",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mModuleNotFoundError\u001b[0m                       Traceback (most recent call last)",
      "Cell \u001b[0;32mIn[50], line 1\u001b[0m\n\u001b[0;32m----> 1\u001b[0m \u001b[38;5;28;01mfrom\u001b[39;00m \u001b[38;5;21;01mpretty_html_table\u001b[39;00m \u001b[38;5;28;01mimport\u001b[39;00m build_table\n",
      "\u001b[0;31mModuleNotFoundError\u001b[0m: No module named 'pretty_html_table'"
     ]
    }
   ],
   "source": [
    "from pretty_html_table import build_table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "65720609",
   "metadata": {},
   "outputs": [
    {
     "ename": "ModuleNotFoundError",
     "evalue": "No module named 'pretty_html_table'",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mModuleNotFoundError\u001b[0m                       Traceback (most recent call last)",
      "Cell \u001b[0;32mIn[51], line 5\u001b[0m\n\u001b[1;32m      3\u001b[0m \u001b[38;5;28;01mfrom\u001b[39;00m \u001b[38;5;21;01memail\u001b[39;00m\u001b[38;5;21;01m.\u001b[39;00m\u001b[38;5;21;01mmime\u001b[39;00m\u001b[38;5;21;01m.\u001b[39;00m\u001b[38;5;21;01mtext\u001b[39;00m \u001b[38;5;28;01mimport\u001b[39;00m MIMEText\n\u001b[1;32m      4\u001b[0m \u001b[38;5;28;01mfrom\u001b[39;00m \u001b[38;5;21;01memail\u001b[39;00m\u001b[38;5;21;01m.\u001b[39;00m\u001b[38;5;21;01mmime\u001b[39;00m\u001b[38;5;21;01m.\u001b[39;00m\u001b[38;5;21;01mmultipart\u001b[39;00m \u001b[38;5;28;01mimport\u001b[39;00m MIMEMultipart\n\u001b[0;32m----> 5\u001b[0m \u001b[38;5;28;01mfrom\u001b[39;00m \u001b[38;5;21;01mpretty_html_table\u001b[39;00m \u001b[38;5;28;01mimport\u001b[39;00m build_table\n\u001b[1;32m      6\u001b[0m \u001b[38;5;28;01mfrom\u001b[39;00m \u001b[38;5;21;01memail\u001b[39;00m\u001b[38;5;21;01m.\u001b[39;00m\u001b[38;5;21;01mmessage\u001b[39;00m \u001b[38;5;28;01mimport\u001b[39;00m EmailMessage\n\u001b[1;32m      9\u001b[0m sender\u001b[38;5;241m=\u001b[39m\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mnareshsisodiya1996@gmail.com\u001b[39m\u001b[38;5;124m\"\u001b[39m\n",
      "\u001b[0;31mModuleNotFoundError\u001b[0m: No module named 'pretty_html_table'"
     ]
    }
   ],
   "source": [
    "import re\n",
    "from smtplib import *\n",
    "from email.mime.text import MIMEText\n",
    "from email.mime.multipart import MIMEMultipart\n",
    "from pretty_html_table import build_table\n",
    "from email.message import EmailMessage\n",
    "\n",
    "\n",
    "sender=\"nareshsisodiya1996@gmail.com\"\n",
    "rec=\"naresh_kumar@sattrixsoftware.com\"\n",
    "password='yszbenixyrdrioei'\n",
    "\n",
    "conn=pymysql.connect(host=\"192.168.21.95\", user=\"test\", password=\"Sattrix@123\", database=\"Trainings\")\n",
    "d=pd.read_sql(\"select * from SMPT\",conn)\n",
    "d=pd.DataFrame(d)\n",
    "d=build_table(d,\"blue_light\")\n",
    "\n",
    "msg=EmailMessage()\n",
    "# msg['subject']='Sattrix Software'\n",
    "# msg['body']=\"Hello Welcome To Sattrix\"\n",
    "subject='Sattrix Software'\n",
    "body=\"Hello Welcome To Sattrix\"\n",
    "\n",
    "message=f\"subject:{subject} \\n\\n {body} \\n\\n{d}\"\n",
    "# d=msg.set_content(d,subtype='html')\n",
    "\n",
    "\n",
    "server=SMTP(\"smtp.gmail.com\",587)\n",
    "server.starttls()\n",
    "server.login(sender,password)\n",
    "server.sendmail(sender, rec , message)\n",
    "print(\"Mail Send Successfully\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "02f583bf",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/tmp/ipykernel_35252/4193370920.py:7: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.\n",
      "  d=pd.read_sql(\"select * from SMPT\",conn)\n"
     ]
    },
    {
     "ename": "NameError",
     "evalue": "name 'build_table' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "Cell \u001b[0;32mIn[49], line 15\u001b[0m\n\u001b[1;32m     13\u001b[0m msg[\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mTo\u001b[39m\u001b[38;5;124m'\u001b[39m]\u001b[38;5;241m=\u001b[39mrec\n\u001b[1;32m     14\u001b[0m msg[\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mSubject\u001b[39m\u001b[38;5;124m'\u001b[39m]\u001b[38;5;241m=\u001b[39msubject\n\u001b[0;32m---> 15\u001b[0m email_body\u001b[38;5;241m=\u001b[39m\u001b[38;5;124m'''\u001b[39m\u001b[38;5;130;01m\\t\u001b[39;00m\u001b[38;5;124m hy this is data table for Logs \u001b[39m\u001b[38;5;132;01m{}\u001b[39;00m\u001b[38;5;124m Thanks And Regard Naresh Kumar\u001b[39m\u001b[38;5;124m'''\u001b[39m\u001b[38;5;241m.\u001b[39mformat(\u001b[43mbuild_table\u001b[49m(d,\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mblue_light\u001b[39m\u001b[38;5;124m'\u001b[39m))\n\u001b[1;32m     16\u001b[0m msg\u001b[38;5;241m.\u001b[39mset_content(email_body, subtype\u001b[38;5;241m=\u001b[39m\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mhtml\u001b[39m\u001b[38;5;124m\"\u001b[39m)\n\u001b[1;32m     18\u001b[0m server\u001b[38;5;241m=\u001b[39mSMTP(\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124msmtp.gmail.com\u001b[39m\u001b[38;5;124m\"\u001b[39m,\u001b[38;5;241m587\u001b[39m)\n",
      "\u001b[0;31mNameError\u001b[0m: name 'build_table' is not defined"
     ]
    }
   ],
   "source": [
    "sender=\"nareshsisodiya1996@gmail.com\"\n",
    "rec=\"naresh_kumar@sattrixsoftware.com\"\n",
    "password='yszbenixyrdrioei'\n",
    "subject=\"Python\"\n",
    "\n",
    "conn=pymysql.connect(host=\"192.168.21.95\", user=\"test\", password=\"Sattrix@123\", database=\"Trainings\")\n",
    "d=pd.read_sql(\"select * from SMPT\",conn)\n",
    "d=pd.DataFrame(d)\n",
    "# d=build_table(d,\"blue_light\")\n",
    "\n",
    "msg=EmailMessage()\n",
    "msg['From']=sender\n",
    "msg['To']=rec\n",
    "\n",
    "msg['Subject']=subject\n",
    "email_body='''\\t hy this is data table for Logs {} Thanks And Regard Naresh Kumar'''.format(build_table(d,'blue_light'))\n",
    "msg.set_content(email_body, subtype=\"html\")\n",
    " \n",
    "server=SMTP(\"smtp.gmail.com\",587)\n",
    "server.starttls()\n",
    "server.login(sender,password)\n",
    "\n",
    "server.send_message(msg)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "f8fa8590",
   "metadata": {},
   "outputs": [],
   "source": [
    "report_id=123\n",
    "report_name=\"chart and table\"\n",
    "report_notification=1\n",
    "customerName=\"SATTRIX\"\n",
    "# save_location=\"C:/Users/Admin/Pictures/IMG_20230415_125432256.jpg\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "94cc4191",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "image/jpeg\n"
     ]
    },
    {
     "ename": "FileNotFoundError",
     "evalue": "[Errno 2] No such file or directory: '/opt/NewEvol/Current/NewEvol_Solr/Datalake/reports/defualt_logo.png'",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mFileNotFoundError\u001b[0m                         Traceback (most recent call last)",
      "Input \u001b[1;32mIn [11]\u001b[0m, in \u001b[0;36m<cell line: 112>\u001b[1;34m()\u001b[0m\n\u001b[0;32m    109\u001b[0m         url \u001b[38;5;241m=\u001b[39m \u001b[38;5;124m'\u001b[39m\u001b[38;5;124mhttps://192.168.21.198:8082/api/rulereport\u001b[39m\u001b[38;5;124m'\u001b[39m\n\u001b[0;32m    110\u001b[0m         response \u001b[38;5;241m=\u001b[39m requests\u001b[38;5;241m.\u001b[39mpost(url, json\u001b[38;5;241m=\u001b[39mdict3,verify\u001b[38;5;241m=\u001b[39m\u001b[38;5;28;01mFalse\u001b[39;00m)\n\u001b[1;32m--> 112\u001b[0m \u001b[43mreport_notification_alert\u001b[49m\u001b[43m(\u001b[49m\u001b[43mreport_id\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mreport_name\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mreport_notification\u001b[49m\u001b[43m,\u001b[49m\u001b[43mcustomerName\u001b[49m\u001b[43m,\u001b[49m\u001b[43msave_location\u001b[49m\u001b[43m)\u001b[49m\n",
      "Input \u001b[1;32mIn [11]\u001b[0m, in \u001b[0;36mreport_notification_alert\u001b[1;34m(report_id, report_name, report_notification, customerName, save_location)\u001b[0m\n\u001b[0;32m     59\u001b[0m html \u001b[38;5;241m=\u001b[39m \u001b[38;5;124m\"\"\"\u001b[39m\u001b[38;5;130;01m\\\u001b[39;00m\n\u001b[0;32m     60\u001b[0m \u001b[38;5;124m        <html>\u001b[39m\n\u001b[0;32m     61\u001b[0m \u001b[38;5;124m        <head><title>NE</title></head>\u001b[39m\n\u001b[1;32m   (...)\u001b[0m\n\u001b[0;32m     90\u001b[0m \u001b[38;5;124m        </html>\u001b[39m\n\u001b[0;32m     91\u001b[0m \u001b[38;5;124m        \u001b[39m\u001b[38;5;124m\"\"\"\u001b[39m\n\u001b[0;32m     92\u001b[0m email\u001b[38;5;241m.\u001b[39mattach(MIMEText(html, \u001b[38;5;124m'\u001b[39m\u001b[38;5;124mhtml\u001b[39m\u001b[38;5;124m'\u001b[39m))\n\u001b[1;32m---> 93\u001b[0m \u001b[38;5;28;01mwith\u001b[39;00m \u001b[38;5;28;43mopen\u001b[39;49m\u001b[43m(\u001b[49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[38;5;124;43m/opt/NewEvol/Current/NewEvol_Solr/Datalake/reports/defualt_logo.png\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[38;5;124;43mrb\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[43m)\u001b[49m \u001b[38;5;28;01mas\u001b[39;00m f1:\n\u001b[0;32m     94\u001b[0m     img_data \u001b[38;5;241m=\u001b[39m f1\u001b[38;5;241m.\u001b[39mread()\n\u001b[0;32m     95\u001b[0m image \u001b[38;5;241m=\u001b[39m MIMEImage(img_data)\n",
      "\u001b[1;31mFileNotFoundError\u001b[0m: [Errno 2] No such file or directory: '/opt/NewEvol/Current/NewEvol_Solr/Datalake/reports/defualt_logo.png'"
     ]
    }
   ],
   "source": [
    "import smtplib  \n",
    "import mimetypes\n",
    "import os\n",
    "import requests\n",
    "from email import encoders\n",
    "from email.message import Message\n",
    "from email.mime.audio import MIMEAudio\n",
    "from email.mime.base import MIMEBase\n",
    "from email.mime.image import MIMEImage\n",
    "from email.mime.multipart import MIMEMultipart\n",
    "from email.mime.text import MIMEText\n",
    "def report_notification_alert(report_id, report_name, report_notification,customerName,save_location):\n",
    "    if report_notification == 1:\n",
    "        try:\n",
    "            EmailConfigHost = 'smtp.office365.com'\n",
    "            EmailConfigPort = 587\n",
    "            EmailFromId = 'support@sattrixsoftware.com'\n",
    "            EmailPassword = 'NE#@$Sattrix!12'\n",
    "            pdf_file_path=save_location\n",
    "            emailto =[\"naresh_kumar@sattrixsoftware.com\"]\n",
    "            EmailSubject = 'Report_Alert'\n",
    "            email = MIMEMultipart()\n",
    "\n",
    "            email['From'] = EmailFromId\n",
    "            email['To'] =','.join(emailto)\n",
    "            email['Subject'] = EmailSubject\n",
    "            Title=\"NE | \"+report_name\n",
    "            with open(pdf_file_path, 'rb') as f:\n",
    "                pdf_content = f.read()\n",
    "\n",
    "            mime_type, encoding = mimetypes.guess_type(pdf_file_path)\n",
    "            print(mime_type)\n",
    "            if mime_type is None:\n",
    "                mime_type = 'application/octet-stream'\n",
    "\n",
    "            if pdf_file_path.endswith(\".pdf\"):\n",
    "\n",
    "                attachment = MIMEBase(mime_type, 'pdf', filename=os.path.basename(pdf_file_path))\n",
    "\n",
    "            elif pdf_file_path.endswith(\".xlsx\") or pdf_file_path.endswith('xls'):\n",
    "\n",
    "                attachment = MIMEBase(mime_type, 'xlsx', filename=os.path.basename(pdf_file_path))\n",
    "\n",
    "            else:\n",
    "\n",
    "                attachment = MIMEBase(mime_type, 'csv', filename=os.path.basename(pdf_file_path))\n",
    "            attachment.set_payload(pdf_content)\n",
    "            encoders.encode_base64(attachment)\n",
    "            email.attach(attachment)\n",
    "            EmailTypeDescr = \"Report Generated Successfully For Report Name {}  \\nThank You\".format(report_name)\n",
    "\n",
    "\n",
    " \n",
    "\n",
    "#             email.attach(MIMEText(Title))\n",
    "\n",
    "#             email.attach(MIMEText(EmailTypeDescr))\n",
    "\n",
    "            html = \"\"\"\\\n",
    "                    <html>\n",
    "                    <head><title>NE</title></head>\n",
    "                    <style>\n",
    "                        .vl {\n",
    "                          border-left: 6px solid green;\n",
    "                          height: 10px;\n",
    "                        }\n",
    "                        .div{\n",
    "                            height:100%;\n",
    "                            width:100%\n",
    "                        }\n",
    "                        .div1{\n",
    "                            float:left;\n",
    "                            width:10%;\n",
    "                            height:100%;\n",
    "                        }\n",
    "                        .div2{\n",
    "                            float:left;\n",
    "                            color:blue;\n",
    "                            width:30%;\n",
    "                            height:80%;\n",
    "                        }\n",
    "                        </style>\n",
    "                      <body>\n",
    "                        <p>Here is an example </p>\n",
    "                        <div class=div>\n",
    "                        <div class=div1><img  width=100px src=\"cid:image1\"></div>\n",
    "                        <div class=div2><a>sattrix@gmail.com</a></div>\n",
    "                        </div>\n",
    "                      </body>\n",
    "                    </html>\n",
    "                    \"\"\"\n",
    "            email.attach(MIMEText(html, 'html'))\n",
    "            with open('/opt/NewEvol/Current/NewEvol_Solr/Datalake/reports/defualt_logo.png', 'rb') as f1:\n",
    "                img_data = f1.read()\n",
    "            image = MIMEImage(img_data)\n",
    "            image.add_header('Content-ID', '<image1>')\n",
    "            email.attach(image)\n",
    "            mailserver = smtplib.SMTP(EmailConfigHost, EmailConfigPort)\n",
    "            mailserver.ehlo()\n",
    "            mailserver.starttls()\n",
    "            mailserver.ehlo()\n",
    "            mailserver.login(EmailFromId, EmailPassword)\n",
    "            mailserver.sendmail(EmailFromId, emailto, email.as_string())\n",
    "            mailserver.close()\n",
    "        except smtplib.SMTPDataError as e:\n",
    "            print(\"error while sending email: {}\".format(e))\n",
    "    if report_notification == 2:\n",
    "        dict3 = {'Report_Id': str(report_id), 'Report_Name': report_name,\"customerName\":customerName[1:-1]}\n",
    "        url = 'https://192.168.21.198:8082/api/rulereport'\n",
    "        response = requests.post(url, json=dict3,verify=False)\n",
    "        \n",
    "report_notification_alert(report_id, report_name, report_notification,customerName,save_location)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "eb0e954b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "application/pdf\n"
     ]
    }
   ],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3de6665c",
   "metadata": {},
   "outputs": [],
   "source": [
    "<style>\n",
    ".vl {\n",
    "  border-left: 6px solid green;\n",
    "  height: 500px;\n",
    "}\n",
    "</style>\n",
    "\n",
    "<div class=\"vl\"></div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "id": "68799187",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "image/jpeg\n"
     ]
    }
   ],
   "source": [
    "import smtplib  \n",
    "import mimetypes\n",
    "import os\n",
    "import requests\n",
    "from email import encoders\n",
    "from email.message import Message\n",
    "from email.mime.audio import MIMEAudio\n",
    "from email.mime.base import MIMEBase\n",
    "from email.mime.image import MIMEImage\n",
    "from email.mime.multipart import MIMEMultipart\n",
    "from email.mime.text import MIMEText\n",
    "def report_notification_alert(report_id, report_name, report_notification,customerName,save_location):\n",
    "    if report_notification == 1:\n",
    "        try:\n",
    "            EmailConfigHost = 'smtp.office365.com'\n",
    "            EmailConfigPort = 587\n",
    "            EmailFromId = 'support@sattrixsoftware.com'\n",
    "            EmailPassword = 'NE#@$Sattrix!12'\n",
    "            pdf_file_path=save_location\n",
    "            emailto =[\"naresh_kumar@sattrixsoftware.com\"]\n",
    "            EmailSubject =\"NE Report | \"+report_name\n",
    "            email = MIMEMultipart()\n",
    "\n",
    "            email['From'] = EmailFromId\n",
    "            email['To'] =','.join(emailto)\n",
    "            email['Subject'] = EmailSubject\n",
    "            Title=\"NE | \"+report_name\n",
    "            with open(save_location, 'rb') as f:\n",
    "                pdf_content = f.read()\n",
    "\n",
    "            mime_type, encoding = mimetypes.guess_type(pdf_file_path)\n",
    "            print(mime_type)\n",
    "            if mime_type is None:\n",
    "                mime_type = 'application/octet-stream'\n",
    "\n",
    "            if pdf_file_path.endswith(\".pdf\"):\n",
    "\n",
    "                attachment = MIMEBase(mime_type, 'pdf', filename=os.path.basename(pdf_file_path))\n",
    "\n",
    "            elif pdf_file_path.endswith(\".xlsx\") or pdf_file_path.endswith('xls'):\n",
    "\n",
    "                attachment = MIMEBase(mime_type, 'xlsx', filename=os.path.basename(pdf_file_path))\n",
    "\n",
    "            else:\n",
    "\n",
    "                attachment = MIMEBase(mime_type, 'csv', filename=os.path.basename(pdf_file_path))\n",
    "            attachment.set_payload(pdf_content)\n",
    "            encoders.encode_base64(attachment)\n",
    "            email.attach(attachment)\n",
    "            EmailTypeDescr = \"Report Generated Successfully For Report Name {}  \\nThank You\".format(report_name)\n",
    "\n",
    "\n",
    " \n",
    "\n",
    "#             email.attach(MIMEText(Title))\n",
    "\n",
    "#             email.attach(MIMEText(EmailTypeDescr))\n",
    "\n",
    "            html = \"\"\"\\\n",
    "                   <html>\n",
    "                      <head><title>NE</title></head>\n",
    "                      <style>\n",
    "                        .vl {\n",
    "                          border-left: 3px solid #DF511E;\n",
    "                          float:left;\n",
    "                          height: 100px;\n",
    "                          margin:0px 10px;\n",
    "                        }\n",
    "                        .div{\n",
    "                          display:block;\n",
    "                          height:100%;\n",
    "                          width:100%\n",
    "                        }\n",
    "                        .div1{\n",
    "                          float:left;\n",
    "                          width:15%;\n",
    "                          height:100%;\n",
    "                        }\n",
    "                        .div2{\n",
    "                          float:left;\n",
    "                          color:blue;\n",
    "                          width:40%;\n",
    "                          height:100%;\n",
    "                          text-align:center;\n",
    "                        }\n",
    "                      </style>\n",
    "                      <body>\n",
    "                        <p>Here is an example </p>\n",
    "                        <div class=div>\n",
    "                          <div class=div1><img  width=100px src=\"\"></div>\n",
    "                          <div class=\"vl\"></div>\n",
    "                          <div class=div2>\n",
    "                            <a href=\"https://outlook.office.com/mail/inbox/id/AAQkADZlNGQxMzAyLWRhMjEtNGRlZS1hMjVhLWMxNjY1N2NjNjQzMwAQAK4bb%2Fjdh0%2FendGgHQAxkrk%3D\">support@sattrixsoftware.com</a><br>\n",
    "                          </div>\n",
    "                          <div class=\"vl\"></div>\n",
    "                          <div class=div1><img  width=100px src=\"\"></div>\n",
    "                        </div>\n",
    "                      </body>\n",
    "                    </html>\n",
    "                    \"\"\"\n",
    "            email.attach(MIMEText(html, 'html'))\n",
    "            with open(save_location, 'rb') as f1:\n",
    "                img_data = f1.read()\n",
    "            image = MIMEImage(img_data)\n",
    "            image.add_header('Content-ID', '<image1>')\n",
    "            email.attach(image)\n",
    "            mailserver = smtplib.SMTP(EmailConfigHost, EmailConfigPort)\n",
    "            mailserver.ehlo()\n",
    "            mailserver.starttls()\n",
    "            mailserver.ehlo()\n",
    "            mailserver.login(EmailFromId, EmailPassword)\n",
    "            mailserver.sendmail(EmailFromId, emailto, email.as_string())\n",
    "            mailserver.close()\n",
    "        except smtplib.SMTPDataError as e:\n",
    "            print(\"error while sending email: {}\".format(e))\n",
    "    if report_notification == 2:\n",
    "        dict3 = {'Report_Id': str(report_id), 'Report_Name': report_name,\"customerName\":customerName[1:-1]}\n",
    "        url = 'https://192.168.21.198:8082/api/rulereport'\n",
    "        response = requests.post(url, json=dict3,verify=False)\n",
    "        \n",
    "report_notification_alert(report_id, report_name, report_notification,customerName,save_location)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "3cab8789",
   "metadata": {},
   "outputs": [
    {
     "ename": "AttributeError",
     "evalue": "'int' object has no attribute 'find'",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mAttributeError\u001b[0m                            Traceback (most recent call last)",
      "Input \u001b[1;32mIn [28]\u001b[0m, in \u001b[0;36m<cell line: 18>\u001b[1;34m()\u001b[0m\n\u001b[0;32m      8\u001b[0m receivers \u001b[38;5;241m=\u001b[39m [\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mnaresh_kumar@sattrixsoftware.com\u001b[39m\u001b[38;5;124m'\u001b[39m]\n\u001b[0;32m     10\u001b[0m message \u001b[38;5;241m=\u001b[39m \u001b[38;5;124m\"\"\"\u001b[39m\u001b[38;5;124mFrom: From Person <from@fromdomain.com>\u001b[39m\n\u001b[0;32m     11\u001b[0m \u001b[38;5;124mTo: To Person <to@todomain.com>\u001b[39m\n\u001b[0;32m     12\u001b[0m \u001b[38;5;124mSubject: SMTP e-mail test\u001b[39m\n\u001b[0;32m     13\u001b[0m \n\u001b[0;32m     14\u001b[0m \u001b[38;5;124mThis is a test e-mail message.\u001b[39m\n\u001b[0;32m     15\u001b[0m \u001b[38;5;124m\"\"\"\u001b[39m\n\u001b[1;32m---> 18\u001b[0m smtpObj \u001b[38;5;241m=\u001b[39m \u001b[43msmtplib\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mSMTP\u001b[49m\u001b[43m(\u001b[49m\u001b[38;5;241;43m587\u001b[39;49m\u001b[43m)\u001b[49m\n\u001b[0;32m     19\u001b[0m smtpObj\u001b[38;5;241m.\u001b[39msendmail(sender, receivers, message)         \n\u001b[0;32m     20\u001b[0m \u001b[38;5;28mprint\u001b[39m(\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mSuccessfully sent email\u001b[39m\u001b[38;5;124m\"\u001b[39m)\n",
      "File \u001b[1;32m~\\anaconda3\\lib\\smtplib.py:255\u001b[0m, in \u001b[0;36mSMTP.__init__\u001b[1;34m(self, host, port, local_hostname, timeout, source_address)\u001b[0m\n\u001b[0;32m    252\u001b[0m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_auth_challenge_count \u001b[38;5;241m=\u001b[39m \u001b[38;5;241m0\u001b[39m\n\u001b[0;32m    254\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m host:\n\u001b[1;32m--> 255\u001b[0m     (code, msg) \u001b[38;5;241m=\u001b[39m \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mconnect\u001b[49m\u001b[43m(\u001b[49m\u001b[43mhost\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mport\u001b[49m\u001b[43m)\u001b[49m\n\u001b[0;32m    256\u001b[0m     \u001b[38;5;28;01mif\u001b[39;00m code \u001b[38;5;241m!=\u001b[39m \u001b[38;5;241m220\u001b[39m:\n\u001b[0;32m    257\u001b[0m         \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mclose()\n",
      "File \u001b[1;32m~\\anaconda3\\lib\\smtplib.py:330\u001b[0m, in \u001b[0;36mSMTP.connect\u001b[1;34m(self, host, port, source_address)\u001b[0m\n\u001b[0;32m    327\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m source_address:\n\u001b[0;32m    328\u001b[0m     \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39msource_address \u001b[38;5;241m=\u001b[39m source_address\n\u001b[1;32m--> 330\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;129;01mnot\u001b[39;00m port \u001b[38;5;129;01mand\u001b[39;00m (\u001b[43mhost\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mfind\u001b[49m(\u001b[38;5;124m'\u001b[39m\u001b[38;5;124m:\u001b[39m\u001b[38;5;124m'\u001b[39m) \u001b[38;5;241m==\u001b[39m host\u001b[38;5;241m.\u001b[39mrfind(\u001b[38;5;124m'\u001b[39m\u001b[38;5;124m:\u001b[39m\u001b[38;5;124m'\u001b[39m)):\n\u001b[0;32m    331\u001b[0m     i \u001b[38;5;241m=\u001b[39m host\u001b[38;5;241m.\u001b[39mrfind(\u001b[38;5;124m'\u001b[39m\u001b[38;5;124m:\u001b[39m\u001b[38;5;124m'\u001b[39m)\n\u001b[0;32m    332\u001b[0m     \u001b[38;5;28;01mif\u001b[39;00m i \u001b[38;5;241m>\u001b[39m\u001b[38;5;241m=\u001b[39m \u001b[38;5;241m0\u001b[39m:\n",
      "\u001b[1;31mAttributeError\u001b[0m: 'int' object has no attribute 'find'"
     ]
    }
   ],
   "source": [
    "import smtplib\n",
    "\n",
    "sender=\"nareshsisodiya1996@gmail.com\"\n",
    "rec=\"naresh_kumar@sattrixsoftware.com\"\n",
    "password='yszbenixyrdrioei'\n",
    "\n",
    "# sender = 'from@fromdomain.com'\n",
    "receivers = ['naresh_kumar@sattrixsoftware.com']\n",
    "\n",
    "message = \"\"\"From: From Person <from@fromdomain.com>\n",
    "To: To Person <to@todomain.com>\n",
    "Subject: SMTP e-mail test\n",
    "\n",
    "This is a test e-mail message.\n",
    "\"\"\"\n",
    "\n",
    "\n",
    "smtpObj = smtplib.SMTP(587)\n",
    "smtpObj.sendmail(sender, receivers, message)         \n",
    "print(\"Successfully sent email\")\n",
    "# except Exception as err:\n",
    "#     print(\"Error: unable to send email\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "c5d3ead1",
   "metadata": {},
   "outputs": [
    {
     "ename": "SyntaxError",
     "evalue": "invalid syntax (39210981.py, line 1)",
     "output_type": "error",
     "traceback": [
      "\u001b[1;36m  Input \u001b[1;32mIn [31]\u001b[1;36m\u001b[0m\n\u001b[1;33m    <html>\u001b[0m\n\u001b[1;37m    ^\u001b[0m\n\u001b[1;31mSyntaxError\u001b[0m\u001b[1;31m:\u001b[0m invalid syntax\n"
     ]
    }
   ],
   "source": [
    "<html>\n",
    "\n",
    "<head>\n",
    "    <title>NE</title>\n",
    "</head>\n",
    "<style>\n",
    "    .vl {\n",
    "        border-left: 3px solid #DF511E;\n",
    "        float: left;\n",
    "        height: 100px;\n",
    "        margin: 0px 10px;\n",
    "    }\n",
    "    .img {\n",
    "        width:30px;\n",
    "    }\n",
    "\n",
    "    .div {\n",
    "        height: 20%;\n",
    "        width: 100%\n",
    "    }\n",
    "\n",
    "    .div1 {\n",
    "        float: left;\n",
    "        width: 30%;\n",
    "        height: 100%;\n",
    "    }\n",
    "\n",
    "    .div2 {\n",
    "        float: left;\n",
    "        color: blue;\n",
    "        width: 35%;\n",
    "        height: 100%;\n",
    "        /* display: block; */\n",
    "        text-align: center;\n",
    "        margin-top: 30px;\n",
    "        margin-bottom: 30px;\n",
    "        \n",
    "    }\n",
    "    .div3{\n",
    "        float: right;\n",
    "        width: 30%;\n",
    "        height: 100%;\n",
    "        margin: 15px 10px;\n",
    "    }\n",
    "</style>\n",
    "\n",
    "<body>\n",
    "    <p>Here is an example </p>\n",
    "    <div class=div>\n",
    "        <div class=div1><img width=70px src=\"C:\\Users\\Admin\\Downloads\\thumbnail_image002.png\"></div>\n",
    "        <div class=\"vl\"></div>\n",
    "        <div class=div2>\n",
    "            <a href=\"https://outlook.office.com/mail/inbox/id/AAQkADZlNGQxMzAyLWRhMjEtNGRlZS1hMjVhLWMxNjY1N2NjNjQzMwAQAK4bb%2Fjdh0%2FendGgHQAxkrk%3D\">support@sattrixsoftware.com</a><br>\n",
    "            <a href=\"https://twitter.com/i/flow/login?redirect_after_login=%2FSattrixSoftware\"> <img class=\"img\" src=\"twitter.png\" alt=\"\"></a>\n",
    "            <a href=\"https://www.linkedin.com/company/sattrix-software-solutions/\"> <img class=\"img\" src=\"linkdin.webp\" alt=\"\"></a>\n",
    "            \n",
    "        </div>\n",
    "        <div class=\"vl\"></div>\n",
    "        <div class=div3><img width=100px src=\"thumbnail_image005.png\"></div>\n",
    "    </div>\n",
    "</body>\n",
    "\n",
    "</html>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "id": "fd3af118",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "image/jpeg\n"
     ]
    }
   ],
   "source": [
    "import smtplib  \n",
    "import mimetypes\n",
    "import os\n",
    "import requests\n",
    "from email import encoders\n",
    "from email.message import Message\n",
    "from email.mime.audio import MIMEAudio\n",
    "from email.mime.base import MIMEBase\n",
    "from email.mime.image import MIMEImage\n",
    "from email.mime.multipart import MIMEMultipart\n",
    "from email.mime.text import MIMEText\n",
    "def report_notification_alert(report_id, report_name, report_notification,customerName,save_location):\n",
    "    if report_notification == 1:\n",
    "        try:\n",
    "            EmailConfigHost = 'smtp.office365.com'\n",
    "            EmailConfigPort = 587\n",
    "            EmailFromId = 'support@sattrixsoftware.com'\n",
    "            EmailPassword = 'NE#@$Sattrix!12'\n",
    "            pdf_file_path=save_location\n",
    "            emailto =[\"naresh_kumar@sattrixsoftware.com\"]\n",
    "            EmailSubject =\"NE Report | \"+report_name\n",
    "            email = MIMEMultipart()\n",
    "\n",
    "            email['From'] = EmailFromId\n",
    "            email['To'] =','.join(emailto)\n",
    "            email['Subject'] = EmailSubject\n",
    "            Title=\"NE | \"+report_name\n",
    "            with open(pdf_file_path, 'rb') as f:\n",
    "                pdf_content = f.read()\n",
    "\n",
    "            mime_type, encoding = mimetypes.guess_type(pdf_file_path)\n",
    "            print(mime_type)\n",
    "            if mime_type is None:\n",
    "                mime_type = 'application/octet-stream'\n",
    "\n",
    "            if pdf_file_path.endswith(\".pdf\"):\n",
    "\n",
    "                attachment = MIMEBase(mime_type, 'pdf', filename=os.path.basename(pdf_file_path))\n",
    "\n",
    "            elif pdf_file_path.endswith(\".xlsx\") or pdf_file_path.endswith('xls'):\n",
    "\n",
    "                attachment = MIMEBase(mime_type, 'xlsx', filename=os.path.basename(pdf_file_path))\n",
    "\n",
    "            else:\n",
    "\n",
    "                attachment = MIMEBase(mime_type, 'csv', filename=os.path.basename(pdf_file_path))\n",
    "            attachment.set_payload(pdf_content)\n",
    "            encoders.encode_base64(attachment)\n",
    "            email.attach(attachment)\n",
    "            EmailTypeDescr = \"Report Generated Successfully For Report Name {}  \\nThank You\".format(report_name)\n",
    "\n",
    "\n",
    " \n",
    "\n",
    "#             email.attach(MIMEText(Title))\n",
    "\n",
    "#             email.attach(MIMEText(EmailTypeDescr))\n",
    "\n",
    "            html = \"\"\"\\\n",
    "                       <html>\n",
    "\n",
    "                            <head>\n",
    "                                <title>NE</title>\n",
    "                            </head>\n",
    "                            <style>\n",
    "                                .vl {\n",
    "                                    border-left: 3px solid #DF511E;\n",
    "                                    float: left;\n",
    "                                    height: 100px;\n",
    "                                    margin: 0px 10px;\n",
    "                                }\n",
    "                                .img {\n",
    "                                    width:30px;\n",
    "                                }\n",
    "\n",
    "                                .div {\n",
    "                                    height: 20%;\n",
    "                                    width: 100%\n",
    "                                }\n",
    "\n",
    "                                .div1 {\n",
    "                                    float: left;\n",
    "                                    width: 30%;\n",
    "                                    height: 100%;\n",
    "                                }\n",
    "\n",
    "                                .div2 {\n",
    "                                    float: left;\n",
    "                                    color: blue;\n",
    "                                    width: 35%;\n",
    "                                    height: 100%;\n",
    "                                    /* display: block; */\n",
    "                                    text-align: center;\n",
    "                                    margin-top: 30px;\n",
    "                                    margin-bottom: 30px;\n",
    "\n",
    "                                }\n",
    "                                .div3{\n",
    "                                    float: right;\n",
    "                                    width: 30%;\n",
    "                                    height: 100%;\n",
    "                                    margin: 15px 10px;\n",
    "                                }\n",
    "                            </style>\n",
    "\n",
    "                            <body>\n",
    "                                <p>Here is an example </p>\n",
    "                                <div class=div>\n",
    "                                    <div class=div1><img width=70px src=\"C:/Users/Admin/Downloads/thumbnail_image002.png\"></div>\n",
    "                                    <div class=\"vl\"></div>\n",
    "                                    <div class=div2>\n",
    "                                        <a href=\"https://outlook.office.com/mail/inbox/id/AAQkADZlNGQxMzAyLWRhMjEtNGRlZS1hMjVhLWMxNjY1N2NjNjQzMwAQAK4bb%2Fjdh0%2FendGgHQAxkrk%3D\">support@sattrixsoftware.com</a><br>\n",
    "                                        <a href=\"https://twitter.com/i/flow/login?redirect_after_login=%2FSattrixSoftware\"> <img class=\"img\" src=\"twitter.png\" alt=\"\"></a>\n",
    "                                        <a href=\"https://www.linkedin.com/company/sattrix-software-solutions/\"> <img class=\"img\" src=\"linkdin.webp\" alt=\"\"></a>\n",
    "\n",
    "                                    </div>\n",
    "                                    <div class=\"vl\"></div>\n",
    "                                    <div class=div3><img width=100px src=\"C:/Users/Admin/Downloads/thumbnail_image005.png\"></div>\n",
    "                                </div>\n",
    "                            </body>\n",
    "                            </html>\n",
    "                            \"\"\"\n",
    "            email.attach(MIMEText(html, 'html'))\n",
    "#             with open( \"C:/Users/Admin/Downloads/thumbnail_image005.png\", 'rb') as f1:\n",
    "#                 img_data = f1.read()\n",
    "#             image = MIMEImage(img_data)\n",
    "#             image.add_header('Content-ID', '<image1>')\n",
    "#             email.attach()\n",
    "            mailserver = smtplib.SMTP(EmailConfigHost, EmailConfigPort)\n",
    "            mailserver.ehlo()\n",
    "            mailserver.starttls()\n",
    "            mailserver.ehlo()\n",
    "            mailserver.login(EmailFromId, EmailPassword)\n",
    "            mailserver.sendmail(EmailFromId, emailto, email.as_string())\n",
    "            mailserver.close()\n",
    "        except smtplib.SMTPDataError as e:\n",
    "            print(\"error while sending email: {}\".format(e))\n",
    "    if report_notification == 2:\n",
    "        dict3 = {'Report_Id': str(report_id), 'Report_Name': report_name,\"customerName\":customerName[1:-1]}\n",
    "        url = 'https://192.168.21.198:8082/api/rulereport'\n",
    "        response = requests.post(url, json=dict3,verify=False)\n",
    "        \n",
    "report_notification_alert(report_id, report_name, report_notification,customerName,save_location)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "320e9bee",
   "metadata": {},
   "outputs": [],
   "source": [
    "import smtplib\n",
    "from email.mime.multipart import MIMEMultipart\n",
    "from email.mime.text import MIMEText\n",
    "from email.mime.image import MIMEImage\n",
    "from email.mime.application import MIMEApplication\n",
    "EmailConfigHost='smtp.office365.com'\n",
    "\n",
    "EmailConfigPort = '587'  # Use the appropriate port for your SMTP server\n",
    "smtp_server = smtplib.SMTP(EmailConfigHost, EmailConfigPort)\n",
    "smtp_username ='support@sattrixsoftware.com'\n",
    "smtp_password ='NE#@$Sattrix!12'\n",
    "\n",
    "\n",
    "sender_email ='support@sattrixsoftware.com'\n",
    "recipient_email = 'naresh_kumar@sattrixsoftware.com'\n",
    "msg = MIMEMultipart()\n",
    "msg['From'] = sender_email\n",
    "msg['To'] = recipient_email\n",
    "msg['Subject'] = report_name\n",
    "email_body = \"\"\"\n",
    "Hello,\n",
    "\n",
    "This is the body of your email.\n",
    "\n",
    "Company Email: 'support@sattrixsoftware.com'\n",
    "\n",
    "Below is our company logo:\n",
    "\"\"\"\n",
    "\n",
    "msg.attach(MIMEText(email_body, 'plain'))\n",
    "\n",
    "with open('C:/Users/Admin/Downloads/thumbnail_image002.png', 'rb') as logo_file:\n",
    "    logo = MIMEImage(logo_file.read())\n",
    "    logo.add_header('Content-Disposition', 'attachment', filename='company_logo.png')\n",
    "    msg.attach(logo)\n",
    "with open('C:/Users/Admin/Downloads/thumbnail_image002.png', 'rb') as logo_file:\n",
    "    logo = MIMEImage(logo_file.read())\n",
    "    logo.add_header('Content-Disposition', 'attachment', filename='company_logo.png')\n",
    "    msg.attach(logo)\n",
    "# try:\n",
    "    server = smtplib.SMTP(smtp_server, EmailConfigPort)\n",
    "    server.starttls()\n",
    "    server.login(smtp_username, smtp_password)\n",
    "    server.sendmail(sender_email, recipient_email, msg.as_string())\n",
    "    server.quit()\n",
    "    print(\"Email sent successfully!\")\n",
    "# except Exception as e:\n",
    "    print(f\"Error: {str(e)}\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0d2a601a",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "a7221de2",
   "metadata": {},
   "outputs": [],
   "source": [
    "report_id=123\n",
    "report_name=\"chart and table\"\n",
    "report_notification=1\n",
    "customerName=\"SATTRIX\"\n",
    "save_location=\"C:/Users/Admin/Downloads/Python Lectureflow.pdf\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "8216585e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "application/pdf\n"
     ]
    }
   ],
   "source": [
    "import smtplib  \n",
    "import mimetypes\n",
    "import os\n",
    "import requests\n",
    "from email import encoders\n",
    "from email.message import Message\n",
    "from email.mime.audio import MIMEAudio\n",
    "from email.mime.base import MIMEBase\n",
    "from email.mime.image import MIMEImage\n",
    "from email.mime.multipart import MIMEMultipart\n",
    "from email.mime.text import MIMEText\n",
    "def report_notification_alert(report_id, report_name, report_notification,customerName,save_location):\n",
    "    if report_notification == 1:\n",
    "        try:\n",
    "            EmailConfigHost = 'smtp.office365.com'\n",
    "            EmailConfigPort = 587\n",
    "            EmailFromId = 'support@sattrixsoftware.com'\n",
    "            EmailPassword = 'NE#@$Sattrix!12'\n",
    "            pdf_file_path=save_location\n",
    "            emailto =[\"naresh_kumar@sattrixsoftware.com\"]\n",
    "            EmailSubject = 'Ne Report |'+report_name\n",
    "            email = MIMEMultipart()\n",
    "            email['From'] = EmailFromId\n",
    "            email['To'] =','.join(emailto)\n",
    "            email['Subject'] = EmailSubject\n",
    "            Title=\"NE | \"+report_name\n",
    "            with open(pdf_file_path, 'rb') as f:\n",
    "                pdf_content = f.read()\n",
    "            mime_type, encoding = mimetypes.guess_type(pdf_file_path)\n",
    "            print(mime_type)\n",
    "            if mime_type is None:\n",
    "                mime_type = 'application/octet-stream'\n",
    "            if pdf_file_path.endswith(\".pdf\"):\n",
    "                attachment = MIMEBase(mime_type, 'pdf', filename=os.path.basename(pdf_file_path))\n",
    "            elif pdf_file_path.endswith(\".xlsx\") or pdf_file_path.endswith('xls'):\n",
    "                attachment = MIMEBase(mime_type, 'xlsx', filename=os.path.basename(pdf_file_path))\n",
    "            else:\n",
    "                attachment = MIMEBase(mime_type, 'csv', filename=os.path.basename(pdf_file_path))\n",
    "            attachment.set_payload(pdf_content)\n",
    "            encoders.encode_base64(attachment)\n",
    "            email.attach(attachment)\n",
    "            EmailTypeDescr = \"Report Generated Successfully For Report Name {}  \\nThank You\".format(report_name)\n",
    "#             email.attach(MIMEText(Title))\n",
    "#             email.attach(MIMEText(EmailTypeDescr))\n",
    "            html = \"\"\"\\\n",
    "                     <html>\n",
    "\n",
    "                            <head>\n",
    "                                <title>NE</title>\n",
    "                            </head>\n",
    "                            <style>\n",
    "                                .vl {\n",
    "                                    border-left: 3px solid #DF511E;\n",
    "                                    float: left;\n",
    "                                    height: 100px;\n",
    "                                    margin: 0px 5px;\n",
    "                                }\n",
    "                                .img {\n",
    "                                    width:10%;\n",
    "                                    margin:5px;\n",
    "                                }\n",
    "\n",
    "                                .div {\n",
    "                                    display:block;\n",
    "                                    height:100%;\n",
    "                                    width:100%;\n",
    "                                }\n",
    "\n",
    "                                .div1 {\n",
    "                                    float: left;\n",
    "                                    width: 20%;\n",
    "                                    height: 80%;\n",
    "                                }\n",
    "\n",
    "                                .div2 {\n",
    "                                    float: left;\n",
    "                                    color: blue;\n",
    "                                    width: 40%;\n",
    "                                    height: 100%;\n",
    "                                    text-align: center;\n",
    "                                    margin-top: 30px;\n",
    "                                    margin-bottom: 30px;\n",
    "\n",
    "                                }\n",
    "                                .div3{\n",
    "                                    float: left;\n",
    "                                    width: 25%;\n",
    "                                    height: 100%;\n",
    "                                    margin: 15px 10px;\n",
    "                                }\n",
    "                                .img2{\n",
    "                                    height:60%;\n",
    "                                    width:auto;\n",
    "                                }\n",
    "                            </style>\n",
    "\n",
    "                            <body>\n",
    "                                <p>Here is an example </p>\n",
    "                                <div class=div>\n",
    "                                    <div class=div1><img class=img2 src=cid:image1></div>\n",
    "                                    <div class=\"vl\"></div>\n",
    "                                    <div class=div2>\n",
    "                                        <a href=\"https://outlook.office.com/mail/inbox/id/AAQkADZlNGQxMzAyLWRhMjEtNGRlZS1hMjVhLWMxNjY1N2NjNjQzMwAQAK4bb%2Fjdh0%2FendGgHQAxkrk%3D\">support@sattrixsoftware.com</a><br>\n",
    "                                        <a href=\"https://twitter.com/i/flow/login?redirect_after_login=%2FSattrixSoftware\"> <img class=\"img\" src=cid:image3 alt=\"image not found\"></a>\n",
    "                                        <a href=\"https://www.linkedin.com/company/sattrix-software-solutions/\"> <img class=\"img\" src=cid:image4 alt=\"image not found\"></a>\n",
    "\n",
    "                                    </div>\n",
    "                                    <div class=\"vl\"></div>\n",
    "                                    <div class=div3><img width=80% src=cid:image2></div>\n",
    "                                </div><br>\n",
    "                            </body>\n",
    "                            </html>\n",
    "                    \"\"\"\n",
    "            email.attach(MIMEText(html, 'html'))\n",
    "            with open(\"C:/Users/Admin/Downloads/thumbnail_image002.jpg\", 'rb') as f1:\n",
    "                img_data = f1.read()\n",
    "            image = MIMEImage(img_data)\n",
    "            email.attach(image)\n",
    "            image.add_header('Content-ID', '<image1>')\n",
    "            with open(\"C:/Users/Admin/Downloads/thumbnail_image005.png\", 'rb') as f1:\n",
    "                img_data2 = f1.read()\n",
    "            image = MIMEImage(img_data2)\n",
    "            image.add_header('Content-ID', '<image2>')\n",
    "            email.attach(image)\n",
    "            with open(\"C:/Users/Admin/Downloads/twitter.png\", 'rb') as f1:\n",
    "                img_data3 = f1.read()\n",
    "            image = MIMEImage(img_data3)\n",
    "            image.add_header('Content-ID', '<image3>')\n",
    "            email.attach(image)\n",
    "            with open(\"C:/Users/Admin/Downloads/linkdin.webp\", 'rb') as f1: \n",
    "                img_data4= f1.read()\n",
    "            image = MIMEImage(img_data4)\n",
    "            image.add_header('Content-ID', '<image4>')\n",
    "            email.attach(image)\n",
    "            mailserver = smtplib.SMTP(EmailConfigHost, EmailConfigPort)\n",
    "            mailserver.ehlo()\n",
    "            mailserver.starttls()\n",
    "            mailserver.ehlo()\n",
    "            mailserver.login(EmailFromId, EmailPassword)\n",
    "            mailserver.sendmail(EmailFromId, emailto, email.as_string())\n",
    "            mailserver.close()\n",
    "        except smtplib.SMTPDataError as e:\n",
    "            print(\"error while sending email: {}\".format(e))\n",
    "    if report_notification == 2:\n",
    "        dict3 = {'Report_Id': str(report_id), 'Report_Name': report_name,\"customerName\":customerName[1:-1]}\n",
    "        url = 'https://192.168.21.198:8082/api/rulereport'\n",
    "        response = requests.post(url, json=dict3,verify=False)\n",
    "report_notification_alert(report_id, report_name, report_notification,customerName,save_location)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "1ea28ee1",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "application/pdf\n"
     ]
    }
   ],
   "source": [
    "import smtplib  \n",
    "import mimetypes\n",
    "import os\n",
    "import requests\n",
    "from email import encoders\n",
    "from email.message import Message\n",
    "from email.mime.audio import MIMEAudio\n",
    "from email.mime.base import MIMEBase\n",
    "from email.mime.image import MIMEImage\n",
    "from email.mime.multipart import MIMEMultipart\n",
    "from email.mime.text import MIMEText\n",
    "def report_notification_alert(report_id, report_name, report_notification,customerName,save_location):\n",
    "    if report_notification == 1:\n",
    "        try:\n",
    "            EmailConfigHost = 'smtp.office365.com'\n",
    "            EmailConfigPort = 587\n",
    "            EmailFromId = 'support@sattrixsoftware.com'\n",
    "            EmailPassword = 'NE#@$Sattrix!12'\n",
    "            pdf_file_path=save_location\n",
    "            emailto =[\"naresh_kumar@sattrixsoftware.com\"]\n",
    "            EmailSubject = 'Ne Report |'+report_name\n",
    "            email = MIMEMultipart()\n",
    "            email['From'] = EmailFromId\n",
    "            email['To'] =','.join(emailto)\n",
    "            email['Subject'] = EmailSubject\n",
    "            Title=\"NE | \"+report_name\n",
    "            with open(pdf_file_path, 'rb') as f:\n",
    "                pdf_content = f.read()\n",
    "            mime_type, encoding = mimetypes.guess_type(pdf_file_path)\n",
    "            print(mime_type)\n",
    "            if mime_type is None:\n",
    "                mime_type = 'application/octet-stream'\n",
    "            if pdf_file_path.endswith(\".pdf\"):\n",
    "                attachment = MIMEBase(mime_type, 'pdf', filename=os.path.basename(pdf_file_path))\n",
    "            elif pdf_file_path.endswith(\".xlsx\") or pdf_file_path.endswith('xls'):\n",
    "                attachment = MIMEBase(mime_type, 'xlsx', filename=os.path.basename(pdf_file_path))\n",
    "            else:\n",
    "                attachment = MIMEBase(mime_type, 'csv', filename=os.path.basename(pdf_file_path))\n",
    "            attachment.set_payload(pdf_content)\n",
    "            encoders.encode_base64(attachment)\n",
    "            email.attach(attachment)\n",
    "            EmailTypeDescr = \"Report Generated Successfully For Report Name {}  \\nThank You\".format(report_name)\n",
    "#             email.attach(MIMEText(Title))\n",
    "#             email.attach(MIMEText(EmailTypeDescr))\n",
    "            html = \"\"\"\\\n",
    "                     <html>\n",
    "\n",
    "                            <head>\n",
    "                                <title>NE</title>\n",
    "                            </head>\n",
    "                            <style>\n",
    "                                .vl {\n",
    "                                    border-left: 3px solid #DF511E;\n",
    "                                    float: left;\n",
    "                                    height: 100px;\n",
    "                                    margin: 0px 5px;\n",
    "                                }\n",
    "                                .img {\n",
    "                                    width:10%;\n",
    "                                    margin:5px;\n",
    "                                }\n",
    "\n",
    "                                .div {\n",
    "                                    display:block;\n",
    "                                    height:100%;\n",
    "                                    width:100%;\n",
    "                                }\n",
    "\n",
    "                                .div1 {\n",
    "                                    float: left;\n",
    "                                    width: 20%;\n",
    "                                    height: 80%;\n",
    "                                }\n",
    "\n",
    "                                .div2 {\n",
    "                                    float: left;\n",
    "                                    color: blue;\n",
    "                                    width: 40%;\n",
    "                                    height: 100%;\n",
    "                                    text-align: center;\n",
    "                                    margin-top: 30px;\n",
    "                                    margin-bottom: 30px;\n",
    "\n",
    "                                }\n",
    "                                .div3{\n",
    "                                    float: left;\n",
    "                                    width: 25%;\n",
    "                                    height: 100%;\n",
    "                                    margin: 15px 10px;\n",
    "                                }\n",
    "                                .img2{\n",
    "                                    height:60%;\n",
    "                                    width:auto;\n",
    "                                }\n",
    "                            </style>\n",
    "\n",
    "                            <body>\n",
    "                                <p>Here is an example </p>\n",
    "                                <div class=div>\n",
    "                                    <div class=div1><img class=img2 src=\"C:/Users/Admin/Downloads/thumbnail_image002.jpg\"></div>\n",
    "                                    <div class=\"vl\"></div>\n",
    "                                    <div class=div2>\n",
    "                                        <a href=\"https://outlook.office.com/mail/inbox/id/AAQkADZlNGQxMzAyLWRhMjEtNGRlZS1hMjVhLWMxNjY1N2NjNjQzMwAQAK4bb%2Fjdh0%2FendGgHQAxkrk%3D\">support@sattrixsoftware.com</a><br>\n",
    "                                        <a href=\"https://twitter.com/i/flow/login?redirect_after_login=%2FSattrixSoftware\"> <img class=\"img\" src=\"C:/Users/Admin/Downloads/twitter.png\" alt=\"image not found\"></a>\n",
    "                                        <a href=\"https://www.linkedin.com/company/sattrix-software-solutions/\"> <img class=\"img\" src=\"C:/Users/Admin/Downloads/linkdin.webp\" alt=\"image not found\"></a>\n",
    "\n",
    "                                    </div>\n",
    "                                    <div class=\"vl\"></div>\n",
    "                                    <div class=div3><img width=80% src=\"C:/Users/Admin/Downloads/thumbnail_image005.png\"></div>\n",
    "                                </div><br>\n",
    "                            </body>\n",
    "                            </html>\n",
    "                    \"\"\"\n",
    "            binary_data = html.encode('utf-8')\n",
    "            binary_string = ''.join(format(byte, '08b') for byte in binary_data)\n",
    "            email.attach(MIMEText(html, 'binary_string'))\n",
    "#             with open(\"C:/Users/Admin/Downloads/thumbnail_image002.jpg\", 'rb') as f1:\n",
    "#                 img_data = f1.read()\n",
    "#             image = MIMEImage(img_data)\n",
    "#             email.attach(image)\n",
    "#             image.add_header('Content-ID', '<image1>')\n",
    "#             with open(\"C:/Users/Admin/Downloads/thumbnail_image005.png\", 'rb') as f1:\n",
    "#                 img_data2 = f1.read()\n",
    "#             image = MIMEImage(img_data2)\n",
    "#             image.add_header('Content-ID', '<image2>')\n",
    "#             email.attach(image)\n",
    "#             with open(\"C:/Users/Admin/Downloads/twitter.png\", 'rb') as f1:\n",
    "#                 img_data3 = f1.read()\n",
    "#             image = MIMEImage(img_data3)\n",
    "#             image.add_header('Content-ID', '<image3>')\n",
    "#             email.attach(image)\n",
    "#             with open(\"C:/Users/Admin/Downloads/linkdin.webp\", 'rb') as f1: \n",
    "#                 img_data4= f1.read()\n",
    "#             image = MIMEImage(img_data4)\n",
    "#             image.add_header('Content-ID', '<image4>')\n",
    "#             email.attach(image)\n",
    "            mailserver = smtplib.SMTP(EmailConfigHost, EmailConfigPort)\n",
    "            mailserver.ehlo()\n",
    "            mailserver.starttls()\n",
    "            mailserver.ehlo()\n",
    "            mailserver.login(EmailFromId, EmailPassword)\n",
    "            mailserver.sendmail(EmailFromId, emailto, email.as_string())\n",
    "            mailserver.close()\n",
    "        except smtplib.SMTPDataError as e:\n",
    "            print(\"error while sending email: {}\".format(e))\n",
    "    if report_notification == 2:\n",
    "        dict3 = {'Report_Id': str(report_id), 'Report_Name': report_name,\"customerName\":customerName[1:-1]}\n",
    "        url = 'https://192.168.21.198:8082/api/rulereport'\n",
    "        response = requests.post(url, json=dict3,verify=False)\n",
    "report_notification_alert(report_id, report_name, report_notification,customerName,save_location)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "f4821023",
   "metadata": {},
   "outputs": [],
   "source": [
    "import smtplib\n",
    "import os\n",
    "import requests\n",
    "from email.mime.multipart import MIMEMultipart\n",
    "from email.mime.text import MIMEText\n",
    "from email.mime.base import MIMEBase\n",
    "from email import encoders\n",
    "\n",
    "def report_notification_alert(report_id, report_name, report_notification, customerName, save_location):\n",
    "    if report_notification == 1:\n",
    "        try:\n",
    "            EmailConfigHost = 'smtp.office365.com'\n",
    "            EmailConfigPort = 587\n",
    "            EmailFromId = 'support@sattrixsoftware.com'\n",
    "            EmailPassword = 'NE#@$Sattrix!12'\n",
    "            pdf_file_path = save_location\n",
    "            emailto = [\"naresh_kumar@sattrixsoftware.com\"]\n",
    "            EmailSubject = 'NE Report | ' + report_name\n",
    "            email = MIMEMultipart()\n",
    "            email['From'] = EmailFromId\n",
    "            email['To'] = ','.join(emailto)\n",
    "            email['Subject'] = EmailSubject\n",
    "\n",
    "            # Attach the PDF file\n",
    "            with open(pdf_file_path, 'rb') as f:\n",
    "                attachment = MIMEBase('application', 'octet-stream')\n",
    "                attachment.set_payload(f.read())\n",
    "                encoders.encode_base64(attachment)\n",
    "                attachment.add_header('Content-Disposition', f'attachment; filename={os.path.basename(pdf_file_path)}')\n",
    "                email.attach(attachment)\n",
    "\n",
    "            # HTML content\n",
    "            html = \"\"\"\n",
    "                <html>\n",
    "                <body>\n",
    "                    <p>Here is an example:</p>\n",
    "                    <img src=\"cid:image1\" width=\"100\">\n",
    "                    <a href=\"https://outlook.office.com/mail/inbox/id/AAQkADZlNGQxMzAyLWRhMjEtNGRlZS1hMjVhLWMxNjY1N2NjNjQzMwAQAK4bb%2Fjdh0%2FendGgHQAxkrk%3D\">support@sattrixsoftware.com</a><br>\n",
    "                    <a href=\"https://twitter.com/i/flow/login?redirect_after_login=%2FSattrixSoftware\"><img src=\"cid:image2\" width=\"100\" alt=\"Twitter\"></a>\n",
    "                    <a href=\"https://www.linkedin.com/company/sattrix-software-solutions/\"><img src=\"cid:image3\" width=\"100\" alt=\"LinkedIn\"></a>\n",
    "                </body>\n",
    "                </html>\n",
    "                \"\"\"\n",
    "\n",
    "            # Attach embedded images\n",
    "            for image_path, cid in [(\"C:/Users/Admin/Downloads/thumbnail_image002.jpg\", \"image1\"),\n",
    "                                    (\"C:/Users/Admin/Downloads/twitter.png\", \"image2\"),\n",
    "                                    (\"C:/Users/Admin/Downloads/linkdin.webp\", \"image3\")]:\n",
    "                with open(image_path, 'rb') as img_file:\n",
    "                    image = MIMEBase('application', 'octet-stream')\n",
    "                    image.set_payload(img_file.read())\n",
    "                    encoders.encode_base64(image)\n",
    "                    image.add_header('Content-ID', f'<{cid}>')\n",
    "                    email.attach(image)\n",
    "\n",
    "            email.attach(MIMEText(html, 'html'))\n",
    "\n",
    "            mailserver = smtplib.SMTP(EmailConfigHost, EmailConfigPort)\n",
    "            mailserver.ehlo()\n",
    "            mailserver.starttls()\n",
    "            mailserver.ehlo()\n",
    "            mailserver.login(EmailFromId, EmailPassword)\n",
    "            mailserver.sendmail(EmailFromId, emailto, email.as_string())\n",
    "            mailserver.close()\n",
    "        except Exception as e:\n",
    "            print(f\"Error while sending email: {e}\")\n",
    "\n",
    "    if report_notification == 2:\n",
    "        dict3 = {'Report_Id': str(report_id), 'Report_Name': report_name, \"customerName\": customerName[1:-1]}\n",
    "        url = 'https://192.168.21.198:8082/api/rulereport'\n",
    "        response = requests.post(url, json=dict3, verify=False)\n",
    "\n",
    "\n",
    "\n",
    "report_notification_alert(report_id, report_name, report_notification, customerName, save_location)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f07431d3",
   "metadata": {},
   "outputs": [],
   "source": [
    "report_id=123\n",
    "report_name=\"chart and table\"\n",
    "report_notification=1\n",
    "customerName=\"SATTRIX\"\n",
    "save_location=\"C:/Users/Admin/Downloads/Python Lectureflow.pdf\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "a4fcb044",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Email sent successfully!\n"
     ]
    }
   ],
   "source": [
    "import smtplib\n",
    "import os\n",
    "from email.mime.multipart import MIMEMultipart\n",
    "from email.mime.text import MIMEText\n",
    "from email.mime.image import MIMEImage\n",
    "from email.mime.application import MIMEApplication\n",
    "from email import encoders\n",
    "\n",
    "def send_email_with_attachment_and_images():\n",
    "    try:\n",
    "        # SMTP Configuration\n",
    "        smtp_server = 'smtp.office365.com'\n",
    "        smtp_port = 587\n",
    "        smtp_username ='support@sattrixsoftware.com'\n",
    "        smtp_password =  'NE#@$Sattrix!12'\n",
    "#         EmailFromId = 'support@sattrixsoftware.com'\n",
    "#             EmailPassword = 'NE#@$Sattrix!12'\n",
    "        # Email Content\n",
    "        sender_email = 'support@sattrixsoftware.com'\n",
    "        recipient_email = 'naresh_kumar@sattrixsoftware.com'\n",
    "        email_subject = 'Email Subject'\n",
    "\n",
    "        # Create an email message\n",
    "        email = MIMEMultipart()\n",
    "        email['From'] = sender_email\n",
    "        email['To'] = recipient_email\n",
    "        email['Subject'] = email_subject\n",
    "\n",
    "        # Attach PDF\n",
    "        pdf_file_path =\"C:/Users/Admin/Downloads/Python Lectureflow.pdf\"\n",
    "        with open(pdf_file_path, 'rb') as pdf_file:\n",
    "            attachment = MIMEApplication(pdf_file.read())\n",
    "            attachment.add_header('Content-Disposition', 'attachment', filename=os.path.basename(pdf_file_path))\n",
    "            email.attach(attachment)\n",
    "\n",
    "        # HTML Content with Logo and Image\n",
    "        html = \"\"\"\n",
    "            <html>\n",
    "            <body>\n",
    "                <p>Here is your email body.</p>\n",
    "                <img src=\"cid:logo_image\" width=\"200\">\n",
    "                <img src=\"cid:other_image\" width=\"200\">\n",
    "            </body>\n",
    "            </html>\n",
    "        \"\"\"\n",
    "\n",
    "        email.attach(MIMEText(html, 'html'))\n",
    "\n",
    "        # Attach Logo Image\n",
    "        logo_image_path = \"C:/Users/Admin/Downloads/thumbnail_image002.jpg\"\n",
    "        with open(logo_image_path, 'rb') as logo_image_file:\n",
    "            logo_image = MIMEImage(logo_image_file.read())\n",
    "            logo_image.add_header('Content-ID', '<logo_image>')\n",
    "            email.attach(logo_image)\n",
    "\n",
    "        # Attach Another Image\n",
    "        other_image_path = \"C:/Users/Admin/Downloads/twitter.png\"\n",
    "        with open(other_image_path, 'rb') as other_image_file:\n",
    "            other_image = MIMEImage(other_image_file.read())\n",
    "            other_image.add_header('Content-ID', '<other_image>')\n",
    "            email.attach(other_image)\n",
    "\n",
    "        # Connect to SMTP server and send email\n",
    "        server = smtplib.SMTP(smtp_server, smtp_port)\n",
    "        server.starttls()\n",
    "        server.login(smtp_username, smtp_password)\n",
    "        server.sendmail(sender_email, recipient_email, email.as_string())\n",
    "        server.quit()\n",
    "\n",
    "        print(\"Email sent successfully!\")\n",
    "\n",
    "    except Exception as e:\n",
    "        print(f\"Error: {str(e)}\")\n",
    "\n",
    "# Call the function to send the email\n",
    "send_email_with_attachment_and_images()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "25ebd5ad",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "application/pdf\n"
     ]
    }
   ],
   "source": [
    "import smtplib  \n",
    "import mimetypes\n",
    "import os\n",
    "import requests\n",
    "from email import encoders\n",
    "from email.message import Message\n",
    "from email.mime.audio import MIMEAudio\n",
    "from email.mime.base import MIMEBase\n",
    "from email.mime.image import MIMEImage\n",
    "from email.mime.multipart import MIMEMultipart\n",
    "from email.mime.text import MIMEText\n",
    "def report_notification_alert(report_id, report_name, report_notification,customerName,save_location):\n",
    "    if report_notification == 1:\n",
    "        try:\n",
    "            EmailConfigHost = 'smtp.office365.com'\n",
    "            EmailConfigPort = 587\n",
    "            EmailFromId = 'support@sattrixsoftware.com'\n",
    "            EmailPassword = 'NE#@$Sattrix!12'\n",
    "            pdf_file_path=save_location\n",
    "            emailto =[\"naresh_kumar@sattrixsoftware.com\",\"atul_kumar@sattrixsoftware.com\"]\n",
    "            EmailSubject = 'Ne Report |'+report_name\n",
    "            email = MIMEMultipart()\n",
    "            email['From'] = EmailFromId\n",
    "            email['To'] =','.join(emailto)\n",
    "            email['Subject'] = EmailSubject\n",
    "            Title=\"NE | \"+report_name\n",
    "            with open(pdf_file_path, 'rb') as f:\n",
    "                pdf_content = f.read()\n",
    "            mime_type, encoding = mimetypes.guess_type(pdf_file_path)\n",
    "            print(mime_type)\n",
    "            if mime_type is None:\n",
    "                mime_type = 'application/octet-stream'\n",
    "            if pdf_file_path.endswith(\".pdf\"):\n",
    "                attachment = MIMEBase(mime_type, 'pdf', filename=os.path.basename(pdf_file_path))\n",
    "            elif pdf_file_path.endswith(\".xlsx\") or pdf_file_path.endswith('xls'):\n",
    "                attachment = MIMEBase(mime_type, 'xlsx', filename=os.path.basename(pdf_file_path))\n",
    "            else:\n",
    "                attachment = MIMEBase(mime_type, 'csv', filename=os.path.basename(pdf_file_path))\n",
    "            attachment.set_payload(pdf_content)\n",
    "            encoders.encode_base64(attachment)\n",
    "            email.attach(attachment)\n",
    "            EmailTypeDescr = \"Report Generated Successfully For Report Name {}  \\nThank You\".format(report_name)\n",
    "#             email.attach(MIMEText(Title))\n",
    "#             email.attach(MIMEText(EmailTypeDescr))\n",
    "            html = \"\"\"\\\n",
    "                     <html>\n",
    "\n",
    "                            <head>\n",
    "                                <title>NE</title>\n",
    "                            </head>\n",
    "                            <style>\n",
    "                                .vl {\n",
    "                                    border-left: 3px solid #DF511E;\n",
    "                                    float: left;\n",
    "                                    height: 100px;\n",
    "                                    margin: 0px 5px;\n",
    "                                }\n",
    "                                .img {\n",
    "                                    width:10%;\n",
    "                                    margin:5px;\n",
    "                                }\n",
    "\n",
    "                                .div {\n",
    "                                    display:block;\n",
    "                                    height:100%;\n",
    "                                    width:100%;\n",
    "                                }\n",
    "\n",
    "                                .div1 {\n",
    "                                    float: left;\n",
    "                                    width: 20%;\n",
    "                                    height: 80%;\n",
    "                                }\n",
    "\n",
    "                                .div2 {\n",
    "                                    float: left;\n",
    "                                    color: blue;\n",
    "                                    width: 40%;\n",
    "                                    height: 100%;\n",
    "                                    text-align: center;\n",
    "                                    margin-top: 30px;\n",
    "                                    margin-bottom: 30px;\n",
    "\n",
    "                                }\n",
    "                                .div3{\n",
    "                                    float: left;\n",
    "                                    width: 25%;\n",
    "                                    height: 100%;\n",
    "                                    margin: 15px 10px;\n",
    "                                }\n",
    "                                .img2{\n",
    "                                    height:60%;\n",
    "                                    width:auto;\n",
    "                                }\n",
    "                            </style>\n",
    "\n",
    "                            <body>\n",
    "                                <p>Here is an example </p>\n",
    "                                <div class=div>\n",
    "                                    <div class=div1><img class=img2 src=cid:image1></div>\n",
    "                                    <div class=\"vl\"></div>\n",
    "                                    <div class=div2>\n",
    "                                        <a href=\"https://outlook.office.com/mail/inbox/id/AAQkADZlNGQxMzAyLWRhMjEtNGRlZS1hMjVhLWMxNjY1N2NjNjQzMwAQAK4bb%2Fjdh0%2FendGgHQAxkrk%3D\">support@sattrixsoftware.com</a><br>\n",
    "                                        <a href=\"https://twitter.com/i/flow/login?redirect_after_login=%2FSattrixSoftware\"> <img class=\"img\" src=cid:image3 alt=\"image not found\"></a>\n",
    "                                        <a href=\"https://www.linkedin.com/company/sattrix-software-solutions/\"> <img class=\"img\" src=cid:image4 alt=\"image not found\"></a>\n",
    "\n",
    "                                    </div>\n",
    "                                    <div class=\"vl\"></div>\n",
    "                                    <div class=div3><img width=80% src=cid:image2></div>\n",
    "                                </div><br>\n",
    "                            </body>\n",
    "                            </html>\n",
    "                    \"\"\"\n",
    "            email.attach(MIMEText(html, 'html'))\n",
    "            with open(\"C:/Users/Admin/Downloads/thumbnail_image002.jpg\", 'rb') as f1:\n",
    "                img_data = f1.read()\n",
    "            image = MIMEImage(img_data)\n",
    "            email.attach(image)\n",
    "            image.add_header('Content-ID', '<image1>')\n",
    "            with open(\"C:/Users/Admin/Downloads/thumbnail_image005.png\", 'rb') as f1:\n",
    "                img_data2 = f1.read()\n",
    "            image = MIMEImage(img_data2)\n",
    "            image.add_header('Content-ID', '<image2>')\n",
    "            email.attach(image)\n",
    "            with open(\"C:/Users/Admin/Downloads/twitter.png\", 'rb') as f1:\n",
    "                img_data3 = f1.read()\n",
    "            image = MIMEImage(img_data3)\n",
    "            image.add_header('Content-ID', '<image3>')\n",
    "            email.attach(image)\n",
    "            with open(\"C:/Users/Admin/Downloads/linkdin.webp\", 'rb') as f1: \n",
    "                img_data4= f1.read()\n",
    "            image = MIMEImage(img_data4)\n",
    "            image.add_header('Content-ID', '<image4>')\n",
    "            email.attach(image)\n",
    "            mailserver = smtplib.SMTP(EmailConfigHost, EmailConfigPort)\n",
    "            mailserver.ehlo()\n",
    "            mailserver.starttls()\n",
    "            mailserver.ehlo()\n",
    "            mailserver.login(EmailFromId, EmailPassword)\n",
    "            mailserver.sendmail(EmailFromId, emailto, email.as_string())\n",
    "            mailserver.close()\n",
    "        except smtplib.SMTPDataError as e:\n",
    "            print(\"error while sending email: {}\".format(e))\n",
    "    if report_notification == 2:\n",
    "        dict3 = {'Report_Id': str(report_id), 'Report_Name': report_name,\"customerName\":customerName[1:-1]}\n",
    "        url = 'https://192.168.21.198:8082/api/rulereport'\n",
    "        response = requests.post(url, json=dict3,verify=False)\n",
    "report_notification_alert(report_id, report_name, report_notification,customerName,save_location)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2a9a7e40",
   "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.9.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}