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))