def parse_xls_and_insert_to_database(path, filename, classify):
""" 解析excel文件，解析出满足导入基本条件的数据并插入到数据库中
:参数 path: 目标文件的存放路径
:参数 filename: 通常会以shop_id来进行命名
:参数 classify: 商品分类
:返回值:
"""
xlrd.Book.encoding = "gbk"
xls_workbook = xlrd.open_workbook("{}{}.xlsx".format(path, filename))
table = xls_workbook.sheets()[0]
nrows = table.nrows
print(nrows, "111")
for i in range(1, nrows):
row = table.row_values(i)
goods_code, goods_name, unit = row
if unit.find("kg(千克)") != -1:
new_unit = 1
else:
new_unit = 2
new_goods = models.Goods(goods_code=int(goods_code),
goods_name=goods_name,
unit=new_unit,
classify=classify)
session.add(new_goods)
session.commit()
return True
def post(self):
name = self.args["name"].strip()
code = self.args["code"].strip()
length = self.args.get("length", 0)
width = self.args.get("width", 0)
height = self.args.get("height", 0)
standards_weight = self.args.get("standards_weight", 0)
user = self.current_user
station = self.current_station
valid, message = self.validate_name_and_code(name, code, length, width,
height, standards_weight)
if not valid:
return self.send_fail(message)
new_goods = models.Goods(
name=name,
code=code,
length=check_int(length * 100),
width=check_int(width * 100),
height=check_int(height * 100),
standards_weight=check_int(standards_weight * 100),
creator_id=user.id,
station_id=station.id,
)
self.session.add(new_goods)
self.session.commit()
goods_dict = new_goods.to_dict()
return self.send_success(goods_dict=goods_dict)
def wish_order_parser(self, file_name, sheet):
wish_date = self.args["wish_date"]
station_id = self.current_station.id
creator_id = self.current_user.id
# file_name = '2018.11.17各店意向单(1)(1).xlsx'
# wb = load_workbook(file_name)
# sheet = wb.active
# 先搞个意向单
wish_order = self.session.query(models.WishOrder) \
.filter(models.WishOrder.wish_date == wish_date,
models.WishOrder.station_id == station_id) \
.first()
if not wish_order:
wish_order = models.WishOrder(
wish_date=wish_date,
status=2, # 直接是提交状态，要改再说
station_id=station_id,
creator_id=creator_id,
)
self.session.add(wish_order)
self.session.flush()
# 每次直接把之前的意向单商品全删了重建
self.session.query(models.WishOrderGoods) \
.filter(models.WishOrderGoods.wish_order_id == wish_order.id) \
.update({"status": -1})
self.session.flush()
goods_status = 0 # 0-有货 1-缺货 2-没货
for i in range(4, 999999):
# 取出表里一行的数据
no = sheet["A{}".format(i)].value
code = sheet["B{}".format(i)].value
name = sheet["C{}".format(i)].value
purchaser = sheet["D{}".format(i)].value
remarks = sheet["G{}".format(i)].value
# 如果序号和商品名称都没有，则表示数据导入完成
if not no and not name:
break
if name == '下方产品不保证有货':
goods_status = 1
continue
if remarks and '没有了' in remarks:
goods_status = 2
# print('{} {} {} {} {}'.format(no, code, name, purchaser, remarks))
# 用名字把商品查出来，没有就建一个
goods = self.session.query(models.Goods) \
.filter(models.Goods.name == name,
models.Goods.station_id == station_id,
models.Goods.status == 0) \
.first()
if not goods:
# print('-------------- {} 还没有，建一个 --------------'.format(name))
goods = models.Goods(
name=name,
station_id=station_id,
creator_id=creator_id,
)
self.session.add(goods)
self.session.flush()
goods.code = code
# 拿本行数据建一个意向单商品
wish_goods = models.WishOrderGoods(
remarks=remarks,
status=goods_status,
goods_id=goods.id,
wish_order_id=wish_order.id,
goods_name=goods.name,
priority=i,
)
self.session.add(wish_goods)
self.session.flush()
if purchaser:
purchaser_id = PURCHASER_MAP.get(purchaser)
if not purchaser_id:
return False, "{} 没有对应的采购员".format(purchaser), 0
default_purchaser = self.session.query(models.StaffGoods) \
.filter(models.StaffGoods.goods_id == goods.id) \
.first()
if not default_purchaser:
default_purchaser = models.StaffGoods(
goods_id=goods.id,
staff_id=purchaser_id,
)
self.session.add(default_purchaser)
self.session.flush()
else:
default_purchaser.staff_id = purchaser_id
self.session.flush()
self.session.commit()
return True, "", wish_order.id
def parse_xls_and_update_to_database(path, filename, classify, shop_id):
""" 解析excel文件，解析出满足导入基本条件的数据并插入到数据库中
:参数 path: 目标文件的存放路径
:参数 filename: 通常会以shop_id来进行命名
:参数 classify: 商品分类
:返回值:
"""
xlrd.Book.encoding = "gbk"
xls_workbook = xlrd.open_workbook("{}{}.xlsx".format(path, filename))
table = xls_workbook.sheets()[0]
nrows = table.nrows
exist_num = 0
exist_sg = 0
update_goods_id = []
for i in range(1, nrows):
row = table.row_values(i)
goods_code, goods_name, unit = row
if unit.find("kg") != -1:
new_unit = 1
else:
new_unit = 2
# 首先查询数据库中是否已经存在该种商品，有则不添加，只更新信息
exists_goods = session.query(
models.Goods).filter_by(goods_code=int(goods_code)).first()
if exists_goods:
exists_goods.goods_name = goods_name
exists_goods.unit = new_unit
else:
exist_num += 1
new_goods = models.Goods(goods_code=int(goods_code),
goods_name=goods_name,
unit=new_unit,
classify=classify,
market_id=1)
exists_goods = new_goods
session.add(new_goods)
session.flush()
update_goods_id.append(exists_goods.id)
# 更新十升店的商品
exists_shop_goods = session.query(models.ShopGoods).filter_by(
shop_id=shop_id, goods_id=exists_goods.id).first()
if not exists_shop_goods:
exists_shop_goods = models.ShopGoods(shop_id=shop_id,
goods_id=exists_goods.id)
session.add(exists_shop_goods)
session.flush()
exist_sg += 1
print("一共有%s种商品更新\n" % exist_num)
print("该店铺一共有%s种商品更新\n" % exist_sg)
Goods = models.Goods
ShopGoods = models.ShopGoods
# 把原来该店铺存在的商品，现在没有的删除了
all_shop_goods=session.query(ShopGoods)\
.join(Goods,ShopGoods.goods_id==Goods.id)\
.filter(Goods.classify==classify,\
ShopGoods.shop_id==shop_id,\
Goods.market_id==1)\
.all()
for item in all_shop_goods:
if item.goods_id not in update_goods_id:
item.status = -1
session.commit()
return True
def add_test_data(data):
object_list = list()
user = models.AccountInfo(
phone='18627765247',
sex=2,
nickname='kai',
realname="chenkai",
headimgurl='https://thirdwx.qlogo.cn/mmopen/vi_32/DYAIOgq83eq4GRIvU1EfTcicfRHlrpibKnSPjDtoGHWiccXP8lbF0XR5uDzjbJlREwAOVZiaFxkDlqm5CWsMaJe6PQ/132',
birthday=2018,
wx_country='中国',
wx_province='江西',
wx_city='九江',
wx_unionid='oxkR_jlY3xj-BdzsPGggDTslqK5',
wx_openid='oMvaL1i3oOJkI8hRjCSfQ8rXLVlw',
create_time='2018-03-23 12:10:06.000',
passport_id=194726,
alipay_acctid='',
alipay_acctname=''
)
object_list.append(user)
station = models.TransferStation(
name="测试中转站",
province=1,
city=1,
address="光谷",
create_time=datetime.datetime.now(),
status=0,
creator_id=1
)
object_list.append(station)
staff = models.Staff(
remarks="测试员工",
date_onboarding=datetime.date.today(),
birthday=datetime.date.today(),
status=0,
station_id=1,
account_id=1
)
object_list.append(staff)
firm_1 = models.Firm(
name="甲",
station_id=1,
creator_id=1
)
object_list.append(firm_1)
firm_2 = models.Firm(
name="乙",
station_id=1,
creator_id=1
)
object_list.append(firm_2)
firm_3 = models.Firm(
name="丙",
station_id=1,
creator_id=1
)
object_list.append(firm_3)
goods_1 = models.Goods(
name="苹果",
station_id=1
)
object_list.append(goods_1)
goods_2 = models.Goods(
name="脆梨",
station_id=1
)
object_list.append(goods_2)
goods_3 = models.Goods(
name="柚子",
station_id=1
)
object_list.append(goods_3)
firm_goods_1 = models.FirmGoods(
goods_id=1,
firm_id=1,
)
object_list.append(firm_goods_1)
firm_goods_2 = models.FirmGoods(
goods_id=1,
firm_id=2,
)
object_list.append(firm_goods_2)
firm_goods_3 = models.FirmGoods(
goods_id=2,
firm_id=1,
)
object_list.append(firm_goods_3)
wish_order = models.WishOrder(
wish_date=datetime.date.today(),
station_id =1,
creator_id=1
)
object_list.append(wish_order)
purchase_order = models.PurchaseOrder(
station_id=1,
purchaser_id=1,
wish_order_id=1
)
object_list.append(purchase_order)
purchase_order_goods_1 = models.PurchaseOrderGoods(
firm_id=1,
goods_id=1,
purchase_order_id=1
)
object_list.append(purchase_order_goods_1)
purchase_order_goods_2 = models.PurchaseOrderGoods(
firm_id=2,
goods_id=1,
purchase_order_id=1
)
object_list.append(purchase_order_goods_2)
purchase_order_goods_3 = models.PurchaseOrderGoods(
firm_id=1,
goods_id=2,
purchase_order_id=1
)
object_list.append(purchase_order_goods_3)
session.add_all(object_list)
session.commit()
if remarks and '没有了' in remarks:
goods_status = 2
print('{} {} {} {} {}'.format(no, code, name, purchaser, remarks))
# 用名字把商品查出来，没有就建一个
goods = session.query(models.Goods) \
.filter(models.Goods.name == name,
models.Goods.station_id == STATION_ID,
models.Goods.status == 0) \
.first()
if not goods:
print('-------------- {} 还没有，建一个 --------------'.format(name))
goods = models.Goods(
name=name,
station_id=STATION_ID,
creator_id=CREATOR_ID,
)
session.add(goods)
session.flush()
goods.code = code
# 拿本行数据建一个意向单商品
wish_goods = models.WishOrderGoods(
remarks=remarks,
status=goods_status,
goods_id=goods.id,
wish_order_id=wish_order.id,
goods_name=goods.name,
priority=i,
)