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)