{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import glob "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\\Accelerometer.csv (15201, 5) Index(['time', 'seconds_elapsed', 'z', 'y', 'x'], dtype='object')\n",
      "\\AccelerometerUncalibrated.csv (15201, 5) Index(['time', 'seconds_elapsed', 'z', 'y', 'x'], dtype='object')\n",
      "\\Barometer.csv (145, 4) Index(['time', 'seconds_elapsed', 'relativeAltitude', 'pressure'], dtype='object')\n",
      "\\Gravity.csv (15201, 5) Index(['time', 'seconds_elapsed', 'z', 'y', 'x'], dtype='object')\n",
      "\\Gyroscope.csv (15201, 5) Index(['time', 'seconds_elapsed', 'z', 'y', 'x'], dtype='object')\n",
      "\\GyroscopeUncalibrated.csv (15201, 5) Index(['time', 'seconds_elapsed', 'z', 'y', 'x'], dtype='object')\n",
      "\\Location.csv (153, 12) Index(['time', 'seconds_elapsed', 'altitude', 'speedAccuracy',\n",
      "       'bearingAccuracy', 'latitude', 'altitudeAboveMeanSeaLevel', 'bearing',\n",
      "       'horizontalAccuracy', 'verticalAccuracy', 'longitude', 'speed'],\n",
      "      dtype='object')\n",
      "\\Magnetometer.csv (15201, 5) Index(['time', 'seconds_elapsed', 'z', 'y', 'x'], dtype='object')\n",
      "\\MagnetometerUncalibrated.csv (15201, 5) Index(['time', 'seconds_elapsed', 'z', 'y', 'x'], dtype='object')\n",
      "\\Metadata.csv (1, 8) Index(['version', 'device name', 'recording time', 'platform', 'appVersion',\n",
      "       'device id', 'sensors', 'sampleRateMs'],\n",
      "      dtype='object')\n",
      "\\Orientation.csv (15201, 9) Index(['time', 'seconds_elapsed', 'yaw', 'qx', 'qz', 'roll', 'qw', 'qy',\n",
      "       'pitch'],\n",
      "      dtype='object')\n"
     ]
    }
   ],
   "source": [
    "filepath = r'C:\\Users\\User\\Downloads\\포트홀 주행 데이터\\2023-05-30_05-10-55'\n",
    "for filename in glob.glob(filepath+'\\*.csv'):\n",
    "    try :\n",
    "        df= pd.read_csv(filename)\n",
    "        print(filename.replace(filepath,''),df.shape,df.columns)    \n",
    "    except:\n",
    "        continue\n",
    "    #"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(df.shape,df.columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "import psycopg2\n",
    "import time\n",
    "from datetime import timedelta,datetime\n",
    "\n",
    "class DB():\n",
    "    def __init__(self):\n",
    "        self.conn=psycopg2.connect(\n",
    "            host='localhost',\n",
    "            dbname='postgres',\n",
    "            user='postgres',\n",
    "            password='ts4430!@',\n",
    "            port='5432'\n",
    "            ) # db에 접속\n",
    "        self.conn.autocommit=True\n",
    "        \n",
    "    def db_add_report(self,report_id,report_x,report_y) :\n",
    "        cur = self.conn.cursor() # 커서를 생성한다\n",
    "        now=time.localtime()\n",
    "        d=time.strftime('%Y-%m-%d %X', now)\n",
    "        cur.execute(f'''\n",
    "        insert into \"TRAFFICAGENCY\".report (report_id,report_x,report_y,timestamp)\n",
    "        values ('{report_id}','{report_x}','{report_y}','{d}')\n",
    "        ''')\n",
    "        \n",
    "    def db_get_near_point(self,dest_x,dest_y):\n",
    "        cur = self.conn.cursor() # 커서를 생성한다\n",
    "        now=datetime.now() \n",
    "        d_plus=now +timedelta(hours=1)\n",
    "        d_plus=str(\"'\"+d_plus.strftime('%Y-%m-%d %X')+\"'\")\n",
    "        d_minus=now -timedelta(hours=1)\n",
    "        d_minus=str(\"'\"+d_minus.strftime('%Y-%m-%d %X')+\"'\")\n",
    "        cur.execute(f'''\n",
    "        select report_x, report_y\n",
    "        from \"TRAFFICAGENCY\".report \n",
    "        where {dest_y} > report_y  - 0.000498 and {dest_y} <= report_y  + 0.000498\n",
    "        and {dest_x} > report_x  - 0.000498 and {dest_x} <= report_x + 0.000498\n",
    "        and timestamp between {d_minus} and {d_plus};\n",
    "\n",
    "\n",
    "        ''')\n",
    "        result=cur.fetchall()\n",
    "        return result\n",
    "    \n",
    "    def db_add_pothole(pothole_id,pothole_location_x,pothole_location_y) :\n",
    "        cur = self.conn.cursor() # 커서를 생성한다\n",
    "        now=time.localtime()\n",
    "        d=time.strftime('%Y-%m-%d %X', now)\n",
    "        cur.execute(f'''\n",
    "        insert into \"TRAFFICAGENCY\".pothole (pothole_id,pothole_location_x,pothole_location_y,timestamp)\n",
    "        values ('{pothole_id}','{pothole_location_x}','{pothole_location_y}','{d}')\n",
    "        ''')    \n",
    "    \n",
    "\n",
    "\n",
    "db = DB()\n",
    "pothole_x = 123.123\n",
    "pothole_y = 32.123\n",
    "db.db_add_report(2,pothole_x,pothole_y)\n",
    "result=db.db_get_near_point(pothole_x,pothole_y)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "123.123\n",
      "32.123\n"
     ]
    }
   ],
   "source": [
    "if len(result) >3 :\n",
    "    value = 0\n",
    "    for i in range(len(result)):\n",
    "        value += result[i][0]\n",
    "    average_x= (value / len(result))\n",
    "    value2 = 0\n",
    "    for i in range(len(result)):\n",
    "        value2 += result[i][1]\n",
    "    average_y= (value2 / len(result))\n",
    "    db.db_add_pothole(1,average_x,average_y)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "from datetime import timedelta,datetime\n",
    "now=datetime.now() \n",
    "d_plus=now +timedelta(hours=1)\n",
    "d_plus=str(d_plus.strftime('%Y-%m-%d %X'))\n",
    "d_minus=now -timedelta(hours=1)\n",
    "d_minus=str(d_minus.strftime('%Y-%m-%d %X'))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'2023-07-17 17:43:29'"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "d_plus"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "time.struct_time(tm_year=2023, tm_mon=7, tm_mday=17, tm_hour=16, tm_min=30, tm_sec=2, tm_wday=0, tm_yday=198, tm_isdst=0)"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "time.localtime() "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "select report_x, report_y \n",
    "from \"TRAFFICAGENCY\".report \n",
    "where 32.123 > report_y  - 0.000498 and 32.123 <= report_y  + 0.000498\n",
    "and 123.123 > report_x  - 0.000498 and 123.123 <= report_x + 0.000498\n",
    "and timestamp between {d_plus} and {d_minus}\n",
    "\n",
    "pothole_x = 123.123\n",
    "pothole_y = 32.123"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "base",
   "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.11.4"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}