git -C '/home/opc/rocketa.git' show 45de6a1 -- oci_func/test/func.pycommit 45de6a1d4fa7ddaa97757ca5d33499b443241a73
Author: Satoshi Ujihara <satoshi_ujihara@fivegate.jp>
Date: Mon Nov 3 13:50:34 2025 +0900
画像で文字を表示している部分をCSSに変更
test click機能追加
click時のsqlを元に戻した。
diff --git a/oci_func/test/func.py b/oci_func/test/func.py
new file mode 100644
index 0000000..8119a32
--- /dev/null
+++ b/oci_func/test/func.py
@@ -0,0 +1,628 @@
+
+import io
+import json
+import logging
+import json
+from sqlalchemy import create_engine, text
+import datetime
+import string
+import random
+import urllib.parse
+import hashlib
+import os, time
+
+
+from fdk import response
+
+
+os.environ['TZ'] = 'Asia/Tokyo'
+time.tzset()
+logging.basicConfig(level=logging.INFO)
+
+
+def handler(ctx, data: io.BytesIO = None):
+
+ logging.info("handler started")
+ headers = ctx.Headers()
+ full_url = ctx.RequestURL()
+ query_string = urllib.parse.urlparse(full_url).query
+ query_params = urllib.parse.parse_qs(query_string)
+
+ DB_HOST = "api.rocket-a.com"
+ DB_USER = "root"
+ DB_PASSWORD = "buSDonry4%h6rm-0fy"
+ DB_NAME = "rocketa-api"
+
+ # 接続URLの作成: mysql+pymysql://user:password@host/dbname
+ DB_URL = f"mysql+pymysql://{DB_USER}:{DB_PASSWORD}@{DB_HOST}/{DB_NAME}"
+ engine = create_engine(DB_URL)
+ connection = engine.connect()
+
+ # 変数初期値
+ dt_now = datetime.datetime.now() + datetime.timedelta(hours = 9)
+ date = dt_now.strftime('%Y%m%d%H%M%S')
+ format_date = dt_now.strftime('%Y-%m-%d %H:%M:%S')
+ created_ym = dt_now.strftime('%Y%m')
+ url = ''
+ result_method = 1
+ adjust_token = ''
+ dv_pc = 0
+ dv_and_dc = 0
+ dv_and_au = 0
+ dv_and_sb = 0
+ dv_and_other = 0
+ dv_ios_dc = 0
+ dv_ios_au = 0
+ dv_ios_sb = 0
+ dv_ios_other = 0
+ error = 0
+ ad_id = ''
+ client_id = ''
+ media_id = ''
+ uid = ''
+ banner_id = ''
+ ip = headers.get('x-real-ip')
+ # user_agent = headers.get('User-Agent')
+ user_agent = headers.get('user-agent')[1]
+ referer = ''
+ net_price = ''
+ net_price_unit = ''
+ gross_price = ''
+ gross_price_unit = ''
+ group_id = ''
+ other_parameters = ''
+ hash_value = ''
+ hash_text = ''
+
+ other_parameter_list =[]
+
+ defined_parameters = {
+ 'ad_id',
+ 'client_id',
+ 'media_id',
+ 'media_uid',
+ 'banner_id',
+ 'sid',
+ 'amount',
+ 'reward',
+ 'count',
+ 'status',
+ 'date',
+ 'stage'
+ }
+ if headers.get('referer') is not None:
+ referer = headers.get('referer')
+
+ # GETパラメータチェックと変数化
+ if query_params.get('ad_id') is not None:
+ ad_id = query_params.get('ad_id', [None])[0]
+ else:
+ error = 101
+
+ if query_params.get('client_id') is not None:
+ client_id = query_params.get('client_id', [None])[0]
+
+ else:
+ error = 102
+
+ if query_params.get('media_id') is not None:
+ media_id = query_params.get('media_id', [None])[0]
+
+ else:
+ error = 103
+
+ if query_params.get('media_uid') is not None:
+ uid = query_params.get('media_uid', [None])[0]
+
+ if query_params.get('banner_id') is not None:
+ banner_id = query_params.get('banner_id', [None])[0]
+
+ # パートナー独自パラメータをother_parametersに保存
+ for key, value in query_params.items():
+ logging.info(f"■■■125■■■■{key}={value[0]}")
+ if key not in defined_parameters:
+# other_parameters = other_parameters + key + '=' + value + '&'
+ other_parameter_list.append(f"{key}={value[0]}")
+
+ other_parameters = '&'.join(other_parameter_list)
+ if other_parameters is not None:
+ logging.info(f"■■■131■■■■{other_parameters}")
+# other_parameters = other_parameters.rstrip('&')
+
+ logging.info(f"■■■163■■■■{error}")
+ # 広告詳細取得
+ if error == 0:
+ sql_query = 'SELECT '
+ sql_query += ' a.url, '
+ sql_query += ' a.result_method, '
+ sql_query += ' a.dv_pc, '
+ sql_query += ' a.dv_and_dc, '
+ sql_query += ' a.dv_and_au, '
+ sql_query += ' a.dv_and_sb, '
+ sql_query += ' a.dv_and_other, '
+ sql_query += ' a.dv_ios_dc, '
+ sql_query += ' a.dv_ios_au, '
+ sql_query += ' a.dv_ios_sb, '
+ sql_query += ' a.dv_ios_other, '
+ sql_query += ' a.adjust_token '
+ sql_query += 'FROM '
+ sql_query += ' ad_datas AS a '
+ sql_query += 'INNER JOIN '
+ sql_query += ' ad_join_media_datas AS j '
+ sql_query += 'ON '
+ sql_query += ' a.master_ad_id = j.master_ad_id '
+ sql_query += 'WHERE '
+ sql_query += ' a.master_ad_id = :master_ad_id '
+ sql_query += 'AND '
+ sql_query += ' a.client_id = :client_id '
+ sql_query += 'AND '
+ sql_query += ' j.media_id = :media_id '
+ sql_query += 'AND '
+ sql_query += ' a.status = 1 '
+ sql_query += 'AND '
+ sql_query += ' j.status = 1 '
+ sql_query += 'AND '
+ sql_query += ' a.start_date <= CAST(:start_date AS DATETIME) '
+ sql_query += 'AND '
+ sql_query += ' a.end_date > CAST(:end_date AS DATETIME) '
+ sql_query += 'AND '
+ sql_query += ' a.url != "" '
+ sql_query += 'LIMIT 1 '
+
+ params = {
+ "master_ad_id": int(ad_id),
+ "client_id": int(client_id),
+ "media_id": int(media_id),
+ "start_date": format_date,
+ "end_date": format_date,
+ }
+ # SQLAlchemyが安全にクエリを組み立て、SQLインジェクションを防ぐ
+ result = connection.execute(text(sql_query), params)
+
+ # 結果を取得
+ ad_data = result.fetchone()
+
+ if ad_data:
+ ad_data_dict = ad_data._asdict()
+ url = ad_data_dict['url']
+ result_method = ad_data_dict['result_method']
+ dv_pc = ad_data_dict['dv_pc']
+ dv_and_dc = ad_data_dict['dv_and_dc']
+ dv_and_au = ad_data_dict['dv_and_au']
+ dv_and_sb = ad_data_dict['dv_and_sb']
+ dv_and_other = ad_data_dict['dv_and_other']
+ dv_ios_dc = ad_data_dict['dv_ios_dc']
+ dv_ios_au = ad_data_dict['dv_ios_au']
+ dv_ios_sb = ad_data_dict['dv_ios_sb']
+ dv_ios_other = ad_data_dict['dv_ios_other']
+
+ try:
+ adjust_token = ad_data_dict['adjust_token']
+ except KeyError:
+ pass
+
+ logging.info(dict(headers))
+ logging.info(f"■■■236■■■■{user_agent}")
+
+ #デバイスチェック
+ # if any("iPhone OS" in item for item in user_agent) or any("iPad" in item for item in user_agent):
+ if 'iPhone OS' in user_agent or 'iPad' in user_agent:
+ if dv_ios_dc == 0 and dv_ios_au == 0 and dv_ios_sb == 0 and dv_ios_other == 0:
+ error = 2
+
+ # elif any("Android" in item for item in user_agent):
+ elif 'Android' in user_agent:
+ if dv_and_dc == 0 and dv_and_au == 0 and dv_and_sb == 0 and dv_and_other == 0:
+ error = 2
+
+ else:
+ if dv_pc == 0:
+ error = 2
+ else:
+ error = 301
+
+
+
+ logging.info("■■■255■■■■")
+ # 特別遷移先設定取得
+ if banner_id != '':
+ sql_query = 'SELECT '
+ sql_query += ' url, '
+ sql_query += ' ios_url, '
+ sql_query += ' and_url '
+ sql_query += 'FROM '
+ sql_query += ' ad_material_datas '
+ sql_query += 'WHERE '
+ sql_query += ' status = 1 '
+ sql_query += 'AND '
+ sql_query += ' master_ad_id = :master_ad_id '
+ sql_query += 'AND '
+ sql_query += ' master_material_id = :master_material_id '
+ sql_query += 'LIMIT 1 '
+
+
+ params = {
+ "master_ad_id": int(ad_id),
+ "master_material_id": int(banner_id),
+ }
+ # SQLAlchemyが安全にクエリを組み立て、SQLインジェクションを防ぐ
+ result = connection.execute(text(sql_query), params)
+
+ # 結果を取得
+ ad_material_data = result.fetchone()
+
+
+ if ad_material_data:
+ ad_material_data_dict = ad_material_data._asdict()
+ # 特別遷移先に書き換え
+ if ad_material_data_dict['url']:
+ url = ad_material_data_dict['url']
+ # iOS
+ if ad_material_data_dict['ios_url']:
+ url = ad_material_data_dict['ios_url']
+ # Android
+ if ad_material_data_dict['and_url']:
+ url = ad_material_data_dict['and_url']
+ # sid生成
+ if error == 0:
+ while True:
+ sid = ''
+
+ dat = string.digits + string.ascii_lowercase
+ random_string = ''.join([random.choice(dat) for i in range(16)])
+ sid = random_string + date
+
+ sql_query = 'SELECT '
+ sql_query += ' id '
+ sql_query += 'FROM '
+ sql_query += ' click_records '
+ sql_query += 'WHERE '
+ sql_query += ' sid = :sid '
+ sql_query += 'AND '
+ sql_query += ' created_ym = :created_ym '
+ sql_query += 'LIMIT 1'
+
+
+ params = {
+ "sid": sid,
+ "created_ym": int(created_ym)
+ }
+ # SQLAlchemyが安全にクエリを組み立て、SQLインジェクションを防ぐ
+ result = connection.execute(text(sql_query), params)
+
+ # 結果を取得
+ click_data = result.fetchone()
+
+ if not click_data:
+ break
+
+ # クリック時の報酬を取得
+ logging.info(f"■■■329■■■■{error}")
+ if error == 0:
+ sql_query = 'SELECT '
+ sql_query += ' net_price, '
+ sql_query += ' net_price_unit, '
+ sql_query += ' gross_price, '
+ sql_query += ' gross_price_unit, '
+ sql_query += ' group_id '
+ sql_query += 'FROM '
+ sql_query += ' ad_reward_datas '
+ sql_query += 'WHERE '
+ sql_query += ' ad_id = :ad_id '
+ sql_query += 'AND '
+ sql_query += ' type = 1 '
+ sql_query += 'AND '
+ sql_query += ' stage_id IS NULL '
+ sql_query += 'AND '
+ sql_query += ' product_id IS NULL '
+ sql_query += 'AND '
+ sql_query += ' ( '
+ sql_query += ' media_id = :media_id '
+ sql_query += ' OR '
+ sql_query += ' media_id IS NULL '
+ sql_query += ' ) '
+ sql_query += 'ORDER BY media_id DESC '
+
+ params = {
+ "ad_id": int(ad_id),
+ "media_id": int(media_id),
+ }
+ # SQLAlchemyが安全にクエリを組み立て、SQLインジェクションを防ぐ
+ result = connection.execute(text(sql_query), params)
+
+ # 結果を取得
+ reward_list = result.fetchall()
+
+ if reward_list:
+ for reward_data in reward_list:
+ try:
+ reward_data_dict = reward_data._asdict()
+ group_id = reward_data_dict['group_id']
+
+ sql_query = 'SELECT '
+ sql_query += ' id '
+ sql_query += 'FROM '
+ sql_query += ' ad_reward_group_datas '
+ sql_query += 'WHERE '
+ sql_query += ' status = 1 '
+ sql_query += 'AND '
+ sql_query += ' master_id = :master_id '
+ sql_query += 'AND '
+ sql_query += ' ad_id = :ad_id '
+ # sql_query += 'AND '
+ # sql_query += ' valid_start_date <= :valid_start_date '
+ # sql_query += 'AND '
+ # sql_query += ' valid_end_date > :valid_end_date '
+
+ logging.info(f"■■■386■■■■{reward_data_dict}")
+ params = {
+ "master_id": int(group_id or 0),
+ "ad_id": int(ad_id or 0),
+ "valid_start_date": format_date,
+ "valid_end_date": format_date,
+ }
+ # SQLAlchemyが安全にクエリを組み立て、SQLインジェクションを防ぐ
+ result = connection.execute(text(sql_query), params)
+
+ # 結果を取得
+ group_data = result.fetchone()
+
+ if group_data:
+ net_price = reward_data_dict['net_price']
+ net_price_unit = reward_data_dict['net_price_unit']
+ gross_price = reward_data_dict['gross_price']
+ gross_price_unit = reward_data_dict['gross_price_unit']
+ break
+
+ else:
+ net_price = reward_data_dict['net_price']
+ net_price_unit = reward_data_dict['net_price_unit']
+ gross_price = reward_data_dict['gross_price']
+ gross_price_unit = reward_data_dict['gross_price_unit']
+ continue
+ except KeyError:
+ logging.info(f"■■■415■■■■KeyError")
+ pass
+
+ else:
+ #報酬額データがない
+ error=302
+
+ #報酬額データがない
+ if net_price is None or net_price_unit is None or gross_price is None or gross_price_unit is None:
+ error=302
+ logging.info(f"■■■424■■■■報酬額ない")
+ # sidと遷移情報を保存
+ if error == 0:
+ sql_query = 'INSERT INTO '
+ sql_query += ' click_records_test '
+ sql_query += '( '
+ sql_query += ' master_ad_id, '
+ sql_query += ' client_id, '
+ sql_query += ' media_id, '
+ sql_query += ' banner_id, '
+ sql_query += ' uid, '
+ sql_query += ' sid, '
+ sql_query += ' ip, '
+ sql_query += ' user_agent, '
+ sql_query += ' referer, '
+ sql_query += ' net_price, '
+ sql_query += ' net_price_unit, '
+ sql_query += ' gross_price, '
+ sql_query += ' gross_price_unit, '
+ sql_query += ' other_parameters, '
+ sql_query += ' created_ym '
+ sql_query += ') VALUES ( '
+ sql_query += ' :master_ad_id, '
+ sql_query += ' :client_id, '
+ sql_query += ' :media_id, '
+ sql_query += ' :banner_id, '
+ sql_query += ' :uid, '
+ sql_query += ' :sid, '
+ sql_query += ' :ip, '
+ sql_query += ' :user_agent, '
+ sql_query += ' :referer, '
+ sql_query += ' :net_price, '
+ sql_query += ' :net_price_unit, '
+ sql_query += ' :gross_price, '
+ sql_query += ' :gross_price_unit, '
+ sql_query += ' :other_parameters, '
+ sql_query += ' :created_ym '
+ sql_query += ') '
+
+ params = {
+ "master_ad_id": int(ad_id),
+ "client_id": int(client_id),
+ "media_id": int(media_id),
+ "banner_id": int(banner_id) if banner_id else None,
+ "uid": uid if uid else None,
+ "sid": sid,
+ "ip": ip,
+ "user_agent": user_agent,
+ "referer": referer if referer else None,
+ "net_price": net_price if net_price else None,
+ "net_price_unit": net_price_unit if net_price_unit else None,
+ "gross_price": gross_price if gross_price else None,
+ "gross_price_unit": gross_price_unit if gross_price_unit else None,
+ "other_parameters": other_parameters if other_parameters else None,
+ "created_ym": int(created_ym),
+ }
+ result = connection.execute(text(sql_query), params)
+ logging.info(f"■■■445■■■{text(sql_query)}")
+ # トランザクションをコミットして変更を永続化
+ connection.commit()
+ """
+ # 広告ページへ遷移
+ if '?' in url:
+ # ソケット通信の時はパラメータはデフォ
+ if int(result_method) == 2:
+ url += '&sid=' + sid + '&ad_id=' + ad_id + '&client_id=' + client_id + '&media_id=' + media_id
+
+ # AppsFlyer特別処理
+ elif int(result_method) == 3:
+ url += '&af_siteid=' + media_id + '&pid=adleap_int&af_click_lookback=7d&af_sub4=' + sid + '&af_sub1=' + ad_id + '&af_sub2=' + client_id + '&af_sub3=' + media_id
+
+ # Adjust特別処理
+ elif int(result_method) == 4:
+ callback_url = 'https://api.rocket-a.com/service/result?sid=' + sid + '&ad_id=' + ad_id + '&client_id=' + client_id + '&media_id=' + media_id + '&device_id={idfa||gps_adid}&adjust_id={adid}'
+
+ if uid != '':
+ callback_url += '&media_uid=' + uid
+
+ encode_callback_url = urllib.parse.quote(callback_url, safe='')
+
+ url += '&install_callback=' + encode_callback_url
+
+ # CPE
+ if adjust_token != '':
+ encode_callback_url_add_token = urllib.parse.quote(callback_url + '&adjust_token=' + adjust_token, safe='')
+
+ url += '&event_callback_' + adjust_token + '=' + encode_callback_url_add_token
+
+ # Airbridge特別処理
+ elif int(result_method) == 5:
+ url += '&sid=' + sid + '&custom_ad_id=' + ad_id + '&custom_client_id=' + client_id + '&sub_id=' + media_id
+
+ # Tyrads特別処理
+ elif int(result_method) == 6:
+ url += '&sub1=' + sid + '&sub2=' + ad_id + '&sub3=' + client_id + '&media_id=' + media_id
+
+ # AyeT Studios特別処理
+ elif int(result_method) == 7:
+ url += '&custom_1=' + sid + '&custom_2=' + ad_id + '&custom_3=' + client_id + '&media_id=' + media_id
+
+ # Torox特別処理
+ elif int(result_method) == 8:
+ url += '&user_id=' + sid + '&subid1=' + ad_id + '&subid2=' + client_id + '&subid3=' + media_id
+
+ # Appricot Ads特別処理
+ elif int(result_method) == 9:
+ url += '&sub1=' + sid + '&sub6=' + ad_id + '&sub7=' + client_id + '&media_id=' + media_id
+
+ # Singular特別処理
+ elif int(result_method) == 10:
+ url += '&cl=' + sid + '&ad_id=' + ad_id + '&client_id=' + client_id + '&media_id=' + media_id
+ else:
+ pass
+ #client_id独自処理削除
+ else:
+ # ソケット通信の時はパラメータはデフォ
+ if int(result_method) == 2:
+ url += '?sid=' + sid + '&ad_id=' + ad_id + '&client_id=' + client_id + '&media_id=' + media_id
+
+ # AppsFlyer特別処理
+ elif int(result_method) == 3:
+ url += '?af_siteid=' + media_id + '&pid=adleap_int&af_click_lookback=7d&af_sub4=' + sid + '&af_sub1=' + ad_id + '&af_sub2=' + client_id + '&af_sub3=' + media_id
+
+ #Adjust特別処理
+ elif int(result_method) == 4:
+ callback_url = 'https://api.rocket-a.com/service/result?sid=' + sid + '&ad_id=' + ad_id + '&client_id=' + client_id + '&media_id=' + media_id + '&device_id={idfa||gps_adid}&adjust_id={adid}'
+
+ if uid != '':
+ callback_url += '&media_uid=' + uid
+
+ encode_callback_url = urllib.parse.quote(callback_url, safe='')
+
+ url += '?install_callback=' + encode_callback_url
+
+ # CPE
+ if adjust_token != '':
+ encode_callback_url_add_token = urllib.parse.quote(callback_url + '&adjust_token=' + adjust_token, safe='')
+
+ url += '&event_callback_' + adjust_token + '=' + encode_callback_url_add_token
+
+ # Airbridge特別処理
+ elif int(result_method) == 5:
+ url += '?sid=' + sid + '&custom_ad_id=' + ad_id + '&custom_client_id=' + client_id + '&sub_id=' + media_id
+
+ # Tyrads特別処理
+ elif int(result_method) == 6:
+ url += '?sub1=' + sid + '&sub2=' + ad_id + '&sub3=' + client_id + '&media_id=' + media_id
+
+ # AyeT Studios特別処理
+ elif int(result_method) == 7:
+ url += '?custom_1=' + sid + '&custom_2=' + ad_id + '&custom_3=' + client_id + '&media_id=' + media_id
+
+ # Torox特別処理
+ elif int(result_method) == 8:
+ url += '?user_id=' + sid + '&subid1=' + ad_id + '&subid2=' + client_id + '&subid3=' + media_id
+
+ # Appricot Ads特別処理
+ elif int(result_method) == 9:
+ url += '?sub1=' + sid + '&sub6=' + ad_id + '&sub7=' + client_id + '&media_id=' + media_id
+
+ # Singular特別処理
+ elif int(result_method) == 10:
+ url += '?cl=' + sid + '&ad_id=' + ad_id + '&client_id=' + client_id + '&media_id=' + media_id
+
+ else:
+ #client_id独自処理削除
+ url += '?sid=' + sid + '&ad_id=' + ad_id + '&client_id=' + client_id + '&media_id=' + media_id
+
+ if uid != '' and int(result_method) != 4:
+ url += '&media_uid=' + uid
+ """
+ params = {
+ "sid": sid,
+ "ad_id": ad_id,
+ "client_id": client_id,
+ "media_id": media_id,
+ }
+
+ formatter = string.Formatter()
+ fields = [fname for _, fname, _, _ in formatter.parse(url) if fname]
+ missing = [f for f in fields if f not in params]
+ if missing:
+ params = {
+ "sid": sid,
+ "ad_id": ad_id,
+ "client_id": client_id,
+ "media_id": media_id,
+ "media_uid": uid,
+ }
+ formatter = string.Formatter()
+ fields = [fname for _, fname, _, _ in formatter.parse(url) if fname]
+ missing = [f for f in fields if f not in params]
+
+ if missing or uid == '':
+ error = 401
+ logging.info(f"■■■589■■■パラメータエラー")
+ else:
+ url = url.format(sid=sid, ad_id=ad_id, client_id=client_id, media_id=media_id, media_uid=uid)
+ else:
+ url = url.format(sid=sid, ad_id=ad_id, client_id=client_id, media_id=media_id)
+
+ logging.info(f"■■■595■■■{url}")
+
+ if error == 0:
+ return response.Response(
+ ctx,
+ status_code=302,
+ headers={"Location": url}
+ )
+
+
+ # エラーページへ遷移
+ error_page = 'https://api.rocket-a.com/service/error?error=' + str(error)
+ if error == 2:
+ error_page = 'https://api.rocket-a.com/service/device_error'
+
+
+ return response.Response(
+ ctx,
+ status_code=302,
+ headers={"Location": error_page}
+ )
+
+'''
+ # testdic = click_data._asdict()
+ # logging.info(testdic)
+ return response.Response(
+ ctx,
+ response_data=f"Helloa{dv_ios_other}",
+ headers={"Content-Type": "application/json"}
+ )
+'''
+
+
+