util/search_data.py

from geopy.distance import geodesic
from util.dbquery import connection
import requests, os
from datetime import datetime, timedelta

def find_nearest_markets(lat, lon):
    conn = connection()
    mycursor = conn.cursor()
    query = "SELECT Market_name_KR, Latitude, Longitude FROM Market_Info"
    mycursor.execute(query)
    markets = mycursor.fetchall()

    distances = []

    for market in markets:
        market_name, market_lat, market_lon = market
        distance = geodesic((lat, lon), (market_lat, market_lon)).kilometers
        distances.append({"이름":market_name, "위도":market_lat, "경도":market_lon, "거리":distance})

    # 거리에 따라 시장을 정렬
    distances.sort(key=lambda x: x['거리'])

    return distances[:10]
    
def get_market_location(market_name):
    conn = connection()
    #입력받은 시장 명에 대한 위도와 경도 찾기
    mycursor1 = conn.cursor()
    query1 = f"SELECT Latitude, Longitude FROM Market_Info WHERE Market_name_KR = \'{market_name}\'"
    mycursor1.execute(query1)
    market_info = mycursor1.fetchall()
    market_lat = market_info[0][0]
    market_lon = market_info[0][1]

    return market_lat, market_lon
    
def find_nearest_place(market_name):
    conn = connection()
    #입력받은 시장 명에 대한 위도와 경도 찾기
    mycursor1 = conn.cursor()
    query1 = f"SELECT Latitude, Longitude FROM Market_Info WHERE Market_name_KR = \'{market_name}\'"
    mycursor1.execute(query1)
    market_info = mycursor1.fetchall()
    market_lat = market_info[0][0]
    market_lon = market_info[0][1]

    print(f"{market_name} : {market_lat}, {market_lon}")
    
    #시장과 가장 가까운 거리의 인구 정보
    mycursor2 = conn.cursor()
    query2 = "SELECT id, Latitude, Longitude FROM Population"
    mycursor2.execute(query2)
    coordinate = mycursor2.fetchall()

    # # 가장 가까운 장소명과 거리 초기화
    nearest_place = None
    min_distance = float('inf')
    index = 1
    
    for id, lat, lon in coordinate:
        place_lat = lat
        place_lon = lon

        # 시장과 거리 계산
        distance = geodesic((market_lat, market_lon), (place_lat, place_lon)).kilometers

        # 가장 가까운 거리 업데이트
        if distance < min_distance:
            min_distance = distance
            mycursor3 = conn.cursor()
            query3 = f"SELECT Area_name_KR FROM Population WHERE Latitude=\'{place_lat}\' AND Longitude=\'{place_lon}\'"
            mycursor3.execute(query3)
            nearest_place = mycursor3.fetchall()[0][0]      
    
    return id, nearest_place, min_distance

def get_season_data_by_month(month):
    # 제철정보 api
    url = "http://211.237.50.150:7080/openapi/{}/json/Grid_20171128000000000572_1/1/1000"
    
    # API 호출
    KEY = os.environ['FOOD_API']
    response = requests.get(url.format(KEY), params={'M_DISTCTNS': f"{month}월"})

    if response.status_code == 200:
        data = response.json()
        output = [{"이름":d['PRDLST_NM'],"제철":d['M_DISTCTNS'],"기간":d['PRDCTN__ERA'],"지역":d['MTC_NM']} for d in data['Grid_20171128000000000572_1']['row']]
        return output
    else:
        return 'API 호출 실패'
        
def get_season_data_by_name(name):
    # 제철정보 api
    url = "http://211.237.50.150:7080/openapi/{}/json/Grid_20171128000000000572_1/1/1000"
    
    # API 호출
    KEY = os.environ['FOOD_API']
    response = requests.get(url.format(KEY), params={'PRDLST_NM': f"{name.split(' ')[0]}"})

    if response.status_code == 200:
        data = response.json()
        if len(data['Grid_20171128000000000572_1']['row']) == 0:
            return {"이름" : name.split(' ')[0], "제철" : -1, "detail" : "정보가 존재하지 않습니다"}
        output = {
            "이름" : data['Grid_20171128000000000572_1']['row'][0]['PRDLST_NM'],
            "제철" : data['Grid_20171128000000000572_1']['row'][0]['M_DISTCTNS'],
            "기간" : data['Grid_20171128000000000572_1']['row'][0]['PRDCTN__ERA'],
            "지역" : data['Grid_20171128000000000572_1']['row'][0]['MTC_NM'],
            "카테고리" : data['Grid_20171128000000000572_1']['row'][0]['PRDLST_CL'],
            "효능" : data['Grid_20171128000000000572_1']['row'][0]['EFFECT'],
            "기타" : data['Grid_20171128000000000572_1']['row'][0]['PURCHASE_MTH']
        }
        return output
    else:
        return 'API 호출 실패'
        
# 특정 장소의 정보 또는 가장 가까운 장소의 정보를 가져오는 함수
def get_congestion_data(place_name, index):
    # 서울 실시간 인구데이터 API 엔드포인트
    url = 'http://openapi.seoul.go.kr:8088/{}/json/citydata_ppltn/{}/{}/{}'

    # API 호출
    KEY = os.environ['POPULATION_API']
    response = requests.get(url.format(KEY, index, index, place_name))

    # API 응답 확인
    if response.status_code == 200:
        data = response.json()
        congestion = process_congestion_data(data)
        return congestion
    else:
        return 'API 호출 실패'

# 혼잡도 처리
def process_congestion_data(data):
    # print(data)
    print(data['SeoulRtd.citydata_ppltn'][0]['PPLTN_TIME'])
    congestion = data['SeoulRtd.citydata_ppltn'][0]['AREA_CONGEST_LVL']
    print(f"{data['SeoulRtd.citydata_ppltn'][0]['AREA_NM']} : {congestion}({data['SeoulRtd.citydata_ppltn'][0]['AREA_CONGEST_MSG']})")
    return congestion, data['SeoulRtd.citydata_ppltn'][0]['AREA_CONGEST_MSG']

#시장 규모 관련
def determine_market_size(market_name):

    conn = connection()
    #입력받은 시장 명에 대한 위도와 경도 찾기
    mycursor1 = conn.cursor()
    query1 = f"SELECT Building_scale, Num_of_stores FROM Market_Info WHERE Market_name_KR = \'{market_name}\'"
    mycursor1.execute(query1)
    market_info = mycursor1.fetchall()
    area = market_info[0][0]
    store_count = market_info[0][1]
    
    print(f"면적:{area}, 점포수:{store_count}")
    # 점포 수를 기준으로 시장의 규모 분류
    market_size = ""
    if store_count < 100:
        market_size = '소형'
    elif store_count < 500:
        market_size = '중형'
    elif store_count < 1000:
        market_size = '중대형'
    else:
        market_size = '대형'

    return market_size, area, store_count


def get_monthly_average_price(product_name):
    conn = connection()
    try:
        with conn.cursor(dictionary=True) as cursor:
            three_years_ago = datetime.now() - timedelta(days=3*365)
            
            query = """
            SELECT AVG(Product_price) AS avg_price, YEAR(UpdateDate) AS year, MONTH(UpdateDate) AS month
            FROM Markets
            WHERE Product_name = %s AND UpdateDate >= %s
            GROUP BY YEAR(UpdateDate), MONTH(UpdateDate)
            ORDER BY YEAR(UpdateDate), MONTH(UpdateDate);
            """
            cursor.execute(query, (product_name, three_years_ago))
            result = cursor.fetchall()
            result.reverse()
            monthly_prices = [{"year": row["year"], "month": row["month"], "avg_price": int(row["avg_price"])} for row in result]
            return monthly_prices
    finally:
        conn.close()

def get_lowest_price_markets(product_name):
    conn = connection()
    try:
        with conn.cursor(dictionary=True) as cursor:
            query = """
            SELECT Market_name_KR AS market, Product_price AS price, UpdateDate AS date
            FROM Markets
            WHERE Product_name = %s AND UpdateDate >= NOW() - INTERVAL 2 MONTH AND Product_price > 0
            ORDER BY Product_price ASC
            LIMIT 3;
            """
            cursor.execute(query, (product_name,))
            result = cursor.fetchall()
            return result
    finally:
        conn.close()

def get_all_product_names():
    conn = connection()  # 위에서 정의한 connection() 함수를 사용하여 DB에 연결
    product_names = []  # 품목명을 저장할 빈 리스트 초기화

    try:
        with conn.cursor() as cursor:
            # SQL 쿼리를 사용하여 모든 고유한 품목명을 조회
            query = "SELECT DISTINCT Product_name FROM Markets"
            cursor.execute(query)
            
            # 조회 결과를 반복 처리
            for row in cursor.fetchall():
                product_names.append(row[0])  # 각 품목명을 리스트에 추가

    finally:
        conn.close()  # 데이터베이스 연결 종료

    with open("data/products.txt", "w", encoding = "utf-8") as f:
        for p in product_names:
            if p!='s' and p!='' and p!=' ':
                f.write(p)
                f.write('\n')
    return product_names  # 품목명 리스트 반환


def get_cheapest_products_this_month(market_name):
    conn = connection()
    try:
        with conn.cursor(dictionary=True) as cursor:
            current_year = datetime.now().year

            market_query = """
            SELECT Product_name, AVG(Product_price) AS avg_price
            FROM Markets
            WHERE Market_name_KR = %s AND UpdateDate >= NOW() - INTERVAL 2 MONTH AND Product_price > 0
            GROUP BY Product_name
            """
            cursor.execute(market_query, (market_name,))
            market_prices = cursor.fetchall()
            national_query = """
            SELECT Product_name, AVG(Product_price) AS avg_price
            FROM Markets
            WHERE YEAR(UpdateDate) = %s AND UpdateDate >= NOW() - INTERVAL 2 MONTH AND Product_price > 0
            GROUP BY Product_name
            """
            cursor.execute(national_query, (current_year,))
            national_prices = {row['Product_name']: row['avg_price'] for row in cursor.fetchall()}
            cheapest_products = []
            for product in market_prices:
                national_avg_price = national_prices.get(product['Product_name'])
                if national_avg_price and product['avg_price'] < national_avg_price:
                    discount_rate = (national_avg_price - product['avg_price']) / national_avg_price * 100  # 할인율 계산
                    cheapest_products.append({
                        '이름': product['Product_name'],
                        '할인율': int(discount_rate),
                        '시세': int(product['avg_price']),
                        '평균': int(national_avg_price),
                    })

            # 할인율을 기준으로 정렬하고 상위 3개만 선택
            cheapest_products = sorted(cheapest_products, key=lambda x: x['할인율'], reverse=True)[:3]

            return cheapest_products
    finally:
        conn.close()