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)