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 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 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 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 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 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 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 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 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 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 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 `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 createTable(self):
query = '''CREATE TABLE IF NOT EXISTS sku_management(
id SERIAL PRIMARY KEY NOT NULL,
sku TEXT NOT NULL,
name TEXT NOT NULL,
link TEXT NOT NULL,
min_price INTEGER NOT NULL,
max_price INTEGER NOT NULL,
compete_price INTEGER NOT NULL,
special_price INTEGER NOT NULL,
state INTEGER NOT NULL,
repeat_time INTEGER NOT NULL,
created_at INTEGER NOT NULL,
updated_at INTEGER
);'''
DatabaseHelper.execute(query)
def createTable(self):
query = '''CREATE TABLE IF NOT EXISTS order_item(
id INT AUTO_INCREMENT primary key NOT NULL,
order_item_id BIGINT,
shop_id VARCHAR(100),
order_id INTEGER,
name VARCHAR(300),
seller_sku VARCHAR(300),
shop_sku VARCHAR(300),
shipping_type VARCHAR(100),
item_price DECIMAL(10,2),
paid_price DECIMAL(10,2),
currency VARCHAR(50),
wallet_credit INTEGER,
tax_amount INTEGER,
shipping_amount DECIMAL(10,2),
shipping_service_cost DECIMAL(10,2),
voucher_amount DECIMAL(10,2),
voucher_code VARCHAR(100),
status VARCHAR(100),
shipment_provider VARCHAR(100),
is_digital INTEGER,
digital_delivery_info VARCHAR(300),
tracking_code VARCHAR(100),
tracking_code_pre VARCHAR(100),
reason VARCHAR(300),
reason_detail TEXT,
purchase_order_id VARCHAR(100),
purchase_order_number VARCHAR(100),
package_id VARCHAR(100),
promised_shipping_time VARCHAR(100),
extra_attributes VARCHAR(300),
shipping_provider_type VARCHAR(100),
created_at DATETIME,
updated_at DATETIME,
return_status VARCHAR(300),
product_main_image VARCHAR(500),
variation VARCHAR(300),
product_detail_url VARCHAR(500),
invoice_number VARCHAR(100),
user_id INTEGER,
earned DECIMAL(10,2) DEFAULT 0,
original_price DECIMAL(10,2) DEFAULT 0,
actual_paid_price DECIMAL(10,2) DEFAULT 0
);'''
DatabaseHelper.execute(query)
def createTable(self):
query = '''CREATE TABLE IF NOT EXISTS sku_management(
id INT AUTO_INCREMENT primary key NOT NULL,
sku VARCHAR(500) NOT NULL,
name VARCHAR(500) NOT NULL,
link VARCHAR(500) NOT NULL,
min_price INTEGER NOT NULL,
max_price INTEGER NOT NULL,
compete_price INTEGER NOT NULL,
special_price INTEGER NOT NULL,
state INTEGER NOT NULL,
repeat_time INTEGER ,
created_at INTEGER NOT NULL,
updated_at INTEGER,
user_id INTEGER NOT NULL
);'''
DatabaseHelper.execute(query)
def updateConstant(self, user, constant_key, value):
query = '''UPDATE constant SET value = '{}' WHERE constant_key = '{}' AND user_id = '{}'
'''.format(value, constant_key, user['id'])
try:
result, exception = DatabaseHelper.execute(query)
return exception
except Exception as ex:
return '''User: {}-{}, Get Order Constant exception {}'''.format(
user['username'], user['id'], str(ex))
def insertConstant(self, user, constant_key, value):
query = '''INSERT INTO constant(constant_key, value, user_id) VALUES ('{}', '{}', '{}')
'''.format(constant_key, value, user['id'])
try:
result, exception = DatabaseHelper.execute(query)
return exception
except Exception as ex:
return '''User: {}-{}, Insert Constant exception {}'''.format(
user['username'], user['id'], str(ex))
def setIncome(self, user, orderId, shopSku, income, actualPaidPrice):
query = ''' UPDATE order_item
SET earned = {}, actual_paid_price = {}
WHERE order_id = {} AND user_id = '{}' AND shop_sku = '{}'
'''.format(income, actualPaidPrice, orderId, user['id'], shopSku)
try:
result, exception = DatabaseHelper.execute(query)
return exception # Exception will be null if not have any exception
except Exception as ex:
return '''User {}-{}, Set-Order-Item-Income: Order-Item-Id {}, Exception: {} '''.format(user['username'], user['id'], itemItemId, str(ex))
def updateQuantityAndOrginalPrice(self, productId, quantity, orginalPrice):
query = '''UPDATE product
SET quantity = '{}', original_price = '{}'
WHERE id = '{}'
'''.format(quantity, orginalPrice, productId)
try:
isSuccess, exception = DatabaseHelper.execute(query)
return exception # Exception will be null if success
except Exception as ex:
return '''Update product got exception: {}'''.format(str(ex))
def updateOriginalPriceByShopSku(self, user, shopSku, orginalPrice):
query = '''UPDATE product
set original_price = '{}'
WHERE shop_sku = '{}' AND user_id = '{}'
'''.format(orginalPrice, shopSku, user['id'])
try:
result, exception = DatabaseHelper.execute(query)
return exception
except Exception as ex:
return '''Update product's price got exception: {}'''.format(str(ex))
def update(self, user, accountStatementId, income, updatedAt):
query = ''' UPDATE account_statement
SET income = {}, updated_at = '{}'
WHERE user_id = {} AND id = {}
'''.format(income, updatedAt, user['id'], accountStatementId)
try:
result, exception = DatabaseHelper.execute(query)
return exception
except Exception as ex:
return ''' User {}-{}, Update-Account-Statement: {} '''.format(
user['username'], user['id'], str(ex))