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