def get_data(self):
groups = Raspis.query \
.filter(Kontgrp.kont_id == request.args.get('kont_id')) \
.filter((Raspis.day - 1) % 7 + 1 == request.args.get('day')) \
.outerjoin(Auditory, Auditory.id == Raspis.aud_id) \
.outerjoin(Raspnagr, Raspnagr.id == Raspis.raspnagr_id) \
.outerjoin(Kontgrp, Kontgrp.id == Raspnagr.kontgrp_id) \
.outerjoin(Kontkurs, Kontkurs.id == Raspnagr.kontkurs_id) \
.outerjoin(Potoklist, Potoklist.op == Raspnagr.op) \
.with_entities(
Kontgrp.kont_id,
Raspis.day,
Raspis.para,
Auditory.id.label("auditory_id"),
func.rtrim(Auditory.title).label("auditory"),
func.rtrim(coalesce(Potoklist.title, Kontgrp.title, Kontkurs.title)).label("group")
) \
.order_by(Raspis.para)
result = [{
'kont_id': t.kont_id,
'day': t.day,
'para': t.para,
'auditory_id': t.auditory_id,
'auditory': t.auditory,
'group': t.group.strip()
} for t in groups]
return result
def test_filter_by_group_and_day(self):
"""
SELECT para, a.obozn as aud,coalesce(pl.konts, kg.obozn, kk.obozn) as kont, a.id_60
FROM raspis r
LEFT JOIN auditories a ON r.aud = a.id_60
LEFT JOIN raspnagr rn ON rn.id_51 = r.raspnagr
LEFT JOIN kontgrp kg ON kg.id_7 = rn.kontid
LEFT JOIN kontkurs kk ON kk.id_1 = rn.kont
LEFT JOIN potoklist pl ON pl.op = rn.op
WHERE (day-1)%7+1 = 5 AND kg.kont =22979
ORDER BY para
"""
schedule = Raspis.query \
.filter(Kontgrp.kont_id == 22979) \
.filter((Raspis.day - 1) % 7 + 1 == 1) \
.filter(
(Raspis.para == 5) | (Raspis.para == 4)) \
.outerjoin(Auditory, Auditory.id == Raspis.aud_id) \
.outerjoin(Raspnagr, Raspnagr.id == Raspis.raspnagr_id) \
.outerjoin(Kontgrp, Kontgrp.id == Raspnagr.kontgrp_id) \
.outerjoin(Kontkurs, Kontkurs.id == Raspnagr.kontkurs_id) \
.outerjoin(Potoklist, Potoklist.op == Raspnagr.op) \
.with_entities(
Raspis.para,
Auditory.id.label("auditory_id"),
func.rtrim(Auditory.title).label("auditory"),
func.rtrim(coalesce(Potoklist.title, Kontgrp.title, Kontkurs.title)).label("group")
) \
.order_by(Raspis.para)
print(schedule)
for item in schedule:
print(
f"Пара: {item.para} Аудитория: {item.auditory} Аудитория_id: {item.auditory_id} Группа: {item.group} "
)
def test_filter_by_teacher_and_day(self):
"""
SELECT para, a.obozn as aud, a.id_60
FROM raspis r
LEFT JOIN auditories a ON r.aud = a.id_60
LEFT JOIN raspnagr rn ON rn.id_51 = r.raspnagr
LEFT JOIN prepods p ON rn.prep = p.id_61
WHERE preps is not NULL AND preps = 'Бахвалова З.А.' AND (day-1)%7+1 = 1
ORDER BY para
"""
schedule = Raspis.query \
.filter(Teacher.name is not None) \
.filter(func.rtrim(Teacher.name) == "Бахвалова З.А.") \
.filter((Raspis.day - 1) % 7 + 1 == 5) \
.outerjoin(Auditory, Auditory.id == Raspis.aud_id) \
.outerjoin(Raspnagr, Raspnagr.id == Raspis.raspnagr_id) \
.outerjoin(Teacher, Raspnagr.prep_id == Teacher.id) \
.with_entities(
Raspis.para,
Auditory.id.label("auditory_id"),
func.rtrim(Auditory.title).label("auditory")
) \
.order_by(Raspis.para)
print(schedule)
for item in schedule:
print(
f"Пара: {item.para} Аудитория: {item.auditory} Аудитория_id: {item.auditory_id}"
)
def common_entites(self):
return [
func.rtrim(Teacher.full_name).label("teacher"),
func.rtrim(Discipline.title).label("discipline"),
func.rtrim(
func.coalesce(Potoklist.title, Kontgrp.title,
Kontkurs.title)).label("kont"),
Normtime.id.label("nt"),
]
def get_list(self):
query = Raspis.query \
.filter(Raspis.day == request.args.get('day')) \
.filter((Raspis.para == request.args.get('para')) | (Raspis.para == 4)) \
.filter(Kontgrp.kont_id is not None) \
.outerjoin(Auditory, Auditory.id == Raspis.aud_id) \
.outerjoin(Raspnagr, Raspnagr.id == Raspis.raspnagr_id) \
.outerjoin(Kontgrp, Kontgrp.id == Raspnagr.kontgrp_id) \
.outerjoin(Kontkurs, Kontkurs.id == Raspnagr.kontkurs_id) \
.outerjoin(Potoklist, Potoklist.op == Raspnagr.op) \
.with_entities(
Kontgrp.kont_id,
Raspis.day,
Raspis.para,
Auditory.id.label("auditory_id"),
func.rtrim(Auditory.title).label("auditory")
) \
.order_by(Raspis.para, Kontgrp.kont_id)
transitions = {}
transitions_list = []
for item in query:
if item.kont_id not in transitions:
transitions[item.kont_id] = []
transitions[item.kont_id].append(item.auditory)
for kont_id, auditories in transitions.items():
if (len(auditories) != 2):
continue
transitions_list.append({
'kont_id': kont_id,
'auditories': auditories
})
return (transitions_list)
def get_data(self):
teachers = Raspis.query \
.filter(Teacher.name is not None) \
.filter(Teacher.id == request.args.get('id'))\
.filter((Raspis.day - 1) % 7 + 1 == request.args.get('day')) \
.outerjoin(Auditory, Auditory.id == Raspis.aud_id) \
.outerjoin(Raspnagr, Raspnagr.id == Raspis.raspnagr_id) \
.outerjoin(Teacher, Raspnagr.prep_id == Teacher.id) \
.with_entities(
Raspis.para,
Raspis.day,
Teacher.id,
Auditory.id.label("auditory_id"),
func.rtrim(Auditory.title).label("auditory")
) \
.order_by(Raspis.para)
result = [{
'id': t.id,
'day': t.day,
'para': t.para,
'auditory_id': t.auditory_id,
'auditory': t.auditory.strip()
} for t in teachers]
return result
def test_filter_raspis_by_multiple_auds_with_specific_columns(self):
"""
Выведет данные по занятиями в аудитории с id 908 и 907 в понедельник
список полей занятия можно подсмотреть кликнув с Ctrl на Raspis
Получится такой запрос:
SELECT rtrim(auditories.obozn) AS auditory,
raspnagr.id_51 AS raspnagr_id,
raspis.day AS raspis_day,
raspis.para AS raspis_para,
rtrim(kontkurs.obozn) AS kont_title,
rtrim(vacpred.pred) AS discipline
FROM raspis
LEFT OUTER JOIN auditories ON auditories.id_60 = raspis.aud
LEFT OUTER JOIN raspnagr ON raspnagr.id_51 = raspis.raspnagr
LEFT OUTER JOIN kontkurs ON kontkurs.id_1 = raspnagr.kont
LEFT OUTER JOIN vacpred ON vacpred.id_15 = raspnagr.pred
WHERE raspis.aud IN (908, 907)
ORDER BY auditories.obozn, raspis.everyweek, raspis.day, raspis.para
"""
schedule = Raspis.query \
.filter(Raspis.aud_id.in_([908, 907])) \
.outerjoin(Auditory, Auditory.id == Raspis.aud_id) \
.outerjoin(Raspnagr, Raspnagr.id == Raspis.raspnagr_id) \
.outerjoin(Kontkurs, Kontkurs.id == Raspnagr.kontkurs_id) \
.outerjoin(Discipline, Discipline.id == Raspnagr.pred_id) \
.with_entities(
func.rtrim(Auditory.title).label("auditory"),
Raspnagr.id.label("raspnagr_id"),
Raspis.day,
Raspis.para,
func.rtrim(Kontkurs.title).label("kont_title"),
func.rtrim(Discipline.title).label("discipline"),
) \
.order_by(Auditory.title, Raspis.everyweek, Raspis.day, Raspis.para)
print(schedule)
for item in schedule:
print(
f"Аудитория {item.auditory} День: {item.day} Пара: "
f"{item.para} конитнгент: {item.kont_title} дисциплина: {item.discipline}"
)
def get_common_data(self):
Auditory2 = aliased(Auditory)
return RaspisZaoch.query \
.outerjoin(Raspnagr, Raspnagr.id == RaspisZaoch.raspnagr_id) \
.outerjoin(Auditory, RaspisZaoch.aud == Auditory.id) \
.outerjoin(Auditory2, RaspisZaoch.aud2 == Auditory2.id) \
.outerjoin(Teacher, Teacher.id == Raspnagr.prep_id) \
.outerjoin(Discipline) \
.outerjoin(Normtime) \
.outerjoin(Kontgrp, Raspnagr.kontgrp_id == Kontgrp.id) \
.outerjoin(Kontkurs, Raspnagr.kontkurs_id == Kontkurs.id) \
.outerjoin(Potoklist, Raspnagr.op == Potoklist.op) \
.order_by(RaspisZaoch.dt,
RaspisZaoch.para) \
.with_entities(
RaspisZaoch.id,
RaspisZaoch.raspnagr_id,
RaspisZaoch.dt,
RaspisZaoch.para,
RaspisZaoch.aud,
RaspisZaoch.aud2,
RaspisZaoch.kont_id,
RaspisZaoch.kontgrp_id,
RaspisZaoch.op_id,
RaspisZaoch.type,
RaspisZaoch.hours,
Auditory.maxstud,
Auditory2.maxstud.label('maxstud2'),
Raspnagr.prep_id,
Raspnagr.stud,
Raspnagr.nt,
func.rtrim(Normtime.title).label('normtime'),
func.rtrim(Teacher.name).label("teacher"),
func.rtrim(Auditory.title).label("auditory"),
func.rtrim(Auditory2.title).label("auditory2"),
func.rtrim(Discipline.title).label("discipline"),
func.coalesce(Potoklist.title, Kontgrp.title, Kontkurs.title).label('konts')
)
def serialize_column_expression(
self, column: sqlalchemy.sql.ColumnElement
) -> sqlalchemy.sql.ColumnElement:
if isinstance(self.sqlalchemy_type, sqlalchemy.types.Integer):
return cast(column, self.sqlalchemy_type)
elif isinstance(self.sqlalchemy_type, sqlalchemy.types.Date):
return func.date_format(column, literal_column("'yyyy-MM-dd'"))
elif isinstance(self.sqlalchemy_type, sqlalchemy.types.DateTime):
# TODO: Switch back to ISO date format when we rewrite this in Spark
return func.date_format(column,
literal_column("'yyyy-MM-dd HH:mm:ssX'"))
elif isinstance(self.sqlalchemy_type, sqlalchemy.types.Boolean):
return cast(column, self.sqlalchemy_type)
elif isinstance(self.sqlalchemy_type, sqlalchemy.types.String):
# TODO: Allow case-sensitive keys rather than forcing to uppercase?
return func.upper(func.ltrim(func.rtrim(column)))
else:
raise ValueError(f"No serializer defined for type {self.name}")
def city_description(self):
return func.rtrim(
self.continent + "/" + self.country + "/" + self.state + "/" +
self.city, '/')
def set_patient_num_to_excel(path_name,
file_name,
start_row_num,
type_mapping_birthday,
fam_col_index,
dr_col_index,
result_col_index,
worksheet_num=1,
field_name='num'):
# start_row = 2
start_row = start_row_num - 1 # нумерация с 0 !!!!!!!!!!!!!!!!!!
# type_mapping_birthday = MAPPING_BY_DATE
if isinstance(fam_col_index, list):
fam_col = column_index_from_string(fam_col_index[0])
im_col = column_index_from_string(fam_col_index[1])
ot_col = column_index_from_string(fam_col_index[2])
else:
fam_col = column_index_from_string(fam_col_index)
dr_col = column_index_from_string(dr_col_index)
result_col_history = column_index_from_string(result_col_index)
find_records_count = 0
file_full_name = os.path.join(path_name, file_name)
wb = openpyxl.load_workbook(file_full_name)
# ws = wb.active
ws = wb.worksheets[worksheet_num - 1]
if ws.max_column <= result_col_history:
ws.cell(1, result_col_history).value = ''
for row_num, row in enumerate(ws.iter_rows()):
if row_num < start_row:
continue
row_num += 1
year_bird = row[dr_col - 1].value
fam = None
im = None
ot = None
if isinstance(fam_col_index, list):
fam = row[fam_col - 1].value
im = row[im_col - 1].value
ot = row[ot_col - 1].value
if fam is None:
continue
if im is None:
continue
else:
fam_value = row[fam_col - 1].value
if fam_value is None:
continue
logging.warning(fam_value)
fio_list = fam_value.strip().split(' ', 1)
if len(fio_list) > 1:
fam = fio_list[0]
fam_value = fio_list[1]
fio_list = fam_value.strip().split(' ', 1)
im = fio_list[0]
if len(fio_list) > 1:
ot = fio_list[1]
else:
continue
logging.warning([fam, im, ot])
query = session_mis.query(HltMkabTable.num.label('num'),
HltMkabTable.ss.label('ss'))
query = query.filter(
func.rtrim(func.ltrim(HltMkabTable.family)) == fam.strip())
query = query.filter(
func.rtrim(func.ltrim(HltMkabTable.name)) == im.strip())
if ot is not None:
query = query.filter(
func.rtrim(func.ltrim(HltMkabTable.ot)) == ot.strip())
if type_mapping_birthday == MAPPING_BY_YEAR:
query = query.filter(HltMkabTable.date_bd >= datetime.date(
year=year_bird, month=1, day=1))
query = query.filter(HltMkabTable.date_bd <= datetime.date(
year=year_bird, month=12, day=31))
elif type_mapping_birthday == MAPPING_BY_DATE:
if isinstance(year_bird, str):
year_bird = datetime.datetime.strptime(year_bird, '%d.%m.%Y')
if isinstance(year_bird, datetime.datetime):
query = query.filter(HltMkabTable.date_bd == year_bird)
else:
logging.error('Ошибка в дате рождения, поиск не выполнен')
continue
recs = query.all()
logging.warning(recs)
if recs:
find_records_count += 1
ws.cell(row_num, result_col_history).value = recs[0][
recs[0]._fields.index(field_name)]
logging.warning(find_records_count)
wb.save(file_full_name)
def full_location(cls): ad = func.rtrim(func.ltrim(cls.property_location)) city = func.rtrim(func.ltrim(cls.city)) return func.concat(ad, ', ', city)
