class User: """Class that provides methods to work with User data.""" SELECT_TELEGRAM_ID = db.text(""" SELECT telegram_id FROM "user" WHERE id = :user_id and telegram_id is not null """) @classmethod async def get_telegram_id(cls, user_id): """Retrieve telegram id by user id.""" telegram_id_cache_key = TELEGRAM_ID_CACHE_KEY.format(user_id=user_id) telegram_id = await cache.get(telegram_id_cache_key) if telegram_id: return telegram_id try: user = await db.one(cls.SELECT_TELEGRAM_ID, user_id=user_id) except exceptions.NoResultFound: LOGGER.error( "Could not find user by id=%s with activated telegram.", user_id) raise DatabaseError except SQLAlchemyError as err: LOGGER.error("Failed to fetch user by id=%s. Error: %s", user_id, err) raise DatabaseError else: await cache.set(telegram_id_cache_key, user.telegram_id, TELEGRAM_ID_CACHE_EXPIRE) return user.telegram_id
def find_conflicts(cls, screen_id, scheds): """Find a schedule in the database by screen_id its schedule.""" for sched in scheds: yield (cls.query.from_statement( db.text( SELECT_CONFLICT_SCHEDULE_QUERY.format(screen_id=screen_id, **sched))).first())
def post(self): #, id data = UserRegister.parser.parse_args() if data["service-id"] == pran_rfl_serviceId: username = data["username"] service_id = data["service-id"] password = data["password"] encrypted_password = UserModel.encrypt_password(password) ### Check Admin or not ##### admin_user = "******" authenticated_username = current_identity.username if safe_str_cmp(authenticated_username, admin_user) != True: return {"message":"Only Admin can create user."}, 400 if UserModel.find_by_username_serviceid(username, service_id): return {"message":"{} is already exist in service-id {}.".format(username, service_id)}, 400 sql = db.text('SELECT (NVL(MAX(ID),0)+1) FROM TANPOOL_AUTH') user_master_id = db.engine.execute(sql).fetchone() user = UserModel(user_master_id[0], data["username"], encrypted_password, data["service-id"]) # UserModel(data["id"], data["username"], data["password"]) = UserModel(**data) user.save_to_db() return {"message":"{} is created successfully as your authentication user".format(data["username"])}, 201 else: return {"message":"Service ID is unknown"}
class Event(db.Model): __tablename__ = 'event' id = db.Column(UUID(as_uuid=True), primary_key=True, server_default=db.text("uuid_generate_v4()")) item = db.Column(UUID(as_uuid=True), db.ForeignKey('moveout.id'), nullable=False) detail = db.Column(db.String(200), unique=False, nullable=False) type = db.Column(db.Enum(EventType), unique=False, nullable=False) created_date = db.Column(db.DateTime, default=db.func.now(), onupdate=db.func.now()) @classmethod def find_by_id(cls, event_id): return cls.query.filter_by(id=event_id).first() @classmethod def find_by_item_id(cls, item_id): return cls.query.filter_by(item=item_id).all() @classmethod def find_total_by_type(cls): return { 'unknown': cls.query.filter_by(type=EventType.UNKNOWN).count(), 'external': cls.query.filter_by(type=EventType.EXTERNAL).count(), 'staff': cls.query.filter_by(type=EventType.STAFF).count(), 'user': cls.query.filter_by(type=EventType.USER).count(), 'system': cls.query.filter_by(type=EventType.SYSTEM).count() } @classmethod def find_oldest_event(cls): return cls.query.order_by(cls.created_date.asc()).first() @classmethod def find_newest_event(cls): return cls.query.order_by(cls.created_date.desc()).first() @classmethod def find_all_events(cls, filter_params): query = cls.query if filter_params['sort'] == OrderType.ASCENDING: query = query.order_by(cls.created_date.asc()) elif filter_params['sort'] == OrderType.DESCENDING: query = query.order_by(cls.created_date.desc()) return query.all() def commit(self): db.session.add(self) db.session.commit() def remove(self): db.session.delete(self) db.session.commit()
async def count(self, filter: Optional[Union[str, TextClause]] = None) -> int: q = db.select([db.func.count([x for x in self.model.pk][0])]) filter = filter if filter is not None else self.filter if filter is not None: if not isinstance(filter, TextClause): filter = db.text(filter) q = q.where(filter) return await q.gino.scalar() or 0
def post(self, name, date): store = StoreModel.find_by_name(name, date) #print("################## BEFORE CHECKING THE NAME...") if store: return {"message": "{} store is already exist.".format(name)}, 400 #print("################## AFTER CHECKING THE NAME...") sql = db.text('SELECT (NVL(MAX(EMPNO),0)+1) FROM EMP') my_store_id = db.engine.execute(sql).fetchone() #print("###################### ", my_store_id) #datetime_object = datetime.strptime(date, '%d-%m-%Y') datetime_object = datetime.strptime(date, '%d-%m-%Y') #print("###################### ", datetime_object) ################################################################################################ # IF we use encrypted data then we have to use this part # my_data = request.get_json() # print("######################### ",my_data) # ####################################################################### # #### Converting Decrypted data into Dictionary # ###################################################################### # data = ast.literal_eval(my_data) # print("###### text_to_dict : ", data) # print(type(data)) ################################################################################################ data = request.get_json() print("######################### ", data) print("Type of data : ", type(data)) new_store = { "name": data["name"], "designation": data["designation"], "manager_id": data["manager_id"], "date_of_birth": data["date_of_birth"], "salary": data["salary"], "commission": data["commission"], "department_no": data["department_no"] } #print("######################### ",new_store) store = StoreModel(my_store_id[0], new_store["name"], new_store["designation"], new_store["manager_id"], datetime_object.date(), int(new_store["salary"]), int(new_store["commission"]), int(new_store["department_no"])) print(" ********************* ", store) try: store.save_to_db() except Exception as e: return { "message": "Unexpected error occured in Stroe insertion. please see the post method of store Resource." }, 500 return store.json(), 201
def generate_json_ticket(self) -> dict: """Generate a JSON ticket summary of a reservation.""" ticket = (self.query.from_statement( db.text(SELECT_TICKET_QUERY).params(res_id=self.id)).with_entities( *TICKET_COLUMNS).first()) ticket_dict = dict(zip(TICKET_COLUMNS, ticket)) ticket_dict["price_breakdown"] = ticket_dict["price_breakdown"].decode( ) return ticket_dict
class MoveOut(db.Model): __tablename__ = 'moveout' id = db.Column(UUID(as_uuid=True), primary_key=True, server_default=db.text("uuid_generate_v4()")) created_date = db.Column(db.DateTime, default=db.func.now(), onupdate=db.func.now()) value = db.Column(db.Integer(), unique=False, nullable=False)
async def get( self, filter: Optional[Union[str, TextClause]] = None, serializer: Optional[PydanticModel] = None, ) -> list: """ Build and execute the paged sql query, returning the results as a list of Pydantic model instances (if serializer is specified) or dicts (if serializer is NOT specified) """ q = self.model.query if filter is not None: if not isinstance(filter, TextClause): filter = db.text(filter) q = q.where(filter) if self.limit > 0: q = q.limit(self.limit) if self.sort: q = q.order_by(db.text(f"{self.sort} {self.sort_direction}")) result = await q.offset(self.offset).gino.all() if serializer: return [serializer.from_orm(x) for x in result] else: return result
def post(self, name): if ItemModel.find_by_name(name): # here we pass the name into ItemModel calss of item.py in models package and it return value as object return {"Message":"{} is exist. No need to create it again.".format(name)}, 400 data = Item.parser.parse_args() #print("####################", data) sql = db.text('SELECT (NVL(MAX(ID),0)+1) FROM ITEMS6') item_id = db.engine.execute(sql).fetchone() item = ItemModel(item_id[0], name, data["price"], data["store_id"]) #item_id[0] *** #ItemModel(name, data["price"]) <- {"name":name, "price":data["price"]} #because this is not a dictionary. Its a ItemModel object now. try: item.save_to_db() #now we directly insert item because item is now ItemModel class object. #ItemModel.insert(item) except: return {"message":"{} insertion failed.".format(name)}, 500 return item.json(), 201
def post(self, name): store = StoreModel.find_by_name(name) if store: return {"message": "{} store is already exist.".format(name)}, 400 sql = db.text('SELECT (NVL(MAX(ID),0)+1) FROM STORES6') my_store_id = db.engine.execute(sql).fetchone() store = StoreModel(my_store_id[0], name) try: store.save_to_db() except Exception as e: return { "message": "Unexpected error occured in Stroe insertion. please see the post method of store Resource." }, 500 return store.json(), 201
def post(self, name, date): store = StoreModel.find_by_name(name, date) #print("################## BEFORE CHECKING THE NAME...") if store: return {"message": "{} store is already exist.".format(name)}, 400 datetime_object = datetime.strptime(date, '%d-%m-%Y') #print("###################### ", datetime_object) data = request.get_json() print("######################### ", data) print("Type of data : ", type(data)) new_cus = { "cuscode": data["cuscode"], "age": data["age"], "height": data["height"], "lower_bp": data["lower_bp"], "higher_bp": data["higher_bp"] } print("######################### ", new_cus) sql = db.text('SELECT (NVL(MAX(SN),0)+1) FROM BP_INFO') my_store_id = db.engine.execute(sql).fetchone() store = StoreModel(new_cus["cuscode"], int(new_cus["age"]), int(new_cus["height"]), int(new_cus["lower_bp"]), int(new_cus["higher_bp"]), datetime_object.date(), my_store_id[0]) print(" ********************* ", store) try: store.save_to_db() except Exception as e: return { "message": "Unexpected error occured in Stroe insertion. please see the post method of store Resource." }, 500 return store.json(), 201
def post(self): data = UserRegister.parser.parse_args() if UserModel.find_by_username(data["username"]): return { "message": "{} is already exist".format(data["username"]) }, 400 sql = db.text('SELECT (NVL(MAX(ID),0)+1) FROM users6') user_master_id = db.engine.execute(sql).fetchone() #print("Type of id = ", type(user_master_id[0])) #print("***###",user_master_id[0]) user = UserModel( user_master_id[0], data["username"], data["password"] ) # UserModel(data["id"], data["username"], data["password"]) = UserModel(**data) user.save_to_db() return { "message": "{} is created successfully as your authentication user".format( data["username"]) }, 201
class SmartlistCandidate(db.Model): __tablename__ = 'smart_list_candidate' id = db.Column(db.Integer, primary_key=True) smartlist_id = db.Column('SmartlistId', db.Integer, db.ForeignKey('smart_list.Id', ondelete='CASCADE'), nullable=False) candidate_id = db.Column('CandidateId', db.BIGINT, db.ForeignKey('candidate.Id', ondelete='CASCADE'), nullable=False) added_time = db.Column('AddedTime', db.DateTime, server_default=db.text("CURRENT_TIMESTAMP")) updated_time = db.Column( 'UpdatedTime', db.DateTime, server_default=db.text( "CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"), nullable=False) # Relationships smartlist = db.relationship('Smartlist', backref=db.backref( 'smart_list_candidate', cascade="all, delete-orphan")) candidate = db.relationship('Candidate', backref=db.backref( 'smart_list_candidate', cascade="all, delete-orphan")) def __repr__(self): return "<SmartListCandidate> (id = {})".format(self.id) @classmethod @contract def get_smartlist_ids_in_talent_pools(cls, user_id, talentpool_names=None): """ This returns smartlist Ids in a pipeline which is in specified talentpool :param positive user_id: User Id :param list|None talentpool_names: Talent pool names :rtype: list """ talent_pools = TalentPool.get_by_user_id_and_name( user_id, talentpool_names) talent_pool_ids = [talent_pool.id for talent_pool in talent_pools ] # Extracting data on 0th index from tuple candidate_ids = TalentPoolCandidate.query.with_entities(TalentPoolCandidate.candidate_id). \ filter(TalentPoolCandidate.talent_pool_id.in_(talent_pool_ids)).distinct().all() """ candidate_ids is a list of tuple [(358L,), (1005L,), (1054L,), (1055L,)] when we zip it "zip(*candidate_ids)". It makes pairs of 1st-to-1st and 2nd-to-2nd elements of tuples. Since second element is empty so it gets skipped and candidate_ids changes into [(358L, 1005L, 1054L, 1055L)] And then we use * to extract elements of list of tuple and pass them to list() function and receive [358L, 1005L, 1054L, 1055L] """ candidate_ids = list(*zip(*candidate_ids)) # Converting tuple to list candidates = Candidate.get_by_id(candidate_ids, False) candidate_ids = [candidate.id for candidate in candidates] smartlist_ids = SmartlistCandidate.query.with_entities(SmartlistCandidate.smartlist_id). \ filter(SmartlistCandidate.candidate_id.in_(candidate_ids)).distinct().all() smartlist_ids = [smartlist_id[0] for smartlist_id in smartlist_ids ] # Extracting data on 0th index from tuple # Checking if any of the selected smartlists is hidden smartlists = Smartlist.get_by_ids(smartlist_ids, False) smartlist_ids = [smartlist.id for smartlist in smartlists] return smartlist_ids
def post(self): #, name, date #print("################## AFTER CHECKING THE NAME...") sql = db.text('SELECT (NVL(MAX(EMPNO),0)+1) FROM EMP') my_store_id = db.engine.execute(sql).fetchone() #print("###################### ", my_store_id) #datetime_object = datetime.strptime(date, '%d-%m-%Y') #datetime_object = datetime.strptime(date, '%d-%m-%Y') #print("###################### ", datetime_object) # data = Store.parser.parse_args() # print("NOW THE DATA IS : ",data) data = request.get_json() print("######################### ", data) hex_data = data["employee_data"] print("hex_data : ", hex_data) new_rnd_bytes = bytes.fromhex(hex_data) print("new_rnd_bytes : ", new_rnd_bytes) print(type(new_rnd_bytes)) ####################################################################### #### Decrypting data here ################################################# secret_key = 'shifullah1234567' # create new & store somewhere safe cipher1 = AES.new( secret_key, AES.MODE_ECB) # never use ECB in strong systems obviously byte_decoded = cipher1.decrypt(new_rnd_bytes) decoded = byte_decoded.strip().decode('utf-8') print("Decoded text is: ", decoded) print(type(decoded)) ####################################################################### #### Converting Decrypted data into Dictionary ###################################################################### text_to_dict = ast.literal_eval(decoded) print("###### text_to_dict : ", text_to_dict) print(type(text_to_dict)) new_store = { "HARCOD": text_to_dict["HARCOD"], "SOFCOD": text_to_dict["SOFCOD"], "CODDES": text_to_dict["CODDES"], "CORCOD": text_to_dict["CORCOD"], "AMOUNT": text_to_dict["AMOUNT"], "MANHRS": text_to_dict["MANHRS"], "RTDNON": text_to_dict["RTDNON"], "OPRSTAMP": text_to_dict["OPRSTAMP"], "TIMSTAMP": text_to_dict["TIMSTAMP"], "SECCOD": text_to_dict["SECCOD"], "ACTFLG": text_to_dict["ACTFLG"], "SEQNUM": text_to_dict["SEQNUM"], "INTRT_CHGFLG": text_to_dict["INTRT_CHGFLG"], "REQUIRE_FLG": text_to_dict["REQUIRE_FLG"], "ODRSRL": text_to_dict["ODRSRL"], "EFFDAT": text_to_dict["EFFDAT"] } print("######################### NEW Store is: ", new_store) name = new_store["HARCOD"] date = new_store["SOFCOD"] store = StoreModel.find_by_name(name, date) #print("################## BEFORE CHECKING THE NAME...") if store: return {"message": "{} store is already exist.".format(name)}, 400 #return {"message":"{} store is not found.".format(name)}, 400 print("##########################################################") print("new_store_TIMSTAMP: ", new_store["TIMSTAMP"]) print(type(new_store["TIMSTAMP"])) print("new_store_EFFDAT: ", new_store["EFFDAT"]) print(type(new_store["EFFDAT"])) ################################################################ # Date Value Handle For POST Method ################################################################ # if (new_store["EFFDAT"] == "None") or (new_store["TIMSTAMP"] == "None"): # my_TIMSTAMP = None # my_EFFDAT = None # else: TIMSTAMP_datetime_obj = datetime.strptime(new_store["TIMSTAMP"], '%d-%m-%Y') #'%d-%m-%Y' my_TIMSTAMP = TIMSTAMP_datetime_obj.date() EFFDAT_datetime_obj = datetime.strptime(new_store["EFFDAT"], '%d-%m-%Y') my_EFFDAT = EFFDAT_datetime_obj.date() # print(type(EFFDAT_datetime_obj), EFFDAT_datetime_obj) ################################################################### # Integer value Handle ################################################################### if (isinstance(new_store["AMOUNT"], int) == False) or (isinstance( new_store["MANHRS"], int) == False) or (isinstance( new_store["SEQNUM"], int) == False) or (isinstance( new_store["ODRSRL"], int) == False): my_AMOUNT = None my_MANHRS = None my_SEQNUM = None my_ODRSRL = None else: my_AMOUNT = int(new_store["AMOUNT"]) my_MANHRS = int(new_store["MANHRS"]) my_SEQNUM = int(new_store["SEQNUM"]) my_ODRSRL = int(new_store["ODRSRL"]) store = StoreModel(new_store["HARCOD"], new_store["SOFCOD"], new_store["CODDES"], new_store["CORCOD"], my_AMOUNT, int(new_store["MANHRS"]), new_store["RTDNON"], new_store["OPRSTAMP"], my_TIMSTAMP, new_store["SECCOD"], new_store["ACTFLG"], int(new_store["SEQNUM"]), new_store["INTRT_CHGFLG"], new_store["REQUIRE_FLG"], int(new_store["ODRSRL"]), my_EFFDAT) print(" ********************* Final Store is: ", store) #return {"message":"{} store save to final store.".format(name)}, 400 try: store.save_to_db() except Exception as e: return { "message": "Unexpected error occured in Stroe insertion. please see the post method of store Resource." }, 500 return store.json(), 201