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 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()
def post(self):
wish_date = self.args["wish_date"]
goods_list = self.args["goods_list"]
commit = self.args.get("commit", False)
valid, message = self.validate_goods_list(goods_list)
if not valid:
return self.send_fail(message)
existed_order_today = self.session.query(models.WishOrder) \
.filter(models.WishOrder.status > 0,
models.WishOrder.station_id == self.current_station.id,
models.WishOrder.wish_date == wish_date) \
.first()
if existed_order_today:
return self.send_fail("今天已经创建过意向单了")
new_order = models.WishOrder(
station_id=self.current_station.id,
creator_id=self.current_user.id,
wish_date=wish_date,
)
self.session.add(new_order)
self.session.flush()
# 商品库存
goods_ids = [goods["goods_id"] for goods in goods_list]
goods_storage_list = self.session.query(models.Goods.id, models.Goods.stock) \
.filter(models.Goods.id.in_(goods_ids)) \
.all()
goods_storage_dict = {
goods.id: goods.stock
for goods in goods_storage_list
}
# 上一个意向单中的货品名
last_order = self.session.query(models.WishOrder) \
.filter(models.WishOrder.station_id == self.current_station.id,
models.WishOrder.create_time < new_order.create_time) \
.order_by(models.WishOrder.create_time.desc()) \
.limit(1) \
.first()
last_order_goods_name_dict = {}
if last_order:
last_order_goods_list = self.session.query(models.WishOrderGoods.id, models.WishOrderGoods.goods_name) \
.filter(models.WishOrderGoods.wish_order_id == last_order.id,
models.WishOrderGoods.status >= 0) \
.all()
last_order_goods_name_dict = {
goods.id: goods.goods_name
for goods in last_order_goods_list
}
# 添加意向单商品
i = 0
for goods in goods_list:
goods_id = goods["goods_id"]
order_goods_name = goods["order_goods_name"]
status = goods["status"]
tag = goods.get("tag", "")
remarks = goods.get("remarks", "")
storage = goods_storage_dict.get(goods_id, 0)
last_goods_name = last_order_goods_name_dict.get(goods_id)
goods_name_modified = 1 if last_goods_name and last_goods_name != order_goods_name else 0
new_goods = models.WishOrderGoods(
wish_order_id=new_order.id,
goods_id=goods_id,
goods_name=order_goods_name,
goods_name_modified=goods_name_modified,
confirmed_storage=storage,
tag=tag,
remarks=remarks,
status=status,
priority=i,
)
self.session.add(new_goods)
i += 1
# 默认保存为草稿
if commit:
new_order.status = 2
else:
new_order.status = 1
self.session.commit()
return self.send_success(order_id=new_order.id)
def get_current_wish_order(self, station):
now = datetime.datetime.now()
today = now.date()
# 以每天下午三点为界
if now.hour >= 15:
from_time = datetime.datetime.combine(today, datetime.time(15))
to_time = from_time + datetime.timedelta(days=1)
else:
to_time = datetime.datetime.combine(today, datetime.time(15))
from_time = to_time - datetime.timedelta(days=1)
# 最新的意向单
order = self.session.query(models.WishOrder) \
.filter(models.WishOrder.station_id == station.id,
models.WishOrder.create_time >= from_time,
models.WishOrder.create_time <= to_time) \
.first()
order_goods_list = []
if not order:
# 下午三点前创建今天的意向单，下午三点后创建第二天的意向单
if now.hour >= 15:
wish_date = today + datetime.timedelta(days=1)
else:
wish_date = today
order = models.WishOrder(
create_time=now,
wish_date=wish_date,
status=2,
station_id=station.id,
creator_id=self.current_user.id,
)
self.session.add(order)
self.session.flush()
# 上一个意向单
last_order = self.session.query(models.WishOrder) \
.filter(models.WishOrder.station_id == station.id,
models.WishOrder.create_time < from_time) \
.order_by(models.WishOrder.create_time.desc()) \
.limit(1) \
.first()
# 复制上一个意向单的内容
if last_order:
last_order_goods_list = self.session.query(models.WishOrderGoods, models.Goods) \
.join(models.Goods) \
.filter(models.WishOrderGoods.wish_order_id == last_order.id,
models.WishOrderGoods.status >= 0) \
.all()
for order_goods, goods in last_order_goods_list:
new_order_goods = models.WishOrderGoods(
goods_name=order_goods.goods_name,
goods_name_modified=0,
tag=order_goods.tag,
remarks=order_goods.remarks,
status=order_goods.status,
priority=order_goods.priority,
goods_id=order_goods.goods_id,
wish_order_id=order.id,
)
self.session.add(new_order_goods)
self.session.flush()
order_goods_list.append((new_order_goods, goods))
self.session.commit()
else:
order_goods_list = self.session.query(models.WishOrderGoods, models.Goods) \
.join(models.Goods) \
.filter(models.WishOrderGoods.status >= 0,
models.WishOrderGoods.wish_order_id == order.id) \
.order_by(models.WishOrderGoods.priority.asc()) \
.all()
# 默认采购员信息
goods_ids = {goods.id for _, goods in order_goods_list}
default_purchasers = self.session.query(models.StaffGoods) \
.filter(models.StaffGoods.goods_id.in_(goods_ids)) \
.all()
purchaser_ids = {
purchaser.staff_id
for purchaser in default_purchasers
}
purchaser_accounts = self.session.query(models.Staff, models.AccountInfo) \
.join(models.AccountInfo, models.AccountInfo.id == models.Staff.account_id) \
.filter(models.Staff.id.in_(purchaser_ids)) \
.all()
purchaser_account_dict = {
item[0].id: item
for item in purchaser_accounts
}
default_purchaser_dict = {
item.goods_id: purchaser_account_dict.get(item.staff_id)
for item in default_purchasers
}
order_goods_data = defaultdict(list)
for order_goods, goods in order_goods_list:
default_purchaser = default_purchaser_dict.get(goods.id)
purchaser_id = default_purchaser[0].id if default_purchaser else 0
purchaser_name = default_purchaser[0].remarks or default_purchaser[
1].username if default_purchaser else ""
# 仓库库存以提交时的为准
order_goods_data[order_goods.status].append({
"id":
order_goods.id,
"wish_order_id":
order_goods.wish_order_id,
"goods_id":
goods.id,
"serial_number":
goods.serial_number,
"goods_name":
goods.name,
"goods_code":
goods.code,
"goods_storage":
check_float(goods.stock / 100),
"create_time":
TimeFunc.time_to_str(order_goods.create_time),
"tag":
order_goods.tag,
"remarks":
order_goods.remarks,
"status":
order_goods.status,
"purchaser_id":
purchaser_id,
"purchaser_name":
purchaser_name,
})
order_data = {
"id": order.id,
"station_id": order.station_id,
"create_time": TimeFunc.time_to_str(order.create_time),
"creator_id": order.creator_id,
"wish_date": TimeFunc.time_to_str(order.wish_date, "date"),
"status": order.status,
"goods_data": order_goods_data,
}
return order_data
session = DBSession()
filename = '2018-12-30各店意向单(1).xlsx'
wb = load_workbook(filename)
sheet = wb.active
# 先搞个意向单
wish_order = 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,
)
session.add(wish_order)
session.flush()
# 每次直接把之前的意向单商品全删了重建
session.query(models.WishOrderGoods) \
.filter(models.WishOrderGoods.wish_order_id == wish_order.id) \
.update({"status": -1})
session.flush()
goods_status = 0 # 0-有货 1-缺货 2-没货
for i in range(4, 999999):
# 取出表里一行的数据
no = sheet["A{}".format(i)].value