def find(self, text):
print(text)
self.list.clear()
length = len(text)
it = root
print(length)
for index in range(length):
if it == 'end':
break
it = self.find_it(it, text[index])
print(text)
if len(self.list) == 0:
return self.answer
sql = "SELECT answer FROM root"
for u in range(0, len(self.list) - 1):
sql = sql + "_" + str(self.list[u])
sql = sql + " WHERE q_id = " + str(self.list[len(self.list) - 1]) + ";"
print(sql)
conn = mysql.connect()
if conn is None:
return self.answer
cursor = conn.cursor()
cursor.execute(sql)
result = cursor.fetchone()
if result is None:
return self.answer
mysql.close(conn)
return result[0]
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 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 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 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 shutdown():
# Create necessary database connections to use
mysql.close()
mongo.close()
import config, mysql
# read config file
config = config.Config("config.txt")
# get connection
mysql = mysql.MySQL(config.db_host, config.db_uname, config.db_passwd, config.db_name)
# insert
for id in range(10):
data = (id % 2, "Doe % d" % id, "John", "place", "ville")
mysql.insert_or_update(config.db_table, config.db_columns, data)
# close connection
mysql.close()
def sqlQuery(sql=""):
db = mysql.connect()
mysql.query(db, sql)
mysql.close(db)
host = 'localhost'
user = ''
password = ''
conn = MySQLdb.Connection(db=database,
host=host,
user=user,
passwd=password)
mysql = conn.cursor()
sql = r"select * from annotations"
mysql.execute(sql)
fields = mysql.fetchall()
mysql.close()
conn.close()
print fields
# --------------------------------------------------------------------
# Reformat array of strings into portfolio of paintings:
# --------------------------------------------------------------------
# Make mean and weight images:
sum = numpy.zeros(nx, ny)
sumsq = numpy.zeros(nx, ny)
mean = numpy.zeros(nx, ny)
def queryMysql(self, sql):
db = mysql.connect()
mysql.query(db, sql)
mysql.close(db)
def getMysql(self, sql):
db = mysql.connect()
result = mysql.getData(db, sql)
mysql.close(db)
return result
