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 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 getSuperAdmin(self):
query = '''SELECT id, lazada_user_name, lazada_user_id, lazada_api_key
FROM t_user
WHERE lazada_user_id = '{}' '''.format(UserConfig.SUPER_ADMIN)
try:
conn = DatabaseHelper.getConnection()
cur = conn.cursor()
cur.execute(query)
row = cur.fetchone()
if not row:
conn.close()
return ExceptionUtils.error('''Dont have any super admins''')
user = {
'id': row[0],
'username': row[1], # Using lazada user name instead.
'lazada_user_name': row[1],
'lazada_user_id': row[2],
'lazada_api_key': row[3]
}
conn.close()
return user
except Exception as ex:
return ExceptionUtils.error('''Get super admin exception: {}'''.format(str(ex)))
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 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 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 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 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)
def getById(self, id):
try:
query = '''SELECT * from sku_management WHERE id = '{}' '''.format(
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):
try:
query = '''SELECT * FROM t_user '''
conn = DatabaseHelper.getConnection()
cur = conn.cursor()
cur.execute(query)
users = []
rows = cur.fetchall()
for row in rows:
users.append({
"id": row[0],
"username": row[1],
"password": row[2],
"lazada_user_name": row[4],
"lazada_user_id": row[5],
"lazada_api_key": row[6],
"role": "Admin" if row[9] == 1 else "User",
"certain_size": row[10]
})
conn.close()
return users
except Exception as ex:
return None
def getAll(self, user):
query = '''SELECT * from price_by_time WHERE user_id = '{}'
'''.format(user['id'])
try:
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],
"price_by_time": row[4],
"special_price": row[5]
})
conn.close()
return skus
except Exception as ex:
return ExceptionUtils.error(
'''User: {}-{}, get by time skus exception: {}'''.format(
user['username'], user['id'], str(ex)))
def getOrderItems(self, user, query):
try:
conn = DatabaseHelper.getConnection()
cur = conn.cursor()
cur.execute(query)
orderItems = []
rows = cur.fetchall()
for row in rows:
orderItems.append({
'id': row[0],
'order_item_id': row[1],
'shop_id': row[2],
'order_id': row[3],
'name': row[4],
'seller_sku': row[5],
'shop_sku': row[6],
'shipping_type': row[7],
'item_price': row[8],
'paid_price': row[9],
'currency': row[10],
'wallet_credit': row[11],
'tax_amount': row[12],
'shipping_amount': row[13],
'shipping_service_cost': row[14],
'voucher_amount': row[15],
'voucher_code': row[16],
'status': row[17],
'shipment_provider': row[18],
'is_digital': row[19],
'digital_delivery_info': row[20],
'tracking_code': row[21],
'tracking_code_pre': row[22],
'reason': row[23],
'reason_detail': row[24],
'purchase_order_id': row[25],
'purchase_order_number': row[26],
'package_id': row[27],
'promised_shipping_time': row[28],
'extra_attributes': row[29],
'shipping_provider_type': row[30],
'created_at': row[31],
'updated_at': row[32],
'return_status': row[33],
'product_main_image': row[34],
'variation': row[35],
'product_detail_url': row[36],
'invoice_number': row[37],
'earned': row[39], # row[38] is user_id, don't need it
'original_price': row[40],
'actual_paid_price': row[41]
})
conn.close()
return orderItems, None
except Exception as ex:
return None, '''User: {}-{}, Query: {}, Get-Order-Item-By-Order-Item-Id: {}'''.format(user['username'], user['id'], query, str(ex))
def isOrderExist(self, user, orderId):
query = '''SELECT id FROM `order` WHERE order_id = '{}' AND user_id = '{}' '''.format(orderId, user['id'])
try:
conn = DatabaseHelper.getConnection()
cur = conn.cursor()
cur.execute(query)
order = cur.fetchone()
result = False if not order else True;
conn.close()
return result, None
except Exception as ex:
return False, '''User: {}-{}, Check-Order-Exist: {}'''.format(user['username'], user['id'], str(ex))
def getCertainSize(self, id):
query = ''' SELECT certain_size FROM t_user WHERE id = '{}' '''.format(id)
conn = DatabaseHelper.getConnection()
cur = conn.cursor()
cur.execute(query)
count = 0
row = cur.fetchone()
count = row[0];
conn.close()
return count
def checkExistSku(self, sku):
query = ''' SELECT count(*) FROM sku_management WHERE sku = '{}' '''.format(
StringUtils.toString(sku['sku']))
conn = DatabaseHelper.getConnection()
cur = conn.cursor()
cur.execute(query)
count = 0
row = cur.fetchone()
count = row[0]
conn.close()
return count
def getAddedSize(self, id):
query = ''' SELECT count(*) FROM sku_management as sku, t_user as user WHERE user.id = sku.user_id AND user.id = '{}' '''.format(
id)
conn = DatabaseHelper.getConnection()
cur = conn.cursor()
cur.execute(query)
count = 0
row = cur.fetchone()
count = row[0]
conn.close()
return count
def isConstantExist(self, user, constant_key):
query = '''SELECT * FROM constant WHERE user_id = '{}' and constant_key = '{}'
'''.format(user['id'], constant_key)
try:
conn = DatabaseHelper.getConnection()
cur = conn.cursor()
cur.execute(query)
constant = cur.fetchone()
result = False if not constant else True
conn.close()
return result, None
except Exception as ex:
return False, '''User: {}-{}, Insert Constant exception {}'''.format(
user['username'], user['id'], str(ex))
def isProductExist(self, user, shopSku):
query = '''SELECT id
FROM product
WHERE shop_sku = '{}' AND user_id = '{}'
'''.format(shopSku, user['id'])
try:
conn = DatabaseHelper.getConnection()
cur = conn.cursor()
cur.execute(query)
product = cur.fetchone()
result = False if not product else True;
conn.close()
return result, None
except Exception as ex:
errorString = '''User: {}-{}, Check-Product-Exist exception: {}'''.format(user['username'], user['id'], str(ex))
return False, errorString
def getMaxUpdatedAt(self, user):
query = '''SELECT MAX(updated_at)
FROM `order`
WHERE user_id = {} '''.format(user['id'])
try:
conn = DatabaseHelper.getConnection()
cur = conn.cursor()
cur.execute(query)
result = cur.fetchone()
maxUpdatedAt = result[0]
conn.close()
if (maxUpdatedAt == None):
return LazadaApiHelper.getFixedUpdatedAfterForCronJob(), None;
return maxUpdatedAt, None
except Exception as ex:
return None, '''User: {}-{}, Get-Max-Updated-At: {}'''.format(user['username'], user['id'], str(ex))
def update(self, user, orderItem):
query = '''UPDATE order_item
SET shop_id = %s, name = %s, seller_sku = %s, shop_sku = %s,
shipping_type = %s, item_price = %s, paid_price = %s,
currency = %s, wallet_credit = %s, tax_amount = %s,
shipping_service_cost = %s, shipping_amount = %s,
voucher_amount = %s, voucher_code = %s, status = %s,
shipment_provider = %s, is_digital = %s,
digital_delivery_info = %s, tracking_code = %s,
tracking_code_pre = %s, reason = %s, reason_detail = %s,
purchase_order_id = %s, purchase_order_number = %s,
package_id = %s, promised_shipping_time = %s,
extra_attributes = %s, shipping_provider_type = %s,
created_at = %s, updated_at = %s, return_status = %s,
product_main_image = %s, variation = %s,
product_detail_url = %s, invoice_number = %s
WHERE user_id = %s AND order_item_id = %s'''
conn = DatabaseHelper.getConnection()
cur = conn.cursor()
try:
cur.execute(query, (orderItem['shop_id'], orderItem['name'], orderItem['seller_sku'],
orderItem['shop_sku'], orderItem['shipping_type'],
orderItem['item_price'], orderItem['paid_price'],
orderItem['currency'], orderItem['wallet_credit'],
orderItem['tax_amount'], orderItem['shipping_service_cost'],
orderItem['shipping_amount'], orderItem['voucher_amount'],
orderItem['voucher_code'], orderItem['status'],
orderItem['shipment_provider'], orderItem['is_digital'],
orderItem['digital_delivery_info'], orderItem['tracking_code'],
orderItem['tracking_code_pre'], orderItem['reason'],
orderItem['reason_detail'], orderItem['purchase_order_id'],
orderItem['purchase_order_number'], orderItem['package_id'],
orderItem['promised_shipping_time'], orderItem['extra_attributes'],
orderItem['shipping_provider_type'], orderItem['created_at'],
orderItem['updated_at'], orderItem['return_status'],
orderItem['product_main_image'], orderItem['variation'],
orderItem['product_detail_url'], orderItem['invoice_number'],
user['id'], orderItem['order_item_id']))
conn.commit()
conn.close()
return None, None
except Exception as ex:
conn.rollback()
conn.close()
return None, '''User: {}-{}, Update-Order-Item: {}, Query: {}'''.format(user['username'], user['id'], str(ex), query)
def getOrderByOrderNumber(self, user, orderNumber):
query = '''SELECT *
FROM `order`
WHERE user_id = '{}' AND order_number = '{}'
'''.format(user['id'], orderNumber)
try:
conn = DatabaseHelper.getConnection()
cur = conn.cursor()
cur.execute(query)
row = cur.fetchone()
if not row:
conn.close()
return None, '''User: {}-{}, Get Order: {} is not found'''.format(user['username'], user['id'], orderNumber)
order = {
"id": row[0],
"order_id": row[1],
"customer_first_name": row[2],
"customer_lastName": row[3],
"order_number": row[4],
"payment_method": row[5],
"remarks": row[6],
"delivery_info": row[7],
"price": row[8],
"gift_option": row[9],
"gift_message": row[10],
"voucher_code": row[11],
"created_at": row[12],
"updated_at": row[13],
"address_billing": row[14],
"address_shipping": row[15],
"national_registration_number": row[16],
"items_count": row[17],
"promised_shipping_times": row[18],
"extra_attributes": row[19],
"statuses": row[20],
"voucher": row[21],
"shipping_fee": row[22],
"calculated": row[24], # row[23] is user_id, dont need it.
"account_statement_id": row[25]
}
conn.close()
return order, None
except Exception as ex:
return None, '''User: {}-{}, Order-Number: {}, Get-Order-By-Order-Number exception {}'''.format(user['username'], user['id'], orderNumber, str(ex))
def insert(self, user, orderNumber, accountStatementId, reason, createdAt):
query = '''INSERT INTO `account_statement_exception`(order_number, reason,
created_at, account_statement_id, user_id)
VALUES (%s, %s, %s, %s, %s)'''
conn = DatabaseHelper.getConnection()
cur = conn.cursor()
try:
cur.execute(query, (orderNumber, reason, createdAt,
accountStatementId, user['id']))
conn.commit()
conn.close()
return None
except Exception as ex:
conn.rollback()
conn.close()
return '''User: {}-{}, Insert-Account-Statement-Exception: {}'''.format(
user['username'], user['id'], str(ex))
def insert(self, user, shop):
query = '''INSERT INTO `shop`(name, email, api_key, status, user_id, created_at)
VALUES (%s, %s, %s, %s, %s, %s)'''
conn = DatabaseHelper.getConnection()
cur = conn.cursor()
try:
cur.execute(
query,
(shop['name'], shop['email'], shop['api_key'],
ShopConfig.STATE_PROCESSING, user['id'], shop['created_at']))
conn.commit()
conn.close()
return None, None
except Exception as ex:
conn.rollback()
conn.close()
return None, '''User: {}-{}, Insert-Shop: {}'''.format(
user['username'], user['id'], str(ex))
def getTopSellingProducts(self, user):
query = '''SELECT * FROM product INNER JOIN
(SELECT shop_sku, COUNT(shop_sku) AS soluong FROM order_item
GROUP BY shop_sku) top_order
ON product.shop_sku = top_order.shop_sku
WHERE product.user_id = {}
ORDER BY top_order.soluong DESC
LIMIT 100
'''.format(user['id'])
try:
conn = DatabaseHelper.getConnection()
cur = conn.cursor()
cur.execute(query)
products = []
rows = cur.fetchall()
for row in rows:
products.append({
"id": row[0],
"name": row[1],
"url": row[2],
"status": row[3],
"quantity": row[4],
"available_quantity": row[5],
"seller_sku": row[6],
"shop_sku": row[7],
"original_price": row[8],
"special_price": row[9],
"image": row[10],
"width": row[11],
"height": row[12],
"weight": row[13],
"brand": row[14],
"model": row[15],
"primary_category": row[16],
"spu_id": row[17],
"sold": row[20] # Sold Quantity
})
conn.close()
return (products, None)
except Exception as ex:
return (None, '''Search products exception: {}'''.format(str(ex)))
def getProductByShopSku(self, user, shopSku):
query = '''SELECT *
FROM product
WHERE user_id = '{}' and shop_sku = '{}'
'''.format(user['id'], shopSku)
try:
conn = DatabaseHelper.getConnection()
cur = conn.cursor()
cur.execute(query)
row = cur.fetchone()
if not row:
conn.close()
errorMessage = '''User: {}-{}, Get-Product-By-SKU: {} is not found'''.format(user['username'], user['id'], shopSku)
return None, errorMessage
product = {
"id": row[0],
"name": row[1],
"url": row[2],
"status": row[3],
"quantity": row[4],
"available_quantity": row[5],
"seller_sku": row[6],
"shop_sku": row[7],
"original_price": row[8],
"special_price": row[9],
"image": row[10],
"width": row[11],
"height": row[12],
"weight": row[13],
"brand": row[14],
"model": row[15],
"primary_category": row[16],
"spu_id": row[17]
}
conn.close()
return product, None
except Exception as ex:
errorMessage = '''Get-Product-By-SKU {}, exception: {}'''.format(shopSku, str(ex))
return None, errorMessage
def getTotalEarningOfAccountStatement(self, user, accountStatementId):
query = '''SELECT SUM(order_item.earned) as total_earning
FROM `order`
INNER JOIN `order_item` ON order.order_id = order_item.order_id
WHERE order.user_id = {} AND order.account_statement_id = {}
'''.format(user['id'], accountStatementId)
try:
conn = DatabaseHelper.getConnection()
cur = conn.cursor()
cur.execute(query)
if (cur.rowcount <= 0):
conn.close()
return 0, None;
row = cur.fetchone()
totalEarning = row[0]
conn.close()
return totalEarning, None
except Exception as ex:
return 0, '''User: {}-{}, Get-Tatol-Earning-Of-Account-Statement: {} '''.format(user['username'], user['id'], str(ex))
def searchProduct(self, user, searchKey):
query = '''SELECT *
FROM product
WHERE (name LIKE '%{}%' OR seller_sku LIKE '%{}%'
OR shop_sku LIKE '%{}%' OR brand LIKE '%{}%'
OR model LIKE '%{}%') AND user_id = '{}'
LIMIT 20
'''.format(searchKey, searchKey, searchKey, searchKey, searchKey, user['id'])
try:
conn = DatabaseHelper.getConnection()
cur = conn.cursor()
cur.execute(query)
products = []
rows = cur.fetchall()
for row in rows:
products.append({
"id": row[0],
"name": row[1],
"url": row[2],
"status": row[3],
"quantity": row[4],
"available_quantity": row[5],
"seller_sku": row[6],
"shop_sku": row[7],
"original_price": row[8],
"special_price": row[9],
"image": row[10],
"width": row[11],
"height": row[12],
"weight": row[13],
"brand": row[14],
"model": row[15],
"primary_category": row[16],
"spu_id": row[17]
})
conn.close()
return (products, None)
except Exception as ex:
return (None, '''Search products exception: {}'''.format(str(ex)))
def getConstant(self, user, constant_key):
query = '''SELECT * FROM constant WHERE user_id = '{}' AND constant_key = '{}'
'''.format(user['id'], constant_key)
try:
conn = DatabaseHelper.getConnection()
cur = conn.cursor()
cur.execute(query)
row = cur.fetchone()
if not row:
conn.close()
return '''User: {}-{}, Get constant not found, constant_key = {}'''.format(
user['username'], user['id'], constant_key)
result = {'value': row[2]}
conn.close()
return result, None
except Exception as ex:
return None, '''User: {}-{}, Get Constant exception {}'''.format(
user['username'], user['id'], str(ex))