def products_edit(form):
app_id = int(form.app_id.data)
product_id = int(form.product_id.data)
product_name = set_default(form.product_name.data)
product_detail = set_default(form.product_detail.data)
product_price = set_default(form.product_price.data)
inapp_id = set_default(form.inapp_id.data)
service_platform = int(form.service_platform.data)
currency = int(form.currency.data)
status = int(form.status.data)
con, cur = sqlrelay_cursor()
cur.execute("""
UPDATE ms_app_product SET
product_name = '%s'
, product_detail = '%s'
, product_price = '%s'
, inapp_id = '%s'
, service_platform = %d
, currency = %d
, status = %d
WHERE
product_id = %d AND app_id = %d
""" % (product_name, product_detail, product_price, inapp_id, service_platform, currency, status, product_id, app_id))
sqlrelay_close(cur, con)
def payments_detail(app_id, payment_id):
con, cur = sqlrelay_cursor()
cur.execute("""
SELECT
app_id
, member_id
, product_id
, payment_id
, service_platform
, product_price
, inapp_order_id
, inapp_package_name
, inapp_product_sku
, inapp_purchase_time
, inapp_purchase_state
, inapp_purchase_token
, inapp_developer_payload
, inapp_signature
, inapp_appstore_name
, inapp_receipt
, status
, DATE_FORMAT(reg_date, '%%Y/%%m/%%d/ %%H:%%i:%%s')
FROM
ms_app_payment
WHERE
payment_id = %d AND app_id = %d
""" % (payment_id, app_id))
r = cur.fetchone()
sqlrelay_close(cur, con)
if not r:
return None
return _payment_make_result(r)
def products_new(form):
app_id = int(form.app_id.data)
product_name = set_default(form.product_name.data)
product_detail = set_default(form.product_detail.data)
product_price = set_default(form.product_price.data)
inapp_id = set_default(form.inapp_id.data)
service_platform = int(form.service_platform.data)
currency = int(form.currency.data)
status = int(form.status.data)
con, cur = sqlrelay_cursor()
cur.execute("""
INSERT INTO ms_app_product (
app_id
, product_name
, product_detail
, product_price
, inapp_id
, service_platform
, currency
, status
) VALUES (
%d
, '%s'
, '%s'
, '%s'
, '%s'
, %d
, %d
, %d
)""" % (app_id, product_name, product_detail, product_price, inapp_id, service_platform, currency, status))
sqlrelay_close(cur, con)
def products(app_id):
con, cur = sqlrelay_cursor()
cur.execute("""
SELECT
app_id
, product_id
, product_name
, product_detail
, product_price
, inapp_id
, service_platform
, currency
, status
, DATE_FORMAT(reg_date, '%%Y/%%m/%%d/ %%H:%%i:%%s')
FROM
ms_app_product
WHERE
app_id = %d
ORDER BY
product_id
""" % (app_id))
results = cur.fetchall()
sqlrelay_close(cur, con)
products = []
for r in results:
products.append(_product_make_result(r))
return products
def admins():
con, cur = sqlrelay_cursor()
cur.execute("""
SELECT
admin_id
, password
, access_apps
, DATE_FORMAT(reg_date, '%Y/%m/%d/ %H:%i:%s')
FROM
ms_admin
ORDER BY
reg_date
""")
results = cur.fetchall()
sqlrelay_close(cur, con)
admins = []
for r in results:
admins.append({
'admin_id': r[0],
'password': r[1],
'access_apps': r[2],
'reg_date': r[3],
})
return admins
def members_detail(app_id, member_id):
con, cur = sqlrelay_cursor()
cur.execute("""
SELECT
app_id
, member_id
, udid
, device_platform
, service_platform
, push_notification
, gcm_token
, facebook_id
, facebook_email
, status
, DATE_FORMAT(reg_date, '%%Y/%%m/%%d/ %%H:%%i:%%s')
, last_device_platform
, last_service_platform
, DATE_FORMAT(last_login_date, '%%Y/%%m/%%d/ %%H:%%i:%%s')
FROM
ms_member
WHERE
member_id = %d AND app_id = %d
""" % (member_id, app_id))
r = cur.fetchone()
sqlrelay_close(cur, con)
if not r:
return None
return _member_make_result(r)
def app(app_id):
con, cur = sqlrelay_cursor()
cur.execute(
"""
SELECT
app_name
, app_key
, app_secret
, facebook_app_id
, facebook_app_secret
, facebook_api_version
FROM
ms_app
WHERE
app_id = %d
"""
% (app_id)
)
r = cur.fetchone()
sqlrelay_close(cur, con)
if not r:
return None
r = cycle(r)
return {
"app_name": r.next(),
"app_key": r.next(),
"app_secret": r.next(),
"facebook_app_id": r.next(),
"facebook_app_secret": r.next(),
"facebook_api_version": r.next(),
}
def products_detail(app_id, product_id):
con, cur = sqlrelay_cursor()
cur.execute("""
SELECT
app_id
, product_id
, product_name
, product_detail
, product_price
, inapp_id
, service_platform
, currency
, status
, DATE_FORMAT(reg_date, '%%Y/%%m/%%d/ %%H:%%i:%%s')
FROM
ms_app_product
WHERE
product_id = %d AND app_id = %d
""" % (product_id, app_id))
r = cur.fetchone()
sqlrelay_close(cur, con)
if not r:
return None
return _product_make_result(r)
def members_history(app_id, member_id):
con, cur = sqlrelay_cursor()
# 시스템 접근
access = member_history_category(app_id, member_id, membership_pb2.HISTORY_MEMBER_ACCESS, cur)
# 결제정보
payment = member_history_category(app_id, member_id, membership_pb2.HISTORY_MEMBER_PAYMENT, cur)
# 푸시설정
push = member_history_category(app_id, member_id, membership_pb2.HISTORY_MEMBER_PUSH, cur)
sqlrelay_close(cur, con)
return access, payment, push
def push_notification(member_id):
# TODO: query member push notification with cache
con, cur = sqlrelay_cursor()
cur.execute("""
SELECT
push_notification
FROM
ms_member
WHERE
member_id = %d AND app_id = %d
""" % (member_id, app.APP_ID))
r = cur.fetchone()
sqlrelay_close(cur, con)
if not r:
return None
return True if r[0] == '1' else False
def avaliable_admin(admin_id, password):
admin = None
con, cur = sqlrelay_cursor()
cur.execute("""
SELECT
COUNT(0)
FROM
ms_admin
WHERE
admin_id = '%s' AND PASSWORD = PASSWORD('%s')
""" % (escape(from_utf8(admin_id)), escape(from_utf8(password))))
if int(cur.fetchone()[0]) == 1:
admin = admin_id
sqlrelay_close(cur, con)
return admin
def payments(app_id, page, member_id=None, product_id=None):
condition = 'app_id = %d' % (app_id)
if member_id:
condition += ' AND member_id = %d' % (member_id)
if product_id:
condition += ' AND product_id = %d' % (product_id)
con, cur = sqlrelay_cursor()
cur.execute('SELECT COUNT(0) FROM ms_app_payment WHERE %s' % (condition))
total = int(cur.fetchone()[0])
cur.execute("""
SELECT
app_id
, member_id
, product_id
, payment_id
, service_platform
, product_price
, inapp_order_id
, inapp_package_name
, inapp_product_sku
, inapp_purchase_time
, inapp_purchase_state
, inapp_purchase_token
, inapp_developer_payload
, inapp_signature
, inapp_appstore_name
, inapp_receipt
, status
, DATE_FORMAT(reg_date, '%%Y/%%m/%%d/ %%H:%%i:%%s')
FROM
ms_app_payment
WHERE
%s
ORDER BY
reg_date DESC
LIMIT
%d, %d
""" % (condition, (page*PER_PAGE), ((page+1)*PER_PAGE)))
results = cur.fetchall()
sqlrelay_close(cur, con)
payments = []
for r in results:
payments.append(_payment_make_result(r))
return total, payments
def member_id(udid, device_platform, service_platform):
con, cur = sqlrelay_cursor()
cur.execute("""
SELECT
member_id
FROM
ms_member
WHERE
app_id = %d AND
udid = '%s' AND
device_platform = %d AND
service_platform = %d
""" % (app.APP_ID, udid, device_platform, service_platform))
r = cur.fetchone()
sqlrelay_close(cur, con)
if not r:
return None
return r[0]
def push(gcm_sender_id, gcm_server_api_key, app_id, member_ids, message, time):
# /topics/global 메세지 등록
if member_ids == '/topics/global':
push_scheduler.add_date_job(app.tasks.push, time,
kwargs = dict(gcm_url = app.GCM_URL,
gcm_sender_id = gcm_sender_id,
gcm_server_api_key = gcm_server_api_key,
member_id = member_ids,
token = member_ids,
message = message))
else:
con, cur = sqlrelay_cursor()
cur.execute("""
SELECT
gcm_token
, member_id
FROM
ms_member
WHERE
app_id = %d AND
push_notification = 1 AND
status = 1 AND
member_id IN (%s)
""" % (app_id, member_ids))
results = cur.fetchall()
sqlrelay_close(cur, con)
# 사용자별 푸시 스케쥴러 등록
for r in results:
gcm_token = r[0]
member_id = r[1]
push_scheduler.add_date_job(app.tasks.push, time,
kwargs = dict(gcm_url = app.GCM_URL,
gcm_sender_id = gcm_sender_id,
gcm_server_api_key = gcm_server_api_key,
member_id = member_id,
token = gcm_token,
message = message))
def apps():
con, cur = sqlrelay_cursor()
cur.execute("""
SELECT
app_id
, app_key
, app_secret
, app_name
, support_android
, support_ios
, support_playstore
, support_appstore
, support_gameflier
, playstore_url
, appstore_url
, gameflier_url
, gcm_sender_id
, gcm_server_api_key
, gcm_config_path
, facebook_app_name
, facebook_app_id
, facebook_app_secret
, facebook_api_version
, status
, DATE_FORMAT(reg_date, '%Y/%m/%d/ %H:%i:%s')
FROM
ms_app
ORDER BY
app_id
""")
results = cur.fetchall()
sqlrelay_close(cur, con)
apps = []
for r in results:
apps.append(_app_make_result(r))
return apps
def apps_detail(app_id):
con, cur = sqlrelay_cursor()
cur.execute("""
SELECT
app_id
, app_key
, app_secret
, app_name
, support_android
, support_ios
, support_playstore
, support_appstore
, support_gameflier
, playstore_url
, appstore_url
, gameflier_url
, gcm_sender_id
, gcm_server_api_key
, gcm_config_path
, facebook_app_name
, facebook_app_id
, facebook_app_secret
, facebook_api_version
, status
, DATE_FORMAT(reg_date, '%%Y/%%m/%%d/ %%H:%%i:%%s')
FROM
ms_app
WHERE
app_id = %d
""" % (app_id))
r = cur.fetchone()
sqlrelay_close(cur, con)
if not r:
return None
return _app_make_result(r)
def load_app(force=False):
if app.APP_NAME and force == False: return
# load app
con, cur = sqlrelay_cursor()
cur.execute("""
SELECT
app_key
, app_secret
, app_name
, support_android
, support_ios
, support_playstore
, support_appstore
, support_gameflier
, playstore_url
, appstore_url
, gameflier_url
, gcm_sender_id
, gcm_server_api_key
, facebook_app_id
, facebook_app_name
, facebook_app_secret
, facebook_api_version
, status
FROM
ms_app
WHERE
app_id = %d
""" % (app.APP_ID))
result = cur.fetchone()
assert result
r = cycle(result)
# validate APP_KEY and APP_SECRET
assert app.APP_KEY == r.next()
assert app.APP_SECRET == r.next()
app.APP_NAME = r.next()
# validate information by support option
app.SUPPORT_ANDROID = get_support(r.next())
app.SUPPORT_IOS = get_support(r.next())
app.SUPPORT_PLAYSTORE = get_support(r.next())
app.SUPPORT_APPSTORE = get_support(r.next())
app.SUPPORT_GAMEFLIER = get_support(r.next())
app.PLAYSTORE_URL = r.next()
app.APPSTORE_URL = r.next()
app.GAMEFLIER_URL = r.next()
app.GCM_SENDER_ID = r.next()
app.GCM_SERVER_API_KEY = r.next()
app.FACEBOOK_APP_ID = r.next()
app.FACEBOOK_APP_NAME = r.next()
app.FACEBOOK_APP_SECRET = r.next()
app.FACEBOOK_API_VERSION = r.next()
# load product
cur.execute("""
SELECT
product_id
, product_name
, product_price
, inapp_id
, service_platform
, currency
FROM
ms_app_product
WHERE
app_id = %d AND status = %d
ORDER BY
service_platform
""" % (app.APP_ID, membership_pb2.PRODUCT_STATUS_ENABLE))
results = cur.fetchall()
sqlrelay_close(cur, con)
products = {
membership_pb2.SERVICE_PLATFORM_PLAYSTORE: [],
membership_pb2.SERVICE_PLATFORM_APPSTORE: [],
membership_pb2.SERVICE_PLATFORM_GAMEFLIER: [],
}
for r in results:
product_id = r[0]
product_name = r[1]
product_price = r[2]
inapp_id = r[3]
service_platform = int(r[4])
currency = int(r[5])
products[service_platform].append({
'product_id': product_id,
'product_name': product_name,
'product_price': product_price,
'inapp_id': inapp_id,
'currency': currency,
})
logger.info(products)
app.PRODUCTS = products
def apps_delete(app_id):
con, cur = sqlrelay_cursor()
cur.execute("DELETE FROM ms_app WHERE app_id = %d" % (app_id))
sqlrelay_close(cur, con)
def products_delete(app_id, product_id):
con, cur = sqlrelay_cursor()
cur.execute("DELETE FROM ms_app_product WHERE product_id = %d AND app_id = %d" % (product_id, app_id))
sqlrelay_close(cur, con)
