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