def importTmpProducts():
util.logNow("RUN : importTmpProducts()")
query = """
LOAD DATA INFILE '{0}' INTO TABLE {1} FIELDS TERMINATED BY '\\t' LINES TERMINATED BY '\\n' (id, category_1,category_2,category_3, name, price, discount, discount_percent, brand, url, url_source, delivery_period, is_available, image_1, image_2, image_3, image_4, image_5, image_zoom, created_at)
""".format(config.FILE_CSV_PATH, config.TABLE_TMP_PRODUCT)
mysql.executeNoneQuery(query)
def insertNewProducts():
util.logNow("RUN : insertNewProducts()")
query = """
INSERT IGNORE INTO {0}
SELECT * FROM {1}
""".format(config.TABLE_PRODUCT, config.TABLE_TMP_PRODUCT)
mysql.executeNoneQuery(query)
def updateProducts():
util.logNow("RUN : updateProducts()")
query = """
UPDATE {0} p
JOIN {1} tp ON tp.id = p.id
SET
p.price = tp.price,
p.discount = tp.discount,
p.discount_percent = tp.discount_percent
""".format(config.TABLE_PRODUCT, config.TABLE_TMP_PRODUCT)
mysql.executeNoneQuery(query)
def backupDeletedProducts():
util.logNow("RUN : backupDeletedProducts()")
query = """
INSERT INTO {0}
SELECT * FROM {1} WHERE is_deleted = 1
""".format(config.TABLE_DELETED_PRODUCT, config.TABLE_PRODUCT)
return mysql.executeNoneQuery(query)
def insertUserRcb(obj):
query = """
INSERT INTO user_rcb(site_id, monitor, time, user, deposit) values ({0}, '{1}', '{2}', '{3}', {4})
""".format(
obj["site_id"], obj["monitor"], obj["time"], obj["user"], obj["deposit"]
)
return mysql.executeNoneQuery(query)
def setIsDeleted():
util.logNow("RUN : setIsDeleted()")
query = """
UPDATE {0}
SET is_deleted = 1,
deleted_at = now()
WHERE is_deleted = 0 AND id NOT IN (SELECT id FROM {1})
""".format(config.TABLE_PRODUCT, config.TABLE_TMP_PRODUCT)
return mysql.executeNoneQuery(query)
def statsRcbDaily():
query = """
REPLACE INTO user_rcb_daily(date, site_id, monitor, count, deposit)
SELECT DATE(time), site_id, monitor, COUNT(*) as count, SUM(deposit) as deposit
FROM user_rcb
GROUP BY DATE(time), site_id, monitor
"""
return mysql.executeNoneQuery(query)
def setPaid(siteId):
query = """
UPDATE site_monitor SET is_paid = 1 WHERE site_id = {0}
""".format(siteId)
return mysql.executeNoneQuery(query)
def insertUnknowSite(url, monitor):
query = """
INSERT IGNORE INTO unknow_sites(url, monitor) values ('{0}', '{1}')
""".format(url, monitor)
return mysql.executeNoneQuery(query)
def insertSiteMonitor(obj, monitor):
query = """
INSERT IGNORE INTO site_monitor(site_id, monitor, ref_site_id, ref_site_url) values ({0}, '{1}', '{2}', '{3}')
""".format(obj['siteId'], monitor, obj['id'], obj['siteRCBUrl'])
return mysql.executeNoneQuery(query)
def removeDeletedProducts():
util.logNow("RUN : removeDeletedProducts()")
query = """
DELETE FROM {0} WHERE is_deleted = 1
""".format(config.TABLE_PRODUCT)
mysql.executeNoneQuery(query)
def setAvailable(id, status):
query = """
UPDATE {0} SET is_available = {1} WHERE id = '{2}'
""".format(config.TABLE_PRODUCT, status, id)
mysql.executeNoneQuery(query)
def insertSiteStat(obj):
query = """
INSERT INTO site_stats(site_id, total_account, active_account, total_deposit, total_withdraw, time) values ({0}, {1}, {2}, {3}, {4}, {5})
""".format(obj['site_id'], obj['total_account'], obj['active_account'], obj['total_deposit'], obj['total_withdraw'], obj['time'])
return mysql.executeNoneQuery(query)
def truncateTmpProducts():
util.logNow("RUN : truncateTmpProducts()")
query = "TRUNCATE TABLE {0}".format(config.TABLE_TMP_PRODUCT)
mysql.executeNoneQuery(query)
