def process_clubwpt_user_newly_updated_records():
config_value = get_config_value(db,
'last_imported_clubwpt_user_update_time')
def collection(connection, transaction):
""" Get newly added. """
if config_value is None:
return connection.execute(
text(
'SELECT * FROM club_player_detail WHERE exchange_time IS NOT NULL ORDER BY exchange_time ASC'
))
return connection.execute(text(
'SELECT * FROM club_player_detail WHERE exchange_time IS NOT NULL AND exchange_time >= :update_time ORDER BY exchange_time ASC'
),
update_time=config_value)
result_proxy = with_db_context(db, collection, 'orig_wpt')
rows = [{
'_orig_user_id': row['cust_id'],
'exchanged_at': row['exchange_time'],
'exchanged_user_id': row['exchanged_user_id']
} for row in result_proxy]
if rows:
new_config_value = rows[-1]['exchanged_at']
def sync_collection(connection, transaction):
""" Sync newly added. """
where = BIClubWPTUser.__table__.c.orig_user_id == bindparam(
'_orig_user_id')
values = {
'exchanged_at': bindparam('exchanged_at'),
'exchanged_user_id': bindparam('exchanged_user_id')
}
try:
connection.execute(
BIClubWPTUser.__table__.update().where(where).values(
values), rows)
set_config_value(connection,
'last_imported_clubwpt_user_update_time',
new_config_value)
except:
print(
'process_clubwpt_user_newly_updated_records transaction.rollback()'
)
transaction.rollback()
raise
else:
print(
'process_clubwpt_user_newly_updated_records transaction.commit()'
)
transaction.commit()
return
with_db_context(db, sync_collection)
return
def process_clubwpt_user_newly_added_records():
config_value = get_config_value(db, 'last_imported_clubwpt_user_add_time')
if config_value is None:
def collection(connection, transaction):
""" Get newly added. """
return connection.execute(text('SELECT * FROM club_player_detail'))
result_proxy = with_db_context(db, collection, 'orig_wpt')
rows = [{
'orig_user_id': row['cust_id'],
'email': parse_email_or_username(row['email']),
'orig_email': row['email'],
'username': parse_email_or_username(row['username']),
'orig_username': row['username'],
'gold_balance': row['gold_balance'],
'exchanged_at': row['exchange_time'],
'exchanged_user_id': row['exchanged_user_id']
} for row in result_proxy]
if rows:
def sync_collection(connection, transaction):
""" Sync newly added. """
try:
connection.execute(BIClubWPTUser.__table__.insert(), rows)
set_config_value(
connection, 'last_imported_clubwpt_user_add_time',
current_time().format('YYYY-MM-DD HH:mm:ss'))
except:
print(
'process_clubwpt_user_newly_added_records transaction.rollback()'
)
transaction.rollback()
raise
else:
print(
'process_clubwpt_user_newly_added_records transaction.commit()'
)
transaction.commit()
return
with_db_context(db, sync_collection)
return
def process_user_dollar_paid_total():
config_value = get_config_value(
db, 'last_imported_user_bill_dollar_paid_add_time')
def collection(connection, transaction):
""" Get newly added. """
if config_value is None:
return connection.execute(
text("""
SELECT user_id,
COUNT(*) AS dollar_paid_count,
ROUND(SUM(currency_amount), 2) AS dollar_paid_amount,
MAX(created_at) AS max_createtime
FROM bi_user_bill
WHERE currency_type = 'Dollar'
GROUP BY user_id
ORDER BY max_createtime ASC
"""))
return connection.execute(text("""
SELECT user_id,
COUNT(*) AS dollar_paid_count,
ROUND(SUM(currency_amount), 2) AS dollar_paid_amount,
MAX(created_at) AS max_createtime
FROM bi_user_bill
WHERE currency_type = 'Dollar'
AND user_id IN (SELECT DISTINCT user_id
FROM bi_user_bill
WHERE created_at >= :add_time)
GROUP BY user_id
ORDER BY max_createtime ASC
"""),
add_time=config_value)
result_proxy = with_db_context(db, collection)
rows = [{
'_user_id': row['user_id'],
'dollar_paid_count': row['dollar_paid_count'],
'dollar_paid_amount': row['dollar_paid_amount'],
'max_createtime': row['max_createtime']
} for row in result_proxy]
if rows:
new_config_value = rows[-1]['max_createtime']
def sync_collection(connection, transaction):
""" Sync newly added. """
where = BIUser.__table__.c.user_id == bindparam('_user_id')
values = {
'dollar_paid_count': bindparam('dollar_paid_count'),
'dollar_paid_amount': bindparam('dollar_paid_amount'),
}
try:
connection.execute(
BIUser.__table__.update().where(where).values(values),
rows)
set_config_value(
connection, 'last_imported_user_bill_dollar_paid_add_time',
new_config_value)
except:
print('process_user_dollar_paid_total transaction.rollback()')
transaction.rollback()
raise
else:
print('process_user_dollar_paid_total transaction.commit()')
transaction.commit()
return
with_db_context(db, sync_collection)
return
def process_user_gold_currency_newly_added_records():
config_value = get_config_value(
db, 'last_imported_user_gold_currency_add_time')
def collection(connection, transaction):
""" Get newly added. """
if config_value is None:
return connection.execute(
text("""
SELECT id,
recdate AS created_at,
username AS og_account,
gameid AS game_id,
producttype AS transaction_type,
gamecoin AS transaction_amount,
userip AS ip,
( gamecoin + coin ) AS balance
FROM powergamecoin_detail
WHERE username IS NOT NULL
ORDER BY recdate ASC
"""))
return connection.execute(text("""
SELECT id,
recdate AS created_at,
username AS og_account,
gameid AS game_id,
producttype AS transaction_type,
gamecoin AS transaction_amount,
userip AS ip,
( gamecoin + coin ) AS balance
FROM powergamecoin_detail
WHERE recdate >= :recdate AND username IS NOT NULL
ORDER BY recdate ASC
"""),
recdate=config_value)
result_proxy = with_db_context(db, collection, 'orig_wpt_ods')
#########
existed_data = []
if config_value is not None:
orig_result_proxy = []
newly_orig_ids = []
for row in result_proxy:
orig_result_proxy.append(row)
newly_orig_ids.append(row['id'])
def existed_collection(connection, transaction):
return connection.execute(text(
"SELECT orig_id FROM bi_user_currency WHERE currency_type = 'Gold' AND orig_id IN :newly_orig_ids"
),
newly_orig_ids=tuple(newly_orig_ids))
existed_result_proxy = with_db_context(db, existed_collection)
existed_data = [row['orig_id'] for row in existed_result_proxy]
print('process_user_gold_currency_newly_added_records existed data: ' +
str(len(existed_data)))
#########
proxy = orig_result_proxy if config_value is not None else result_proxy
rows = [{
'orig_id': row['id'],
'user_id': -1,
'og_account': row['og_account'],
'game_id': row['game_id'],
'currency_type': 'Gold',
'transaction_type': row['transaction_type'],
'transaction_amount': row['transaction_amount'],
'ip': row['ip'],
'balance': row['balance'],
'created_at': row['created_at']
} for row in proxy if row['id'] not in existed_data]
print('process_user_gold_currency_newly_added_records new data: ' +
str(len(rows)))
if rows:
new_config_value = rows[-1]['created_at']
def sync_collection(connection, transaction):
""" Sync newly added. """
try:
connection.execute(BIUserCurrency.__table__.insert(), rows)
set_config_value(connection,
'last_imported_user_gold_currency_add_time',
new_config_value)
except:
print(
'process_user_gold_currency_newly_added_records transaction.rollback()'
)
transaction.rollback()
raise
else:
print(
'process_user_gold_currency_newly_added_records transaction.commit()'
)
transaction.commit()
return
with_db_context(db, sync_collection)
else:
print('process_user_gold_currency_newly_added_records no data')
return
def process_user_mall_bill_newly_added_records():
config_value = get_config_value(db,
'last_imported_user_mall_bill_order_id')
def collection(connection, transaction):
""" Get newly added. """
if config_value is None:
return connection.execute(
text("""
SELECT o.OrderId,
o.UserId,
o.GameId,
o.PlatformId,
o.Descr,
c.CurrencyCode,
c.CurrencyName,
o.TotalPrice,
p.ParentId,
g.GoodsName,
p.ProductName,
op.GoodsId,
op.ProductId,
op.Quantity,
o.CDate,
o.UDate,
(CASE WHEN p.ParentId IS NULL THEN p.ProductName ELSE (SELECT ProductName FROM Mall_tProduct WHERE Id = p.ParentId) END) AS Category
FROM Mall_tOrder o
LEFT JOIN Mall_tOrderProductLog op
ON op.OrderId = o.OrderId
LEFT JOIN Mall_tProduct p
ON op.ProductId = p.Id
LEFT JOIN Mall_tCurrency c
ON o.CurrencyCode = c.CurrencyCode
LEFT JOIN Mall_tPlatform s
ON s.PlatformId = o.PlatformId
LEFT JOIN Mall_tGoods g
ON g.Id = op.GoodsId
WHERE o.OrderStatus != 1
AND o.OrderStatus != 41
AND o.PaymentMode = 1
ORDER BY o.OrderId ASC
"""))
return connection.execute(text("""
SELECT o.OrderId,
o.UserId,
o.GameId,
o.PlatformId,
o.Descr,
c.CurrencyCode,
c.CurrencyName,
o.TotalPrice,
p.ParentId,
g.GoodsName,
p.ProductName,
op.GoodsId,
op.ProductId,
op.Quantity,
o.CDate,
o.UDate,
(CASE WHEN p.ParentId IS NULL THEN p.ProductName ELSE (SELECT ProductName FROM Mall_tProduct WHERE Id = p.ParentId) END) AS Category
FROM Mall_tOrder o
LEFT JOIN Mall_tOrderProductLog op
ON op.OrderId = o.OrderId
LEFT JOIN Mall_tProduct p
ON op.ProductId = p.Id
LEFT JOIN Mall_tCurrency c
ON o.CurrencyCode = c.CurrencyCode
LEFT JOIN Mall_tPlatform s
ON s.PlatformId = o.PlatformId
LEFT JOIN Mall_tGoods g
ON g.Id = op.GoodsId
WHERE o.OrderStatus != 1
AND o.OrderStatus != 41
AND o.PaymentMode = 1
AND o.OrderId > :order_id
ORDER BY o.OrderId ASC
"""),
order_id=config_value)
result_proxy = with_db_context(db, collection, 'orig_wpt_mall')
#########
orig_result_proxy = []
for row in result_proxy:
orig_result_proxy.append(row)
def existed_collection(connection, transaction):
return connection.execute(
text(
"SELECT orig_id FROM bi_user_bill WHERE orig_db = 'WPT_MALL'"))
existed_result_proxy = with_db_context(db, existed_collection)
existed_data = [row['orig_id'] for row in existed_result_proxy]
#########
rows = [{
'orig_id':
row['OrderId'],
'orig_db':
'WPT_MALL',
'user_id':
row['UserId'],
'game_id':
row['GameId'],
'platform':
parse_user_mall_platform(row['PlatformId'], row['Descr']),
'platform_orig':
row['PlatformId'],
'currency_type':
row['CurrencyName'],
'currency_type_orig':
row['CurrencyCode'],
'currency_amount':
row['TotalPrice'],
'category':
row['Category'],
'category_orig':
row['ParentId'],
'product':
row['ProductName'],
'product_orig':
row['ProductId'],
'goods':
row['GoodsName'],
'goods_orig':
row['GoodsId'],
'quantity':
row['Quantity'],
'created_at':
row['CDate']
} for row in orig_result_proxy if row['OrderId'] not in existed_data]
if rows:
new_config_value = rows[-1]['orig_id']
def sync_collection(connection, transaction):
""" Sync newly added. """
try:
connection.execute(BIUserBill.__table__.insert(), rows)
set_config_value(connection,
'last_imported_user_mall_bill_order_id',
new_config_value)
except:
print(
'process_user_mall_bill_newly_added_records transaction.rollback()'
)
transaction.rollback()
raise
else:
print(
'process_user_mall_bill_newly_added_records transaction.commit()'
)
transaction.commit()
return
with_db_context(db, sync_collection)
return