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