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
