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,