def get_item_barcode(): conn = None cursor = None item_barcode_list = [] try: # get mysql connection conn = mysql.get_connection() # get cursor cursor = conn.cursor() # sql sql = "SELECT store, sku_id, unit_qty, ware_id, o2o_sku_id FROM `tabItem Barcode`" db_result = cursor.execute(sql) for row in cursor.fetchall(): item_barcode_list.append({ 'store': row[0], 'sku_id': row[1], 'unit_qty': row[2], 'ware_id': row[3], 'o2o_sku_id': row[4] }) return item_barcode_list except Exception as e: logging.warn(e) finally: mysql.close(conn, cursor)
def get_uom(item_code, unit_qty): """ try: db_result = frappe.get_doc( 'UOM Conversion Detail', { 'parent': item_code, 'conversion_factor': unit_qty } ) if db_result: return db_result.get('uom') else: return '' except Exception as e: print e """ conn = None cursor = None try: conn = mysql.get_connection() cursor = conn.cursor() sql = "SELECT uom FROM `tabUOM Conversion Detail` WHERE parent = '%s' AND unit_qty = '%s'" % (item_code, unit_qty) db_result = cursor.execute(sql) if db_result: return cursor.fetchone() else: return '' except Exception as e: logging.warn(e) finally: mysql.close(conn, cursor)
def erp_data_item_barcode(): conn = None cursor = None try: #import pdb;pdb.set_trace() conn = mysql.get_connection() cursor = conn.cursor() sql = "SELECT sku_id,unit_qty,ware_id,o2o_sku_id,name FROM `tabItem Barcode`" db_result = cursor.execute(sql) j = 0 list = cursor.fetchall() for result in list: item.append({}) item[j]['item_code'] = result[0] item[j]['unit_qty'] = result[1] item[j]['ware_id'] = result[2] item[j]['o2o_sku_id'] = result[3] item[j]['name'] = result[4] if item[j]['ware_id'] and item[j]['o2o_sku_id']: item[j]['isExist'] = 1 #在后期函数调用中通过isExist值判断是否需要进入O2O查询 else: item[j]['isExist'] = 0 j = j + 1 except Exception as e: print (e) logging.warn(e) finally: mysql.close(conn, cursor)
def get_mail(mail_id): connection = get_connection() result = [] with connection.cursor() as cursor: cursor.execute("SELECT * FROM mails WHERE mail_id = %s" % mail_id) data = cursor.fetchall() if data[0]['attachment_id'] is None: cursor.execute("SELECT email_id FROM mail_to WHERE mail_id = %s" % mail_id) result.append({ 'from': data[0]['email_id'], 'to': cursor.fetchall()[0]['email_id'], 'subject': data[0]['subject'], 'message': data[0]['message'], 'file': None, 'send_at': data[0]['send_at'] }) else: sql = "SELECT file_path FROM attachments WHERE id = %s" % data[0]['attachment_id'] cursor.execute(sql) file_paths = cursor.fetchall() cursor.execute("SELECT email_id FROM mail_to WHERE mail_id = %s" % mail_id) result.append({ 'from': data[0]['email_id'], 'to': cursor.fetchall()[0]['email_id'], 'subject': data[0]['subject'], 'message': data[0]['message'], 'file': file_paths[0]['file_path'], 'send_at': data[0]['send_at'] }) connection.close() return result
def erp_data_item(erp_item): conn = None cursor = None try: conn = mysql.get_connection() cursor = conn.cursor() sql = "SELECT barcode,item_name,net_weight,packing_name,production_location,specification,item_group,shelf_life FROM `tabItem`WHERE item_code=%s"%erp_item['item_code'] db_result = cursor.execute(sql) try: item = cursor.fetchone() except Exception as e: logging.info(e) #erp_item['barcode'] = item[0] erp_item['item_name'] = item[1] erp_item['net_weight'] = item[2] erp_item['packing_name'] = item[3] erp_item['production_location'] = item[4] erp_item['spec_count'] = item[5] erp_item['item_group'] = item[6] erp_item['shelf_life'] = item[7] except Exception as e: logging.warning(e) finally: mysql.close(conn, cursor) return erp_item
def check_email_id(email_id): connection = get_connection() with connection.cursor() as cursor: sql = "SELECT * FROM users WHERE email_id = '%s'" % email_id cursor.execute(sql) if cursor.rowcount == 0: connection.close() return False else: connection.close() return True
def execute(): books = [] connection = mysql.get_connection() try: with connection.cursor() as cursor: sql = "SELECT * FROM Books;" cursor.execute(sql) books = cursor.fetchall() return Response(json.dumps(books), mimetype='application/json') finally: connection.close()
def get_item_price(store_id, item_code, uom): conn = None cursor = None try: conn = mysql.get_connection() cursor = conn.cursor() sql = "SELECT A.price_list_rate FROM `tabItem Price` A, `tabPrice List` B WHERE A.price_list = B.price_list_name" \ " AND B.store_id = '%s' AND A.item_code = '%s' AND A.unit = '%s'" % (store_id, item_code, uom) db_result = cursor.execute(sql) return cursor.fetchone()[0] except Exception as e: logging.warn(e) finally: mysql.close(conn, cursor)
def compare(erp_item): conn = mysql.get_connection() cursor = conn.cursor() sql = "SELECT uom FROM `tabUOM Conversion Detail` " \ "WHERE parent='%s' AND conversion_factor='%s'" % (erp_item['item_code'],erp_item['unit_qty']) db_result = cursor.execute(sql) uom = cursor.fetchone()[0] erp_item['uom'] = uom mysql.close(conn, cursor) erp_item = erp_data_conversion(erp_item) erp_item = erp_data_item(erp_item) erp_item['item_code'] = str(erp_item['item_code']) + '&' + erp_item['uom']#o2o中物料编码为item_code和uom dubbo_client = duduboo.DubboClient() #skuid_spec_data = dubbo_client.get_spec_rule_by_skuid([Long(1000210212)]) wareid_spec_data = dubbo_client.get_spec_rule_by_ware_id([Long(erp_item['ware_id'])]) #skuid_spec_data = skuid_spec_data1[0] if wareid_spec_data is not None: for key, value in dict_erp_o2o.items(): if hasattr(wareid_spec_data,key): if erp_item[value] is not None and getattr(wareid_spec_data,key) is not None: if erp_item[value] != getattr(wareid_spec_data,key): logging.info('%s=%s和%s=%s不相等' %(value,erp_item[value],key,getattr(wareid_spec_data,key))) #if erp_item['barcode'] is not None and wareid_spec_data.itemNum is not None: # if erp_item['barcode'] != wareid_spec_data.itemNum: # logging.info('barcode=%s和itemNum=%s不相等' %(erp_item['barcode'],wareid_spec_data.itemNum)) #if erp_item['item_name'] is not None and wareid_spec_data.title is not None: # if erp_item['item_name'] != wareid_spec_data.title: # logging.info('item_name=%s和title=%s不相等' % (erp_item['item_name'], wareid_spec_data.title)) #if erp_item['net_weight'] is not None and wareid_spec_data.brgew is not None: # if erp_item['net_weight'] != wareid_spec_data.brgew: # logging.info('net_weight=%s和brgew=%s不相等' % (erp_item['net_weight'], wareid_spec_data.brgew)) #if erp_item['packing_name'] is not None and wareid_spec_data.chine is not None: # if erp_item['packing_name'] != wareid_spec_data.chine: # logging.info('packing_name=%s和brgew=%s不相等' % (erp_item['packing_name'], wareid_spec_data.chine)) if erp_item['item_group'] is not None and wareid_spec_data.matkl is not None: if int(erp_item['item_group']) != int(wareid_spec_data.matkl): logging.info('item_group=%s和matkl=%s不相等' % (erp_item['item_group'], wareid_spec_data.matkl)) if erp_item['shelf_life'] is not None and wareid_spec_data.wareLife is not None: if float(erp_item['shelf_life']) != wareid_spec_data.wareLife: logging.info('shelf_life=%s和wareLife=%s不相等' % (float(erp_item['shelf_life']), wareid_spec_data.wareLife)) #if erp_item['is_auxiliary_ware'] is not None and wareid_spec_data.isAuxiliaryWare is not None: # if erp_item['is_auxiliary_ware'] != wareid_spec_data.isAuxiliaryWare: # logging.info('is_auxiliary_ware=%s和isAuxiliaryWare=%s不相等' # % (erp_item['is_auxiliary_ware'], wareid_spec_data.isAuxiliaryWare)) else: logging.info('根据o2o_sku_id=%s未查询到数据!!!!'%Long(erp_item['o2o_sku_id']))
def login(email_id, password): connection = get_connection() sql = "SELECT * FROM users WHERE email_id = '%s'" % email_id with connection.cursor() as cursor: cursor.execute(sql) if cursor.rowcount == 0: connection.close() return [False, 'Email Id'] else: results = cursor.fetchone() if sha256_crypt.verify(password, results['password']): connection.close() return [True, results] else: connection.close() return [False, "Password"]
def get_item_name(store_id, item_code): conn = None cursor = None try: conn = mysql.get_connection() cursor = conn.cursor() sql = "SELECT short_name FROM `tabStore Item` WHERE store_id = '%s' AND item_code = '%s'" % (store_id, item_code) db_result = cursor.execute(sql) if db_result: return cursor.fetchone()[0] else: return '' except Exception as e: logging.warn(e) finally: mysql.close(conn, cursor)
def erp_data_conversion(erp_item): conn = None cursor = None try: conn = mysql.get_connection() cursor = conn.cursor() if erp_item['unit_qty'] > 1: # 查入数为1的o2o_sku_id sql = "SELECT o2o_sku_id FROM `tabItem Barcode` " \ "WHERE sku_id='%s' AND unit_qty='%s'" %(erp_item['item_code'],1) db_result = cursor.execute(sql) erp_item['is_auxiliary_ware'] = 1 erp_item['ref_sku'] = cursor.fetchone()[0] erp_item['spec_num'] = erp_item['unit_qty'] elif erp_item['unit_qty'] == 1: sql = "SELECT uom FROM `tabUOM Conversion Detail` " \ "WHERE parent='%s' AND conversion_factor='%s'" % (erp_item['item_code'],erp_item['unit_qty']) db_result = cursor.execute(sql) uom = cursor.fetchone()[0] erp_item['uom'] = uom if uom == 'kg' or uom == 'Kg' or uom == 'KG': erp_item['is_auxiliary_ware'] = 1 # 通过uom_cd_doc2记录的入数反查Item Barcode表中的o2o_sku_id sql = "SELECT uom FROM `tabUOM Conversion Detail` " \ "WHERE parent='%s' AND (uom='%s' or uom='%s')"% (erp_item['item_code'], 'g','G') db_result = cursor.execute(sql) conversion_factor = cursor.fetchone()[0] sql = "SELECT o2o_sku_id FROM `tabItem Barcode` " \ "WHERE sku_id='%s' AND unit_qty" % (erp_item['item_code'], conversion_factor) db_result = cursor.execute(sql) o2o_sku_id = cursor.fetchone()[0] erp_item['ref_sku'] = o2o_sku_id erp_item['spec_num'] = 1000 else: erp_item['is_auxiliary_ware'] = 0 erp_item['ref_sku'] = None erp_item['spec_num'] = None else: pass # logging.warning('unit_qty=%s值存在问题'%erp_item['unit_qty']) except Exception as e: logging.info(e) finally: mysql.close(conn, cursor) return erp_item
def get_sent_mails(email_id): connection = get_connection() results = [] with connection.cursor() as cursor: cursor.execute("SELECT mail_id, email_id, message, subject, send_at FROM mails WHERE email_id = '%s'" % email_id) fetched_mails = cursor.fetchall() cursor.close() with connection.cursor() as cursor: for mail in fetched_mails: cursor.execute("SELECT email_id FROM mail_to WHERE mail_id = %s" % mail['mail_id']) result = cursor.fetchall() results.append({ 'mail_id': mail['mail_id'], 'to': result[0]['email_id'], 'subject': mail['subject'], 'message': mail['message'], 'send_at': mail['send_at'] }) connection.close() return results
def get_received_mails(email_id): connection = get_connection() results = [] with connection.cursor() as cursor: cursor.execute("SELECT * FROM mail_to WHERE email_id = '%s'" % email_id) fetched_mails = cursor.fetchall() cursor.close() with connection.cursor() as cursor: for mail in fetched_mails: cursor.execute("SELECT * FROM mails WHERE mail_id = %s" % mail['mail_id']) result = cursor.fetchall() results.append({ 'mail_id': result[0]['mail_id'], 'from': result[0]['email_id'], 'message': result[0]['message'], 'subject': result[0]['subject'], 'send_at': result[0]['send_at'], 'is_read': mail['is_read'], }) connection.close() return results
def send_multiple_mail(emails_to, email_id_from, subject, tag, message, file=None): connection = get_connection() if file is None: with connection.cursor() as cursor: sql = "INSERT INTO mails (email_id, message, subject, tag, send_at, is_read) VALUES ('%s', '%s', '%s', '%s', '%s', %d)" \ % (email_id_from, message, subject, tag, str(datetime.datetime.now()), 0) cursor.execute(sql) connection.commit() cursor.close() with connection.cursor() as cursor: cursor.execute("SELECT mail_id FROM mails ORDER BY mail_id DESC LIMIT 1") mail_id = cursor.fetchone()['mail_id'] cursor.close() with connection.cursor() as cursor: for email_id_to in emails_to: sql = "INSERT INTO mail_to VALUES (%s, '%s', 0, 0)" % (mail_id, email_id_to) cursor.execute(sql) connection.commit() connection.close() return True else: attachment_id = Mail().add_files(connection, file) with connection.cursor() as cursor: sql = "INSERT INTO mails (email_id, message, subject, tag, send_at, attachment_id, is_read) VALUES ('%s', '%s', '%s', '%s', '%s', %s, %d)" \ % (email_id_from, message, subject, tag, str(datetime.datetime.now()), attachment_id, 0) cursor.execute(sql) connection.commit() cursor.close() with connection.cursor() as cursor: cursor.execute("SELECT mail_id FROM mails ORDER BY mail_id DESC LIMIT 1") mail_id = cursor.fetchone()['mail_id'] cursor.close() with connection.cursor() as cursor: for email_id_to in emails_to: sql = "INSERT INTO mail_to VALUES (%s, '%s', 0, 0)" % (mail_id, email_id_to) cursor.execute(sql) connection.commit() connection.close() return True
def __init__(self): self.connection = get_connection()