def parse_xls_and_insert_to_database_shop(path, filename):
""" 解析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
Goods = models.Goods
fruit_count = session.query(Goods).filter(Goods.status != -1).filter_by(
classify=1).count()
vegetables_count = session.query(Goods).filter(
Goods.status != -1).filter_by(classify=2).count()
for i in range(1, nrows):
row = table.row_values(i)
shop_province, shop_city, shop_name, shop_region, shop_city_carrefour = row
new_shop = models.Shop(shop_province=shop_province,\
shop_city=shop_city,\
shop_name=shop_name,\
shop_region=shop_region,\
shop_city_carrefour=shop_city_carrefour,\
admin_id=1,\
fruit_count=fruit_count,\
vegetables_count=vegetables_count)
session.add(new_shop)
session.commit()
return True
def post(self):
abbreviation = self.args["abbreviation"].strip()
name = self.args.get("name", "").strip()
address = self.args.get("address", "").strip()
contacts = self.args.get("contacts")
if not contacts:
return self.send_fail("至少有一个店铺订货人")
valid, message = self.validate_contacts(contacts)
if not valid:
return self.send_fail(message)
abbr_duplicated = self.session.query(models.Shop.id) \
.filter(models.Shop.abbreviation == abbreviation, models.Shop.station_id == self.current_station.id, models.Shop.status == 0) \
.scalar()
if abbr_duplicated:
return self.send_fail("该店铺简称已存在")
if not abbreviation:
return self.send_fail("店铺简称不能为空")
elif len(abbreviation) > 4:
return self.send_fail("店铺简称不能超过 4 个字符")
new_shop = models.Shop(
name=name,
abbreviation=abbreviation,
address=address,
station_id=self.current_station.id,
creator_id=self.current_user.id
)
self.session.add(new_shop)
self.session.flush()
# 添加门店订货人
if contacts:
phones = {contact["phone"] for contact in contacts}
# 绑定到已有用户
registered_users = self.session.query(models.AccountInfo) \
.filter(models.AccountInfo.phone.in_(phones)) \
.all()
user_dict = {user.phone: user for user in registered_users}
for contact in contacts:
phone = contact["phone"]
name = contact.get("name", phone)
user = user_dict.get(phone)
new_contact = models.ShopContact(
shop_id=new_shop.id,
account_id=user.id if user else 0,
phone=phone,
name=name,
)
self.session.add(new_contact)
self.session.commit()
return self.send_success()
def add_shop(self, admin_id, shop_id):
# print('[MarketShopAdminInfo]add_shop: login in add_shop')
shop_admin = self.session.query(
models.ShopAdmin).filter_by(id=admin_id).first()
if not shop_admin:
return self.send_fail(
'[MarketShopAdminInfo]add_shop: shop_admin not found')
temp_shop = self.session.query(
models.Spider_Shop).filter_by(id=int(shop_id)).first()
if not temp_shop:
return self.send_fail(
'[MarketShopAdminInfo]add_shop: temp_shop not found')
# 添加系统默认的时间段
period1 = models.Period(name="中午",
start_time="12:00",
end_time="13:00") #按时达默认时间段
period2 = models.Period(name="下午",
start_time="17:00",
end_time="18:00") #按时达默认时间段
period3 = models.Period(name="晚上",
start_time="21:00",
end_time="22:00") #按时达默认时间段
period4 = models.Period(name="中午",
start_time="12:00",
end_time="13:00",
config_type=1) #自提时间默认时间段
period5 = models.Period(name="下午",
start_time="17:00",
end_time="18:00",
config_type=1) #自提时间默认时间段
period6 = models.Period(name="晚上",
start_time="21:00",
end_time="22:00",
config_type=1) #自提时间默认时间段
config = models.Config()
config.periods.extend(
[period1, period2, period3, period4, period5, period6])
marketing = models.Marketing()
shop_code = self.make_shop_code()
# print('[MarketShopAdminInfo]add_shop: make shop_code success')
temp_shop.shop_code = shop_code
shop = models.Shop(admin_id=admin_id,
shop_name=temp_shop.shop_name,
create_date_timestamp=time.time(),
shop_trademark_url=temp_shop.shop_logo,
shop_province=420000,
shop_auth=5,
shop_city=420100,
shop_address_detail=temp_shop.shop_address,
shop_intro=temp_shop.description,
shop_code=shop_code,
shop_phone=temp_shop.shop_phone,
lat=temp_shop.lat,
lon=temp_shop.lon)
shop.config = config
shop.marketing = marketing
shop.shop_start_timestamp = time.time()
temp_shop.has_done = 1
self.session.add(shop)
self.session.flush()
# print('[MarketShopAdminInfo]add_shop: shop add success')
# 添加商品
# print('[MarketShopAdminInfo]add_shop: start add goods')
spider_goods = self.session.query(
models.Spider_Good).filter_by(shop_id=temp_shop.shop_id).all()
for temp_good in spider_goods:
# print("[MarketShopAdminInfo]add_shop: shop.id:",shop.id)
new_good = models.Fruit(
shop_id=shop.id,
fruit_type_id=999,
name=temp_good.goods_name,
storage=100,
unit=3,
img_url=temp_good.good_img_url,
)
new_good.charge_types.append(
models.ChargeType(price=temp_good.goods_price,
unit=3,
num=1,
market_price=None))
self.session.add(new_good)
self.session.flush()
######################################################################################
# inspect whether staff exited
######################################################################################
temp_staff = self.session.query(models.ShopStaff).get(shop.admin_id)
# print('[MarketShopAdminInfo]add_shop: temp_staff:',temp_staff)
# print('[MarketShopAdminInfo]add_shop: admin_id:',shop.admin_id)
if temp_staff is None:
self.session.add(
models.ShopStaff(id=shop.admin_id,
shop_id=shop.id)) # 添加默认员工时先添加一个员工，否则报错
self.session.flush()
self.session.add(
models.HireLink(staff_id=shop.admin_id,
shop_id=shop.id,
default_staff=1)) # 把管理者默认为新店铺的二级配送员
self.session.flush()
# 把管理员同时设为顾客的身份
customer_first = self.session.query(models.Customer).get(shop.admin_id)
if customer_first is None:
self.session.add(
models.Customer(id=shop.admin_id,
balance=0,
credits=0,
shop_new=0))
self.session.commit()
return shop.shop_code
def post(self):
action = self.args["action"]
data = self.args["data"]
if not action or not data:
return self.send_error(403)
try:
super_admin = self.session.query(models.ShopAdmin).filter_by(
id=self.current_user.id, role=1).one()
except:
super_admin = None
if not super_admin:
return self.send_fail("您不是卖家，无法创建新的店铺")
# try:
# if_shopadmin = self.session.query(models.HireLink).join(models.ShopStaff,models.HireLink.staff_id == models.ShopStaff.id)\
# .filter(models.HireLink.active==1,models.HireLink.work ==9 ,models.ShopStaff.id == self.current_user.id).first()
# except:
# if_shopadmin = None
# if if_shopadmin:
# return self.send_fail("您没有创建店铺的权限")
#检查申请店铺数量
try:
shops = self.session.query(
models.Shop).filter_by(admin_id=self.current_user.id)
except:
shops = None
if shops:
shop_frist = shops.first()
if shop_frist:
if shop_frist.shop_auth == 0:
return self.send_fail(
"您的第一个店铺还未进行认证，店铺认证后才可创建多个店铺。最多可创建30个店铺。")
elif shop_frist.shop_auth != 0 and shops.count() >= 30:
return self.send_fail("最多可创建30个店铺")
_admin_id = self.current_user.id
# if self.current_shop:
# _admin_id = self.current_shop.admin_id
# else:
# _admin_id = self.current_user.id
if action == "diy":
args = {}
args["admin_id"] = _admin_id
args["shop_name"] = data["shop_name"]
args["shop_trademark_url"] = data["shop_logo"]
args["shop_phone"] = data["shop_phone"]
args["shop_province"] = data["shop_province"]
args["shop_city"] = data["shop_city"]
args["shop_address_detail"] = data["shop_address_detail"]
args["lat"] = data["lat"]
args["lon"] = data["lon"]
args["shop_code"] = self.make_shop_code()
args["create_date_timestamp"] = time.time()
shop = models.Shop(**args)
self.create_shop(shop)
self.create_staff(shop)
return self.send_success()
elif action == "search":
shop_name = data["shop_name"]
try:
shops = self.session.query(models.Spider_Shop).filter(
models.Spider_Shop.shop_name.like("%%%s%%" %
shop_name)).all()
except:
shops = None
# print("[AdminCreateShop]Shop search error")
data = []
if shops:
for shop in shops:
data.append({
"shop_name": shop.shop_name,
"address": shop.shop_address,
"logo": shop.shop_logo,
"id": shop.id
})
return self.send_success(data=data)
elif action == "import":
if "shop_list" not in data or "code" not in data:
return self.send_error(403)
market_member_id = data["code"]
try:
if_market = self.session.query(
models.SpreadMember).filter_by(code=data["code"]).first()
except:
if_market = None
if not if_market:
return self.send_fail("请输入正确的市场推广人员ID")
shop_list = data["shop_list"]
if self.current_shop:
shop_number = len(self.current_shop.admin.shops)
if len(shop_list) + shop_number > 30:
notice = "您已创建" + str(shop_number) + "家店铺，最多还可创建" + str(
30 - shop_number) + "家店铺"
return self.send_fail(notice)
else:
if len(shop_list) >= 30:
return self.send_fail("最多可创建30家店铺")
spider_shops = self.session.query(models.Spider_Shop).filter(
models.Spider_Shop.id.in_(shop_list)).all()
for spider_shop in spider_shops:
shop = models.Shop(
admin_id=_admin_id,
shop_name=spider_shop.shop_name,
shop_trademark_url=spider_shop.shop_logo,
shop_province=spider_shop.shop_province,
shop_city=spider_shop.shop_city,
shop_address_detail=spider_shop.shop_address,
create_date_timestamp=time.time(),
shop_intro=spider_shop.description,
shop_code=self.make_shop_code(),
shop_phone=spider_shop.shop_phone,
lat=spider_shop.lat,
lon=spider_shop.lon,
spread_member_code=data["code"])
self.create_shop(shop)
# 添加商品
spider_goods = self.session.query(
models.Spider_Good).filter_by(
shop_id=spider_shop.shop_id).all()
for temp_good in spider_goods:
name = temp_good.goods_name
if len(name) > 20:
name = name[1:21]
new_good = models.Fruit(
shop_id=shop.id,
fruit_type_id=999,
name=name,
storage=100,
unit=3,
img_url=temp_good.good_img_url,
)
new_good.charge_types.append(
models.ChargeType(price=temp_good.goods_price,
unit=3,
num=1,
market_price=None))
self.session.add(new_good)
shop.goods_count = len(spider_goods)
self.session.commit()
self.create_staff(shop)
return self.send_success()
def demand_order_parser(self, file_name, sheet):
wish_order_id = self.args["wish_order_id"]
# file_name = '2018.12.4西青道店意向单.xlsx'
# wb = load_workbook(file_name)
# sheet = wb.active
# 从文件名里匹配门店名
matcher = re.search('\d([^\d]+)店', file_name)
if matcher:
shop_name = matcher.group(1)
else:
# raise Exception('{} 文件里没找到有效店名')
return False, "{} 文件里没找到有效店名".format(file_name)
# 把意向单拿出来
wish_order = models.WishOrder.get_by_id(self.session, wish_order_id)
if not wish_order:
# raise Exception('先建个意向单')
return False, "需要先建个意向单"
station_id = wish_order.station_id
creator_id = wish_order.creator_id
# 从文件名里找一个 Shop，没有就建一个
shop = self.session.query(models.Shop) \
.filter(models.Shop.abbreviation == shop_name,
models.Shop.station_id == station_id,
models.Shop.status == 0) \
.first()
if not shop:
# print('-------------- {} 店还没有，建一个 --------------'.format(shop_name))
shop = models.Shop(
abbreviation=shop_name,
station_id=station_id,
creator_id=creator_id,
)
self.session.add(shop)
self.session.flush()
# 建一个订货单
demand_order = self.session.query(models.DemandOrder) \
.filter(models.DemandOrder.wish_order_id == wish_order_id,
models.DemandOrder.shop_id == shop.id) \
.first()
if not demand_order:
# print('-------------- {} 店还没订货，建一个订货单 --------------'.format(shop_name))
demand_order = models.DemandOrder(
wish_order_id=wish_order_id,
shop_id=shop.id,
creator_id=creator_id,
status=2, # 直接加进汇总单里
)
self.session.add(demand_order)
self.session.flush()
# 每次直接把之前的订货单商品全删了重建
self.session.query(models.DemandOrderGoods) \
.filter(models.DemandOrderGoods.demand_order_id == demand_order.id,
models.DemandOrderGoods.status == 0) \
.update({"status": -1})
self.session.flush()
# 把意向单商品都取出来
wish_goods_list = self.session.query(models.WishOrderGoods) \
.join(models.WishOrder, models.WishOrder.id == models.WishOrderGoods.wish_order_id) \
.filter(models.WishOrder.id == wish_order_id,
models.WishOrderGoods.status >= 0) \
.all()
wish_goods_dict = {goods.goods_id: goods for goods in wish_goods_list}
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
storage = sheet["E{}".format(i)].value
demand_amount = sheet["F{}".format(i)].value
# 如果序号和商品名称都没有，则表示数据导入完成
if not no and not name:
break
if name == '下方产品不保证有货':
continue
if storage and not isinstance(storage, float) and not isinstance(
storage, int):
return False, "{} 的库存量 {} 不是纯数字，手动改改".format(name, storage)
if demand_amount and not isinstance(demand_amount,
float) and not isinstance(
demand_amount, int):
return False, "{} 的订货量 {} 不是纯数字，手动改改".format(
name, demand_amount)
# 用名字把商品查出来
goods = self.session.query(models.Goods) \
.filter(models.Goods.name == name,
models.Goods.station_id == station_id) \
.first()
if not goods:
return False, "没找到 {}，先手动建好加到意向单里吧".format(name)
wish_goods = wish_goods_dict.get(goods.id)
if not wish_goods:
return False, "意向单里没有 {}，先去手动加到意向单里".format(name)
# 拿本行数据建一个订货单商品
demand_goods = models.DemandOrderGoods(
current_storage=round(storage * 100) if storage else 0,
demand_amount=round(demand_amount *
100) if demand_amount else 0,
goods_id=goods.id,
demand_order_id=demand_order.id,
wish_order_goods_id=wish_goods.id,
)
self.session.add(demand_goods)
self.session.flush()
self.session.commit()
return True, ""
if not wish_order:
raise Exception('先建个意向单')
station_id = wish_order.station_id
creator_id = wish_order.creator_id
# 从文件名里找一个 Shop，没有就建一个
shop = session.query(models.Shop) \
.filter(models.Shop.abbreviation == shop_name,
models.Shop.station_id == station_id,
models.Shop.status == 0) \
.first()
if not shop:
print('-------------- {} 店还没有，建一个 --------------'.format(shop_name))
shop = models.Shop(
abbreviation=shop_name,
station_id=station_id,
creator_id=creator_id,
)
session.add(shop)
session.flush()
# 建一个订货单
demand_order = session.query(models.DemandOrder) \
.filter(models.DemandOrder.wish_order_id == WISH_ORDER_ID,
models.DemandOrder.shop_id == shop.id) \
.first()
if not demand_order:
print(
'-------------- {} 店还没订货，建一个订货单 --------------'.format(shop_name))
demand_order = models.DemandOrder(
wish_order_id=WISH_ORDER_ID,