def createTable(self):
query = '''CREATE TABLE IF NOT EXISTS `order` (
id INT AUTO_INCREMENT primary key NOT NULL,
order_id BIGINT,
customer_first_name VARCHAR(100),
customer_lastName VARCHAR(100),
order_number BIGINT,
payment_method VARCHAR(100),
remarks VARCHAR(300),
delivery_info VARCHAR(300),
price DECIMAL(10,2),
gift_option BOOLEAN DEFAULT NULL,
gift_message VARCHAR(200),
voucher_code VARCHAR(100),
created_at DATETIME,
updated_at DATETIME,
address_billing JSON DEFAULT NULL,
address_shipping JSON DEFAULT NULL,
national_registration_number VARCHAR(300),
items_count INTEGER,
promised_shipping_times VARCHAR(300),
extra_attributes VARCHAR(300),
statuses JSON DEFAULT NULL,
voucher INTEGER,
shipping_fee DECIMAL(10,2),
user_id INTEGER,
calculated INTEGER DEFAULT 0,
account_statement_id INTEGER
);'''
DatabaseHelper.execute(query)
def update(self, sku):
query = '''UPDATE sku_management set min_price = '{}', max_price = '{}', compete_price = '{}', updated_at = '{}'
WHERE id = '{}' '''.format(sku['min_price'],
sku['max_price'],
sku['compete_price'],
sku['updated_at'], sku['id'])
DatabaseHelper.execute(query)
def deleteHistories(self, sku, millisecond):
try:
query = '''DELETE FROM sku_history WHERE sku = '{}' and created_at < {} '''.format(StringUtils.toString(sku['sku']), millisecond)
DatabaseHelper.execute(query)
return ExceptionUtils.success()
except Exception as ex:
return ExceptionUtils.error('''Delete history failed: {}'''.format(str(ex)))
def insert(self, user):
query = '''INSERT INTO t_user (user_name, password, token, lazada_user_name, lazada_user_id, lazada_api_key, created_at, updated_at)
VALUES ('{}', '{}', '{}', '{}', '{}', '{}', {}, 0)'''.format(
user['user_name'], user['password'], user['token'],
user['lazada_user_name'], user['lazada_user_id'],
user['lazada_api_key'], user['created_at'])
DatabaseHelper.execute(query)
def updateUserToken(self, user):
try:
query = '''UPDATE t_user set token = '{}' WHERE id = '{}' '''.format(user['token'], user['id'])
DatabaseHelper.execute(query)
return user
except Exception as ex:
print(ex)
return None
def updatePw(self, user, token):
try:
query = '''UPDATE t_user SET password = '******' WHERE token = '{}' '''.format(user['newpass'], token)
DatabaseHelper.execute(query)
return user;
except Exception as ex:
print(ex)
return None
def createTable(self):
query = '''CREATE TABLE IF NOT EXISTS constant(
id INT AUTO_INCREMENT primary key NOT NULL,
constant_key VARCHAR(50) NOT NULL,
value VARCHAR(100) ,
user_id INTEGER NOT NULL
);'''
DatabaseHelper.execute(query)
def insertHistory(self, history, sku, user):
try:
query = '''INSERT INTO sku_history(sku, enemy_json, user_id, status, created_at) VALUES ('{}', '{}', {}, {}, {}) '''.format(
sku['sku'], history['enemy_json'], user['id'], history['status'], history['created_at'])
DatabaseHelper.execute(query)
return ExceptionUtils.success()
except Exception as ex:
return ExceptionUtils.error('''Insert new history failed: {}'''.format(str(ex)))
def delete(self, sku, user):
query = '''DELETE from price_by_time where id = '{}' '''.format(
sku['id'])
try:
DatabaseHelper.execute(query)
return ExceptionUtils.success()
except Exception as ex:
return ExceptionUtils.error(
'''User: {}-{}, delete price by time: {}, exception: {}'''.
format(user['username'], user['id'], sku['id'], str(ex)))
def insert(self, sku):
query = '''INSERT INTO sku_management (sku, name, link, min_price, max_price,
compete_price, special_price, state, repeat_time, created_at, updated_at)
VALUES ('{}', '{}', '{}', {}, {}, {}, {}, {}, {}, {}, 0)'''.format(
StringUtils.toString(sku['sku']),
StringUtils.toString(sku['name']),
StringUtils.toString(sku['link']), sku['min_price'],
sku['max_price'], sku['compete_price'], sku['special_price'],
sku['state'], sku['repeat_time'], sku['created_at'])
DatabaseHelper.execute(query)
def createTable(self):
query = '''CREATE TABLE IF NOT EXISTS sku_history(
id INT AUTO_INCREMENT primary key NOT NULL,
sku VARCHAR(500) NOT NULL,
enemy_json VARCHAR(500) NOT NULL,
user_id INTEGER NOT NULL,
status INTEGER NOT NULL, -- Sate for indicated that whether AutoPriceWorker can update special price on Lazada.
created_at INTEGER NOT NULL
);'''
DatabaseHelper.execute(query)
def createTable(self):
query = '''CREATE TABLE IF NOT EXISTS `account_statement_exception` (
id BIGINT AUTO_INCREMENT primary key NOT NULL,
order_number BIGINT,
reason VARCHAR(300),
created_at DATETIME,
account_statement_id BIGINT NOT NULL,
user_id BIGINT NOT NULL
);'''
DatabaseHelper.execute(query)
def createTable(self):
query = '''CREATE TABLE IF NOT EXISTS `shop` (
id INT AUTO_INCREMENT primary key NOT NULL,
name VARCHAR(100) NOT NULL,
email VARCHAR(200) NOT NULL,
api_key VARCHAR(300) NOT NULL,
status VARCHAR(50) NOT NULL,
user_id INTEGER NOT NULL,
created_at DATETIME
);'''
DatabaseHelper.execute(query)
def createTable(self):
query = '''CREATE TABLE IF NOT EXISTS price_by_time(
id INT AUTO_INCREMENT primary key NOT NULL,
sku VARCHAR(100) NOT NULL,
name VARCHAR(300) ,
link VARCHAR(300) ,
price_by_time VARCHAR(300) ,
special_price INTEGER ,
user_id INTEGER NOT NULL
);'''
DatabaseHelper.execute(query)
def updateSpecialPrice(self, sku, user, newSpecialPrice):
query = '''UPDATE price_by_time
set special_price = '{}'
WHERE id = '{}'
'''.format(newSpecialPrice, sku['id'])
try:
DatabaseHelper.execute(query)
return ExceptionUtils.success()
except Exception as ex:
return ExceptionUtils.error(
'''User: {}-{}, update special price of sku: {}, exception: {}'''
.format(user['username'], user['id'], sku['id'], str(ex)))
def insert(self, user):
query = '''INSERT INTO t_user (user_name, password, token, lazada_user_name, lazada_user_id, lazada_api_key, created_at, updated_at, role, certain_size)
VALUES ('{}', '{}', 'temptoken', '{}', '{}', '{}', '{}', 0, '{}', '{}')'''.format(
StringUtils.toString(user['username']),
StringUtils.toString(user['password']),
StringUtils.toString(user['lazada_user_name']),
StringUtils.toString(user['lazada_user_id']),
StringUtils.toString(user['lazada_api_key']),
user['created_at'],
user['role'],
user['certain_size'])
DatabaseHelper.execute(query)
def update(self, sku):
# query = '''UPDATE sku_management set sku = '{}', name = '{}', link = '{}', min_price = {}, max_price = {},
# compete_price = {}, special_price = {}, state = {}, repeat_time = {}, updated_at = {}
# WHERE id = '{}' '''.format(
# sku['sku'], sku['name'], sku['link'], sku['min_price'], sku['max_price'],
# sku['compete_price'], sku['special_price'], sku['state'], sku['repeat_time'],
# sku['updated_at'], sku['id'])
query = '''UPDATE sku_management set sku = '{}', min_price = '{}', max_price='{}', compete_price='{}', repeat_time='{}', state='{}', link='{}', name='{}', updated_at='{}'
WHERE id = '{}' '''.format(
sku['sku'], sku['min_price'], sku['max_price'],
sku['compete_price'], sku['repeat_time'], sku['state'],
sku['link'], sku['name'], sku['updated_at'], sku['id'])
DatabaseHelper.execute(query)
def createTable(self):
query = '''CREATE TABLE IF NOT EXISTS `account_statement` (
id BIGINT AUTO_INCREMENT primary key NOT NULL,
excel_url VARCHAR(100),
start_date DATETIME,
end_date DATETIME,
sales_revenue DECIMAL(10,2),
income DECIMAL(10,2) DEFAULT 0,
created_at DATETIME,
updated_at DATETIME,
user_id BIGINT
);'''
DatabaseHelper.execute(query)
def createTable(self):
query = '''CREATE TABLE IF NOT EXISTS t_user(
id SERIAL PRIMARY KEY NOT NULL,
user_name TEXT NOT NULL,
password TEXT NOT NULL,
token TEXT NOT NULL,
lazada_user_name TEXT,
lazada_user_id TEXT,
lazada_api_key TEXT,
created_at INTEGER NOT NULL,
updated_at INTEGER
);'''
DatabaseHelper.execute(query)
def insert(self, sku, user):
query = '''INSERT INTO price_by_time(sku, name, link, price_by_time, special_price, user_id)
VALUES ('{}', '{}', '{}', '{}', '{}', '{}')'''.format(
StringUtils.toString(sku['sku']),
StringUtils.toString(sku['name']),
StringUtils.toString(sku['link']), sku['price_by_time'],
sku['special_price'], user['id'])
try:
DatabaseHelper.execute(query)
return ExceptionUtils.success()
except Exception as ex:
return ExceptionUtils.error(
'''User: {}-{}, Insert price by time exception: {}'''.format(
user['username'], user['id'], str(ex)))
def createTable(self):
query = '''CREATE TABLE IF NOT EXISTS t_user(
id INT AUTO_INCREMENT primary key NOT NULL,
user_name VARCHAR(200) NOT NULL,
password TEXT NOT NULL,
token TEXT NOT NULL,
lazada_user_name VARCHAR(200),
lazada_user_id VARCHAR(200),
lazada_api_key TEXT,
created_at INTEGER NOT NULL,
updated_at INTEGER,
role INTEGER,
certain_size INTEGER
);'''
DatabaseHelper.execute(query)
def updateProductWithLazadaProduct(self, user, product):
query = '''UPDATE product
set name = %s, url = %s, status = %s, seller_sku = %s,
image = %s, package_width = %s,
package_height = %s, package_weight = %s,
brand = %s, model = %s, primary_category = %s,
spu_id = %s, special_price = %s
WHERE shop_sku = %s '''
conn = DatabaseHelper.getConnection()
cur = conn.cursor()
try:
cur.execute(query, (product['name'], product['url'], product['status'],
product['seller_sku'], product['image'],
product['package_width'], product['package_height'],
product['package_weight'], product['brand'],
product['model'], product['primary_category'],
product['spu_id'], product['special_price'],
product['shop_sku']))
conn.commit()
conn.close()
return None
except Exception as ex:
conn.rollback()
conn.close()
return '''User: {}-{}, update Product exception: {}'''.format(user['username'], user['id'], str(ex))
def getAllHistory(self, user):
try:
query = '''SELECT * from sku_history WHERE user_id = '{}' ORDER BY created_at DESC '''.format(user['id'])
conn = DatabaseHelper.getConnection()
cur = conn.cursor()
cur.execute(query)
histories = []
rows = cur.fetchall()
if not rows:
conn.close()
return histories
for row in rows:
histories.append({
'id': row[0],
'sku': row[1],
'enemy_json': row[2],
'status': row[4]
})
conn.close()
return histories
except Exception as ex:
return ExceptionUtils.error('''Get history failed: {}'''.format(str(ex)))
def getOrderItemByAccountStatement(self, user, accountStatementId):
query = '''SELECT order_item.shop_sku, order.order_id, order_item.order_item_id, order.order_number,
order_item.name, order_item.seller_sku, order_item.product_main_image,
order_item.item_price, order_item.original_price, order_item.earned
FROM `order`
INNER JOIN `order_item` ON order.order_id = order_item.order_id
WHERE order.user_id = {} AND order.account_statement_id = {}
LIMIT 10
'''.format(user['id'], accountStatementId)
try:
conn = DatabaseHelper.getConnection()
cur = conn.cursor()
cur.execute(query)
orderItems = []
rows = cur.fetchall()
for row in rows:
orderItems.append({
"shop_sku": row[0],
"order_id": row[1],
"order_item_id": row[2],
"order_number": row[3],
"name": row[4],
"seller_sku": row[5],
"product_main_image": row[6],
"item_price": row[7],
"original_price": row[8],
"earned": row[9]
})
conn.close()
return orderItems, None
except Exception as ex:
return None, '''User: {}-{}, Get-Order-Item-By-Account-Statement: {} '''.format(user['username'], user['id'], str(ex))
def getActiveSku(self, user):
try:
query = '''SELECT * from sku_management WHERE state = 1 and user_id = {} ORDER BY id DESC LIMIT 100'''.format(
user['id'])
conn = DatabaseHelper.getConnection()
cur = conn.cursor()
cur.execute(query)
skus = []
rows = cur.fetchall()
for row in rows:
skus.append({
"id": row[0],
"sku": row[1],
"name": row[2],
"link": row[3],
"min_price": row[4],
"max_price": row[5],
"compete_price": row[6],
"special_price": row[7],
"state": row[8],
"repeat_time": row[9],
"created_at": row[10]
})
conn.close()
return skus
except Exception as ex:
print(ex)
return None
def getAll(self, user, accountStatementId):
query = ''' SELECT *
FROM account_statement_exception
WHERE user_id = {} AND account_statement_id = {}
LIMIT 5
'''.format(user['id'], accountStatementId)
try:
conn = DatabaseHelper.getConnection()
cur = conn.cursor()
cur.execute(query)
result = []
rows = cur.fetchall()
for row in rows:
result.append({
"id": row[0],
"order_number": row[1],
"reason": row[2],
"created_at": row[3]
})
conn.close()
return result, None
except Exception as ex:
return None, '''User: {}-{}, Get-Account-Statement-Exception: {}'''.format(
user['username'], user['id'], str(ex))
def getAll(self, user):
query = ''' SELECT * FROM `account_statement` WHERE user_id = {}
'''.format(user['id'])
try:
conn = DatabaseHelper.getConnection()
cur = conn.cursor()
cur.execute(query)
rows = cur.fetchall()
result = []
for row in rows:
result.append({
"id": row[0],
"excel_url": row[1],
"start_date": row[2],
"end_date": row[3],
"sales_revenue": row[4],
"income": row[5],
"created_at": row[6],
"updated_at": row[7]
})
conn.close()
return result, None
except Exception as ex:
return None, '''User: {}-{}, Get-Account-Statement: {}'''.format(
user['username'], user['id'], str(ex))
def getFirstAccountStatementForTest(self, user):
query = ''' SELECT *
FROM account_statement
WHERE user_id = {}
'''.format(user['id'])
try:
conn = DatabaseHelper.getConnection()
cur = conn.cursor()
cur.execute(query)
row = cur.fetchone()
if not row:
conn.close()
return '''User: {}-{}, Dont have any account statement data'''.format(
user['username'], user['id'])
result = {
"id": row[0],
"excel_url": row[1],
"start_date": row[2],
"end_date": row[3],
"sales_revenue": row[4],
"income": row[5],
"created_at": row[6],
"updated_at": row[7]
}
conn.close()
return result
except Exception as ex:
return '''User: {}-{}, Get-Account-Statement: {}'''.format(
user['username'], user['id'], str(ex))
def insert(self, user, order):
query = '''INSERT INTO `order`(order_id, customer_first_name,
customer_lastName, order_number, payment_method,
remarks, delivery_info, price, gift_option,
gift_message, voucher_code, created_at, updated_at,
address_billing, address_shipping, national_registration_number,
items_count, promised_shipping_times, extra_attributes,
statuses, voucher, shipping_fee, user_id)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,
%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)'''
conn = DatabaseHelper.getConnection()
cur = conn.cursor()
try:
cur.execute(query, (order['order_id'], order['customer_first_name'],
order['customer_lastName'],order['order_number'],
order['payment_method'], order['remarks'],
order['delivery_info'], order['price'],
order['gift_option'], order['gift_message'],
order['voucher_code'], order['created_at'],
order['updated_at'], order['address_billing'],
order['address_shipping'], order['national_registration_number'],
order['items_count'], order['promised_shipping_times'],
order['extra_attributes'], order['statuses'],
order['voucher'], order['shipping_fee'], user['id']))
conn.commit()
conn.close()
return None, None
except Exception as ex:
conn.rollback()
conn.close()
return None, '''User: {}-{}, Insert-Order: {}'''.format(user['username'], user['id'], str(ex))
def updateOrder(self, user, order):
query = '''UPDATE `order`
SET customer_first_name = %s, customer_lastName = %s,
payment_method = %s, remarks = %s, delivery_info = %s,
price = %s, gift_option = %s, gift_message = %s,
voucher_code = %s, created_at = %s, updated_at = %s,
address_billing = %s, address_shipping = %s,
national_registration_number = %s, items_count = %s,
promised_shipping_times = %s, extra_attributes = %s,
statuses = %s, voucher = %s, shipping_fee = %s
WHERE order_id = %s AND user_id = %s'''
conn = DatabaseHelper.getConnection()
cur = conn.cursor()
try:
cur.execute(query, (order['customer_first_name'], order['customer_lastName'],
order['payment_method'], order['remarks'], order['delivery_info'],
order['price'], order['gift_option'], order['gift_message'],
order['voucher_code'], order['created_at'], order['updated_at'],
order['address_billing'], order['address_shipping'],
order['national_registration_number'], order['items_count'],
order['promised_shipping_times'], order['extra_attributes'],
order['statuses'], order['voucher'], order['shipping_fee'],
order['order_id'], user['id']))
conn.commit()
conn.close()
return None, None
except Exception as ex:
conn.rollback()
conn.close()
return None, '''User: {}-{}, Update-Order: {}, Query: {}'''.format(user['username'], user['id'], str(ex), query)
