4 Разработка программного модуля
Используя знания об элементах языка VBA и объектной модели Excel, можно приступать к решению поставленной задачи – проектирование программного модуля, который будет выявлять накладки в расписании сессий.
В ходе разработки данного программного продукта были выделены этапы проектирования, которые формируют подробный фрагмент жизненного цикла, вплоть до реализации:
Анализ системы, и анализ требований.
Анализ системы, в которую будет внедрен модуль. Определение способа взаимодействия модуля с системой.
Определение требований к программному продукту.
Проектирование программного продукта.
Разработка алгоритмов, определение используемых типов данных, переменных и констант, для процедур.
Выделение компонентов программного продукта.
Разработка пользовательского интерфейса.
Кодирование.
Тестирование.
Тестирование процедур с помощью трассировки алгоритмов.
Тестирование процедур с помощью испытаний.
Сопровождение.
Написание документации программы (которая включена в данный отчет).
Написание руководства пользователя.
Оценка модели заказчиком.
Оценка эффективности программного продукта и рассмотрение перспектив модификаций.
Некоторые этапы могут быть объединены, другие – разделены, для более подробного описания, но, тем не менее, жизненный цикл требуемого программного продукта пройдет намеченный путь.
4.1 Анализ системы и требований
Согласно рисункам 1.1, 1.2 и 1.3 исходный xls файл имеет три вида таблиц – главная таблица, таблица сессий и таблица пересдач. Отсюда следует вывод, что, возможно, потребуется создать три процедуры, для каждой таблицы, ввиду того, что у таблиц разная структура, а для главной таблицы, возможно, понадобится элемент управления, который наполнит ее всеми записями из всех необходимых таблиц.
Исходя из требований к проекту, программа должна четко выявлять накладки, по разным критериям. То есть, надежная и безошибочная работа программы – основное требование, которое будет выполнено, путем разработки безошибочных алгоритмов.
4.2 Проектирование программного продукта
На данном этапе выполняется основная работа по созданию программного продукта – разработка архитектуры системы, выделение в ней процедур, вспомогательных функций и разработка алгоритмов.
4.2.1 Разработка алгоритмов, определение используемых типов данных, переменных и констант, для процедур
Как было упомянуто в пункте 4.1, вероятно, потребуется делать три процедуры, для разных видов таблиц. Однако данный подход противоречит хорошему стилю программирования – следует сделать одну параметризированную процедуру, которая может изменять свое поведение, в зависимости от параметров, а процедура главной таблицы должна будет вызывать эту процедуру с разными параметрами для своих подотчетных таблиц. Такой подход упростит отладку программы.
Итак, требуется разработать параметризированную процедуру выявления накладок для таблиц. Проще создать процедуру, которая будет заполнять главную таблицами записями определенной таблицы, чем создавать один большой алгоритм для проверки всех таблиц.
Разработанная процедура обладает следующим алгоритмом:
Посчитать количество преподавателей в таблице «Всего по кафедрам».
Для каждого преподавателя, в таблице «Всего по кафедрам» найти соответствие по ФИО в таблице, откуда вызвана процедура.
В случае находки соответствия, запомнить дату проведения экзамена, и найти соответствие даты в таблице «Всего по кафедрам».
Если соответствие найдено, то ищется свободная ячейка в соответствующем столбце, для соответствующего преподавателя, заодно проверяя, не занят ли преподаватель другим экзаменом в это же время. Если соответствия не найдено, то дата помечается красным, и алгоритм продолжает работу.
Если свободной ячейки не найдено – генерируется ошибка, иначе свободная ячейка заполняется экзаменом.
Проверяется весь столбец, на наличие коллизий, с помощью процедуры «ПроверкаНакладок».
Алгоритм процедуры проверки накладок имеет два входных параметра – это номер столбца, который нужно проверить и количество строк в таблице «Всего по кафедрам»:
Посчитать количество непустых ячеек в столбце.
Если количество непустых ячеек равно нулю, то выход из процедуры, иначе, выделяется память для массива пользовательского типа, с количеством ячеек равным количества непустых ячеек.
Пользовательский массив заполняется данными из непустых ячеек.
Находится минимальное и максимальное время проведения сессий.
Итератор изменяется от минимального значения времени, до максимального, увеличиваясь на час в каждой итерации. Значение итератора сравнивается со значением времени каждого элемента, и в случае соответствия – элемент подвергается сортировке.
В отсортированном, по времени, массиве, можно выделить группы однородных, по времени, объектов. Эти группы сортируются внутри себя, сначала по аудиториям, затем по потокам, и если внутри одной временной группы есть две одинаковые аудитории, или два одинаковых потока – происходит коллизия.
Вышеперечисленные алгоритмы детально описаны с помощью R-схем в приложении В.
4.2.2 Выделение компонентов программного продукта
Изначально, проект создавался в одном модуле, в котором были и процедуры, алгоритмы которых описаны выше, и константы, которые в них используются, и процедуры, которые используются для вызова этих процедур.
Однако этот подход превращает, даже такую скромную систему, в монолит. Стоит выделить в этой монолитной системе компоненты, что позволит упростить сопровождение продукта.
В результате анализа процедур, было выделено три модуля (рисунок 4.1):
- модуль процедур, выполняющих поставленную задачу (ядро);
- модуль процедур, вызывающих процедуры ядра (интерфейс);
- модуль констант, для настройки программы.
4.2.3 Разработка пользовательского интерфейса
Согласно требованиям заказчика, необходимо, чтоб заполнялась таблица «Всего по кафедрам» из остальных таблиц, и выявлялись накладки. Можно обеспечить выполнение соответствующих процедур двумя способами:
Разместить на каждой таблице кнопку для заполнения данных из этой таблицы.
Разместить на таблице «Всего по кафедрам» кнопку, которая заполнит эту таблицу всеми данными.
Оба способа имеют свои достоинства и недостатки. Если разместить на каждой таблице кнопку для вызова процедуры, то это может быть неудобным, когда нужно заполнить таблицу «Всего по кафедрам» полностью.
Рисунок 4.1 – Диаграмма компонентов системы
С другой стороны, если поместить кнопку на таблице «Всего по кафедрам», тогда пользователь будет лишен возможности проверять накладки исключительно для определенной таблицы.
Исходя из этой дилеммы, было принято решение: поместить кнопку вызова процедуры на каждой таблице, в том числе и на таблице «Всего по кафедрам», с той разницей, что та кнопка, по очереди вызывает процедуры от остальных кнопок (рисунок 4.2)
Кнопки, расположенные в таблицах «Пересдача» и «Пересдача (комиссии)» вызывают процедуру из модуля интерфейсов:
Sub ВызовДиспетчераДляПересдач()
ГенерацияОтчета 1, 2, 6, 3, 4, 5, 6, 20
End Sub
А кнопка в таблице «Расписание» вызывает другую процедуру, которая, в свою очередь, вызывает процедуру из ядра, с указанием других параметров:
Sub ВызовДиспетчераДляСдач()
ГенерацияОтчета 6, 7, 6, 3, 4, 5, 2, 20
End Sub
Рисунок 4.2 – Кнопки, для заполнения таблицы «Всего по кафедрам» из текущей таблицы
На рисунке 4.3 показаны две кнопки: одна кнопка, вызывает процедуру заполнения для всех таблиц («Заполнить»), а другая – очищает цвет ячеек для всех таблиц, и очищает рабочее поле таблицы «Всего по кафедрам» («Очистить»).
Рисунок 4.3 – Кнопки в таблице «Всего по кафедрам»
4.3 Кодирование
Процесс кодирования тесно сплетен с процессом проектирования. Ввиду того, что на разработку проекта были поставлены малые сроки, активно применялись парадигмы экстремального программирования, что значительно ускорило процесс написания исходного кода, командой из двух человек: код постоянно перерабатывался (принцип «рефакторинг»), релизы были короткими – интервал: два дня (принцип коротких интервалов), тестирование происходило сразу после написания процедур.
4.4 Сопровождение
Теперь, когда проект готов, следует написать соответствующую документацию, которая включает в себя данный отчет и руководство пользователя.
Руководство пользователя написано в приложении А.
4.4.1 Оценка эффективности программного продукта и рассмотрение перспектив модификаций
Испытания процедур показали, что обход ячеек таблиц занимает длительное время, что снижает эффективность программы. В дальнейшем, возможно, полное копирование данных из таблиц в виртуальную память, для быстрого доступа к этим данным. А так же – активное использование свойств Offset и конструкций With –End With.
Более того, программе не хватает диалоговых окон для предварительной настройки программы:
- настройка цветов ошибок;
- настройка параметров функций ядра;
- улучшение алгоритмов, использование более эффективных методов сортировки.
ВЫВОДЫ
Одних познаний в объектной модели Excel и элементов языка VBA недостаточно, для осуществления проекта на профессиональном уровне. Полученная программа, является результатом некого симбиоза дисциплин, которые изучались, по сей день:
- познания в теории алгоритмов позволили быстро соорудить рабочий алгоритм, который выполняет поставленную задачу;
- программная инженерия обеспечивает правильный стиль разработки – программа, из монолитной системы, превратилась в компонентную, а стиль написания кода, в том числе и правила именований переменных и констант, позволили создать удобочитаемый и легкосопровождаемый код;
- знание инженерных принципов создания ПО обеспечили комфортное, как для разработчиков, так и для заказчика, взаимодействие. Более того, применение парадигм технологий конструирования ПО систематизировало процесс разработки проекта, и применение методологий экстремального программирование повысило эффективность работы команды разработчиков;
- с помощью теории баз данных было выполнено преобразование отношения исходного объекта в более удобное, для анализа, что привело к упрощению обработки существующей базы данных;
- юзабилити, как подраздел эргономики, обеспечило создание удобного, для пользователя, интерфейса;
- элементы системного анализа, которые преподаются в дисциплине «Алгоритмы и структуры данных» позволили оценить эффективность полученных алгоритмов, более того, рассмотреть перспективу модификации алгоритмов, с целью повышения скорости работы программы.
Исходя из вышеописанного, следует прийти к выводу, что в ходе обучения были приобретены ценные навыки и знания, которые позволяют создавать эффективное, удобное, востребованное и легкосопровождаемое ПО.
ПРИЛОЖЕНИЕ А. РУКОВОДСТВО ПОЛЬЗОВАТЕЛЯ
Данный документ описывает способы взаимодействия с созданным продуктом. Данный программный продукт имеет только одну функциональность – заполнение таблицы «Всего по кафедрам» с выявлением накладок.
Однако использовать результаты проекта можно разными способами.
Первый способ заключается в использовании кнопки «Заполнить» в таблице «Всего по кафедрам», что приведет к вызову процедур из таблиц «Расписание», «Пересдача» и «Пересдача (комиссии)», предварительно зачистив рабочие поля таблиц. Следует заметить, что в случае переименования таблиц, процедура, закрепленная за кнопкой «Всего по кафедрам» перестанет функционировать. С обратной стороны, переименование таблицы «Всего по кафедрам» приведет к неработоспособности процедур ядра разработанных модулей. Эти и некоторые другие вопросы обсуждаются в настоящем руководстве.
А.1 Запуск процедур
Для запуска процедур из файла xls, который содержится в поставке, достаточно нажать кнопку «Заполнить таблицу «Всего по кафедрам»» в любой из перечисленных таблиц: «Расписание», «Пересдача», «Пересдача (комиссии)» (рисунок А.3).
Эти кнопки заполняют таблицу «Всего по кафедрам», без предварительной очистки последней, данными из таблиц, в которой находилась кнопка, вызвавшая процедуру заполнения.
Факт того, что кнопки не очищают таблицу «Всего по кафедрам» указывает на то, что можно дважды вызвать процедуру заполнения таблицы «Всего по кафедрам», и тогда возникнет столько накладок, столько записей в таблице, в которой дважды вызвана процедура заполнения таблицы минус количество преподавателей, которые есть в графике сессий, но нету в таблице «Всего по кафедрам» (рисунок А.1, А.2). Этот прием позволяет выявить тех преподавателей, которые не указаны в таблице «Всего по кафедрам» или преподавателей, ФИО которых написано некорректно.
Рисунок А.1 – Двойной вызов процедуры заполнения выявил, что доц.Ходикіна В.В. отсутствует в таблице «Всего по кафедрам»
Рисунок А.2 – Встроенный фильтр MS Excel подтвердил результат
Рисунок А.3 – Кнопки, для заполнения таблицы «Всего по кафедрам» из текущей таблицы
Также предоставляется возможность сразу заполнить таблицу «Всего по кафедрам», с помощью кнопки «Заполнить» в таблице «Всего по кафедрам» (рисунок А.4). Сначала очищается рабочее поле таблицы «Всего по кафедрам», затем очищается цвет ячеек всех таблиц, и поочередно вызываются процедуры для таблиц «Расписание», «Пересдача» и «Пересдача (комиссии)».
Рисунок А.4 – Элементы управления таблицы «Всего по кафедрам»
Кнопка «Очистить», очищает рабочее поле таблицы «Всего по кафедрам», и очищает цвета ячеек всех таблиц.
А.2 Возможные ошибки, и их исправление
Так как данный проект представляет собой первую версию программы, существуют ситуации, которые могут привести к сбою программы, и лишить ее работоспособности. Данный раздел описывает возможные ситуации, которые приведут к сбоям, и способы их решения.
А.2.1 Переименование таблиц
Как было упомянуто выше, в случае переименования таблиц, процедура, прикрепленная к кнопке «Заполнить» перестанет функционировать.
На рисунке А.5 продемонстрирован результат нажатия кнопки «Заполнить», при переименовании таблицы «Расписание» в «Расписание1».
В текущей поставке отсутствует возможность настройки программы с помощью оконного интерфейса, следовательно, для исправления этой ошибки, следует нажать Debug, и в выделенной строке переименовать таблицу (Рисунок А.6). После этого, следует нажать квадрат, на панели вверху (стоп), что остановит выполнение кода, и закрыть редактор кода.
После приведенных операций, можно снова запустить процедуру, и убедиться, что все работает (Рисунок А.7).
Рисунок А.5 – Ошибка процедуры, в случае переименования рабочих таблиц
→
Рисунок А.6 – Исправление ошибки неправильного имени таблицы
Рисунок А.7 – Программа работает корректно
А.2.2 Некорректное выделение полей
В результате корректировки структур таблиц, может произойти ситуация, изображенная на рисунке А.8.
Рисунок А.8 –Некорректное выделение полей
В таком случае необходимо обратиться к модулю интерфейсов, и изменить параметры вызова процедур (рисунок А.9).
→
Рисунок А.9 – Смена некорректных параметров вызова процедуры
В результате этих действий, программа снова работает корректно (рисунок А.10).
Рисунок А.10 – Результат корректировки параметров
Следует отметить, что некорректное задание полей приводит к некорректному анализу накладок, следовательно, приводит к ошибочной работе программы.
Так же, можно создать собственную кнопку, для вызова разработанной процедуры обработки специфической таблицы. Пусть в таблице будет 20 полей, если среди них будут корректные поля: ФИО, дата, время, аудитория, вид экзамена, дисциплина, то можно написать новый интерфейс, задав корректные параметры вызова, и процедура ядра будет правильно функционировать.
ПРИЛОЖЕНИЕ Б. ЛИСТИНГ МОДУЛЕЙ
Модуль мИнтерфейсы
'Этот модуль содержит процедуры, для вызова процедур обработки данных, в модулях, в котором выполняется сортировка, поиск накладок.
Option Explicit
Sub ВызовДиспетчераДляПересдач()
ГенерацияОтчета 1, 2, 3, 4, 5, 6, 6, 20
End Sub
Sub ВызовДиспетчераДляСдач()
ГенерацияОтчета 6, 7, 6, 3, 4, 5, 2, 20
End Sub
Sub ВызовСортировка()
Сортировка 6, 100
End Sub
Модуль мКонстанты
' Этот модуль содержит константы, для модулей:
' Константы, для настройки программы, в случае изменения структуры таблиц.
Public Const GLOBAL_FIO_ROW = 2 ' Строка, в которой записан первый преподаватель, в глобальной таблице.
Public Const GLOBAL_FIO_COLLUMN = 2 ' Столбец, в котором находятся преподаватели.
Public Const GLOBAL_STEP = 4 ' Максимальное количество экзаменов в день.
Public Const GLOBAL_TAB_NAME = "Всего по кафедрам" ' Имя главной таблицы.
Public Const TRACE_TAB_NAME = "Trace" ' Имя таблицы, в которой будет отображен результат работы алгоритма проверки на накладки. Позволяет, по желанию пользователя, отследить работу алгоритма на таблице.
Public Const GLOBAL_DATE_COLLUMN = 4 ' Столбец, с которого начинаются даты.
Public Const GLOBAL_DATE_ROW = 1 ' Строка, с которой начинаются даты.
Public Const max_holes = 50 ' Максимальное количество пустых строк между групп.
Модуль-ядро мCollisioner
'Option Explicit
Type TSession
iRow As Integer
strTime As String * 5
strRoom As String * 5
strGroup As String * 8
End Type
Sub ГенерацияОтчета(LOCAL_SUBJECT_COLLUMN As Integer, _
LOCAL_FIO_COLLUMN As Integer, _
LOCAL_DATE_COLLUMN As Integer, _
LOCAL_TIME_COLLUMN As Integer, _
LOCAL_ROOM_COLLUMN As Integer, _
LOCAL_TYPE_COLLUMN As Integer, _
LOCAL_START_ROW As Integer, _
LOCAL_MAX_HOLES As Integer)
' Основные переменные:
Dim nGlobalEmptyCount As Integer ' Счетчик пустых ячеек в таблице по кафедрам. Делает цикл обхода по глобальной таблице гибким. Цикл завершает работу, когда обнаруживаются четыре неиспользованные ячейки (для надежности).
Dim nLocalEmptyCount As Integer ' Счетчик пустых ячеек в текущей таблице.
Dim iGlobalTabWorker As Integer ' Переменная запоминает строку, в которой находится нужный сотрудник, в глобальной таблице.
Dim nGlobalRows As Integer 'Kоличество строк в таблице "Всего по кафедрам".
Dim nGlobalCollumns As Integer ' Kоличество столбцов в таблице "Всего по кафедрам".
Dim iLocalTabWorker As Integer ' Переменная запоминает строку, в которой находится нужный сотрудник, в локальной таблице (таблица экзаменов).
Dim iCount As Integer ' Счетчик ячеек, выделенных преподавателю.
Dim GlobalTime As Integer ' Время из ячейки глобальной таблицы, для поиска коллизий.
' Временные строки созданы для придания гибкости алгоритму,т.к. некоторые дисциплины, ФИО и т.п. растягиваются на две ячейки, при этом считается, что данные записаны только в первой ячейке, а остальные- пустые.
Dim strTmpFio As String ' Временная строка, для хранения фио преподавателя.
Dim strTmpGroup As String ' Временная строка, для хранения группы (последней попавшейся в таблице, используя свойство, описанное выше).
Dim strTmp() As String ' Временные строки, для получения времени, аудитории и группы, с помощью функции Split.
Dim bOccupied As Boolean ' Флаг, который указывает, что все ячейки преподавателя заняты.
Dim bCollision As Boolean ' Флаг, который указывает, что произошла накладка.
nGlobalEmptyCount = 0
nLocalEmptyCount = 0
nGlobalRows = GLOBAL_FIO_ROW
nGlobalCollumns = 4
iGlobalTabWorker = GLOBAL_FIO_ROW
iLocalTabWorker = LOCAL_START_ROW
CurrentSheet = ActiveSheet.Name
Application.StatusBar = "Начало работы"
If CurrentSheet = GLOBAL_TAB_NAME Then
' Если как-то получилось, что активная таблица - главная, то это ошибка и функция завершит свою работу.
Application.StatusBar = "Ошибка."
Exit Sub
End If
' В цикле выполняется подсчет ячеек преподавателей.
Application.StatusBar = "Подсчет ячеек."
Do While (nGlobalEmptyCount < 4)
If Worksheets(GLOBAL_TAB_NAME).Cells(nGlobalRows, GLOBAL_FIO_COLLUMN) = Empty Then
nGlobalEmptyCount = nGlobalEmptyCount + 1
Else:
nGlobalEmptyCount = 0
End If
nGlobalRows = nGlobalRows + GLOBAL_STEP
Loop
' Убирается лишний запас, ввиду особенности алгоритма.
nGlobalRows = nGlobalRows - GLOBAL_STEP * 4
' В цикле выполняется подсчет ячеек с датами.
Do While Worksheets(GLOBAL_TAB_NAME).Cells(GLOBAL_DATE_ROW, nGlobalCollumns) <> Empty
nGlobalCollumns = nGlobalCollumns + 1
Loop
nGlobalCollumns = nGlobalCollumns - 1
' Вход в основной цикл.
Application.StatusBar = "Обработка данных. Поиск накладок."
' Обход по рядам глобальной таблицы, начиная от первого преподавателя, с шагом 4 (т.е по каждому преподавателю проходим).
For iGlobalRows = GLOBAL_FIO_ROW To nGlobalRows Step 4
' Начинается обход по строкам с некой стартовой позиции. Для каждой таблицы приходится указывать свою стартовую строку. Это необходимо, чтоб обработать единый массив данных, т.к. обработка завершается, если есть пустые ячейки.
iLocalTabWorker = LOCAL_START_ROW
nLocalEmptyCount = 0
' Обход локальной таблицы для каждого преподавателя из внешнего цикла.
Do While nLocalEmptyCount < max_holes
Application.StatusBar = VBA.CStr(iGlobalTabWorker) + " " + Worksheets(GLOBAL_TAB_NAME).Cells(iGlobalTabWorker, GLOBAL_FIO_COLLUMN)
' Если первая ячейка пустует, значит, вся строка пустая, т.к. согласно свойствам таблиц - в первой ячейке всегда есть какое-то значение: толи это группа (как озаглавие группы записей, толи какая-то информация.
If Worksheets(CurrentSheet).Cells(iLocalTabWorker, 1) = Empty Then
nLocalEmptyCount = nLocalEmptyCount + 1
Else:
nLocalEmptyCount = 0
End If
' Если ячейка с фамилией не пуста - сохранить ее в временной переменной. Это необходимо, т.к. зачастую фамилии растягиваются на две строки, но при этом, в программе считается, что вторая строка пуста.
If Worksheets(CurrentSheet).Cells(iLocalTabWorker, LOCAL_FIO_COLLUMN) <> Empty Then
strTmpFio = Worksheets(CurrentSheet).Cells(iLocalTabWorker, LOCAL_FIO_COLLUMN)
End If
' Произвольно выбранный элемент сообщает своей пустотой, что текущая строка отображает группу. Ее тоже полезно запомнить.
If Worksheets(CurrentSheet).Cells(iLocalTabWorker, LOCAL_DATE_COLLUMN) = Empty Then
strTmpGroup = Worksheets(CurrentSheet).Cells(iLocalTabWorker, 1)
End If
' Если ФИО преподавателя в локальной таблице совпадает с ФИО преподавателем в глобальной таблице, значит, пора искать соответствующий стоблец даты.
If Worksheets(GLOBAL_TAB_NAME).Cells(iGlobalTabWorker, GLOBAL_FIO_COLLUMN) = strTmpFio Then
Dim iDateCollumn As Integer
' Поиск соответствующей даты.
For iDateCollumn = GLOBAL_DATE_COLLUMN To nGlobalCollumns
If Worksheets(GLOBAL_TAB_NAME).Cells(GLOBAL_DATE_ROW, iDateCollumn) = Worksheets(CurrentSheet).Cells(iLocalTabWorker, LOCAL_DATE_COLLUMN) Then
Exit For
End If
Next iDateCollumn
' Если соответсвующая дата не найдена, то
If iDateCollumn - 1 = nGlobalCollumns Then
' Перекрашиваем в цвет ошибки выхода за пределы дат.
Worksheets(CurrentSheet).Cells(iLocalTabWorker, LOCAL_DATE_COLLUMN).Interior.Color = RGB(255, 100, 100)
' Вместо Continue в С
GoTo NextIteration
End If
' Далее, в указанном столбце дат, ищется свободная ячейка для преподавателя, и выполняется локальная проверка на накладки (то есть проверяется нагрузка на преподавателя).
bOccupied = True
bCollision = False
For iCount = 0 To 3
' Если есть пустая ячейка.
If Worksheets(GLOBAL_TAB_NAME).Cells(iGlobalTabWorker + iCount, iDateCollumn) = Empty Then
' То есть место, куда можно записать данные, следовательно, можно добавить новую нагрузку.
bOccupied = False
Exit For
Else: ' Ячейка занята другим экзаменом.
' Нужно проанализировать время экзамена в ячейке, и то, которое будет введено.
strTmp = Split(Worksheets(GLOBAL_TAB_NAME).Cells(iGlobalTabWorker + iCount, iDateCollumn))
Dim str As String
' Если новое время экзамена совпадает со временем, указанным в занятой ячейке, значит преподаватель, согласно этим данным, должен проводить два экзамена в одно время.
If strTmp(2) = Worksheets(CurrentSheet).Cells(iLocalTabWorker, LOCAL_TIME_COLLUMN) Then
bCollision = True
' Если накладка есть, то время закрашивается в таблице, откуда взяты данные.
Worksheets(CurrentSheet).Cells(iLocalTabWorker, LOCAL_TIME_COLLUMN).Interior.Color = RGB(255, 100, 100)
End If
End If
Next iCount
If bOccupied = True And iCount = 3 Then
MsgBox "Преподаватель" + Worksheets(CurrentSheet).Cells(iLocalTabWorker, LOCAL_FIO_COLLUMN) + "перегружен.", vbOKCancel, "Предупреждение"
Else:
' VBA.CStr - решение против ошибки приведения типа. Присваивание не срабатывает, если в куче заносятся и числа и строки.
Worksheets(GLOBAL_TAB_NAME).Cells(iGlobalTabWorker + iCount, iDateCollumn) = strTmpGroup + " " + vbCrLf + "в " + CStr(Worksheets(CurrentSheet).Cells(iLocalTabWorker, LOCAL_TIME_COLLUMN)) + " " + vbCrLf + "ауд " + CStr(Worksheets(CurrentSheet).Cells(iLocalTabWorker, LOCAL_ROOM_COLLUMN)) + " - " + CStr(Worksheets(CurrentSheet).Cells(iLocalTabWorker, LOCAL_TYPE_COLLUMN))
End If
End If ' end If Worksheets(GLOBAL_TAB_NAME).Cells(iGlobalTabWorker, GLOBAL_FIO_COLLUMN) = strTmpFio
' Вместо Continue в С
NextIteration:
iLocalTabWorker = iLocalTabWorker + 1
Loop ' end Do While nLocalEmptyCount < MAX_HOLES
' End If 'If Worksheets(GLOBAL_TAB_NAME).Cells(iGlobalTabWorker, GLOBAL_START) = Empty Then
iGlobalTabWorker = iGlobalTabWorker + GLOBAL_STEP
Next iGlobalRows
' После заполнения всей таблицы - вызываются процедуры выявления накладок.
For iGlobalCollumns = GLOBAL_DATE_COLLUMN To nGlobalCollumns
Application.StatusBar = CDate(Worksheets(GLOBAL_TAB_NAME).Cells(GLOBAL_DATE_ROW, iGlobalCollumns))
ПроверкаНакладок iGlobalCollumns, nGlobalRows
Next iGlobalCollumns
Application.StatusBar = "Готово."
End Sub
Function Парсер(Data As String) As Integer
' Функция, которая получает из строки две первые и две последние цифры. Функция строго заточена под получение дат.
Dim Answer As Integer
Dim tmpData As String
tmpData = Data
Answer = Val(tmpData)
Answer = Answer * 100
tmpData = Right(tmpData, 2)
Answer = Answer + Val(tmpData)
Парсер = Answer
End Function
Sub ПроверкаНакладок(iCollumn, nRows As Integer)
' Первое, что нужно сделать - преобразовать кортежи таким образом, чтоб с ними можно было работать. Данные будут храниться в оперативной памяти, в виде пользовательской структуры, следующего вида:
' <Данные> => <НомерСтроки,ВремяСдачи,Аудитория,Группа>.
' Далее, выполняется сортировка по времени сдачи.
' В результате сортировки - все данные разгруппируются, и внутри каждой группы выполняется сортировка по аудиториям, для выяснения накладок по аудитории, и тоже самое с группами.
Dim nSession As Integer ' Количество строк в новой таблице.
Dim pstrParser() As String ' Используется, как хранилище, для результатов работы функции Split.
Dim Session() As TSession ' Пустой массив, динамически определяется его размер.
Dim iSession As Integer ' Индексатор, для массива Сессия.
Dim bTab As Boolean ' Переменная, отвечающая за доступность дополнительной, трассировочной таблицы.
Dim iRows As Integer
' Внимание. Следующие константы работают только в текущей конфигурации записи в ячейку.
Const SPLIT_GROUP = 0 ' Индекс строки в массиве строк, полученных с помощью функции Сплит, который указывает на группу.
Const SPLIT_TIME = 2 ' Индекс времени.
Const SPLIT_ROOM = 4 ' Индекс аудитории.
bTab = False
' Поиск трассирочной таблицы.
For Each Item In Worksheets
If Item.Name = TRACE_TAB_NAME Then
bTab = True
' В случае нахождения трассировочной таблицы - очистить ее содержимое.
Item.Range("A1:av1000").Clear
End If
Next Item
If bTab = False Then
' Если таблицы нет - активировать глобальную таблицу.
Worksheets(GLOBAL_TAB_NAME).Activate
End If
' Подсчет непустых ячеек указанного столбца главной таблицы.
nSession = 0
With Worksheets(GLOBAL_TAB_NAME)
For iRows = GLOBAL_FIO_ROW To nRows
If .Cells(iRows, iCollumn) <> Empty Then
nSession = nSession + 1
End If
Next iRows
' Если в столбце нет данных - выход из функции.
If nSession = 0 Then
Exit Sub
End If
' Выделяется память под массив объектов Сессия и заполняется данными из таблицы.
ReDim Session(nSession)
For iRows = GLOBAL_FIO_ROW To nRows
If .Cells(iRows, iCollumn) <> Empty Then
pstrParser = Split(.Cells(iRows, iCollumn))
Session(iSession).iRow = iRows
Session(iSession).strRoom = pstrParser(SPLIT_ROOM)
Session(iSession).strTime = pstrParser(SPLIT_TIME)
Session(iSession).strGroup = pstrParser(SPLIT_GROUP)
iSession = iSession + 1
End If
Next iRows
End With
' Заполнение трассировочной таблицы.
If bTab = True Then
iRows = GLOBAL_FIO_ROW
With Worksheets(TRACE_TAB_NAME)
For iSession = 0 To nSession - 1
.Cells(iRows, 1) = Session(iSession).iRow
.Cells(iRows, 2) = Session(iSession).strRoom
.Cells(iRows, 3) = Session(iSession).strTime
.Cells(iRows, 4) = Session(iSession).strGroup
iRows = iRows + 1
Next iSession
.Cells(1, 1) = "Исходные данные"
End With
End If
' Сортировка данных по времени, с помощью функции Парсер.
Dim Min As Integer
Dim Max As Integer
Dim iSort As Integer
Dim iTime As Integer
Min = 9999
Max = 0
For iSession = 0 To nSession - 1
If Парсер(Session(iSession).strTime) < Min Then
Min = Парсер(Session(iSession).strTime)
End If
If Парсер(Session(iSession).strTime) > Max Then
Max = Парсер(Session(iSession).strTime)
End If
Next iSession
iSort = 0
Dim Tmp As TSession
' Поиск экзаменов, начиная от минимального времени, увеличиая образец на час. Например 9.00 10.00 11.00. Алгоритм для альфа-релиза.
For iTime = Min To Max Step 100
' Для образчика времени, выполняется проход по всему массиву.
For iSession = 0 To nSession - 1
' Если значение в ячейке совпадает с образцом, то
If Парсер(Session(iSession).strTime) = iTime Then
' скопировать в временную переменную текущую ячейку, в алгоритме сортировки
Tmp = Session(iSort)
' в текущую ячейку занести подходящую ячейку
Session(iSort) = Session(iSession)
' А в найденную ячейку заносится та ячейка, которая находилась в текущей ячейке алгоритма
Session(iSession) = Tmp
iSort = iSort + 1
End If
Next iSession
Next iTime
' Таким образом, ячейки постепенно выстраиваются, подобно сортировке игральных карт в руке.
If bTab = True Then
iRows = GLOBAL_FIO_ROW
With Worksheets(TRACE_TAB_NAME)
For iSession = 0 To nSession - 1
.Cells(iRows, 6) = CStr(Session(iSession).iRow)
.Cells(iRows, 7) = Session(iSession).strRoom
.Cells(iRows, 8) = Session(iSession).strTime
.Cells(iRows, 9) = Session(iSession).strGroup
iRows = iRows + 1
Next iSession
.Cells(1, 6) = "Сортировка по времени"
End With
End If
' Сортировка по группам. Ищется блок (группировка) по времени, и по этому блоку происходит сортировка по потокам.
Dim iLowBorder As Integer
Dim iHighBorder As Integer
iSession = 0
Do
' Выделение группы
iLowBorder = iSession
Do
iSession = iSession + 1
Loop While Session(iSession).strTime = Session(iSession - 1).strTime
iHighBorder = iSession - 1
' Обход по группе. Далее, аналогично с предыдущим алгоритмом.
Do While iLowBorder < iHighBorder
For iSort = iLowBorder + 1 To iHighBorder
If Session(iSort).strGroup = Session(iLowBorder).strGroup Then
iLowBorder = iLowBorder + 1
Tmp = Session(iSort)
Session(iSort) = Session(iLowBorder)
Session(iLowBorder) = Tmp
End If
Next iSort
iLowBorder = iLowBorder + 1
Loop
Loop While iSession < nSession
' Отсортировав по потокам внутри временных групп - выявляем накладки - в одной временной группе каждая группа не может встречаться больше одного раза.
iSession = 0
Do
iLowBorder = iSession
Do
iSession = iSession + 1
Loop While Session(iSession).strTime = Session(iSession - 1).strTime
iHighBorder = iSession - 1
Do While iLowBorder < iHighBorder
iLowBorder = iLowBorder + 1
If Session(iLowBorder).strGroup = Session(iLowBorder - 1).strGroup Then
' Красим клетку
Worksheets(GLOBAL_TAB_NAME).Cells(Session(iLowBorder).iRow, iCollumn).Interior.Color = RGB(100, 100, 100)
End If
Loop
Loop While iSession < nSession
If bTab = True Then
' Демонстрируем на таблице правильность сортировки
iRows = GLOBAL_FIO_ROW
With Worksheets(TRACE_TAB_NAME)
For iSession = 0 To nSession - 1
.Cells(iRows, 11) = CStr(Session(iSession).iRow)
.Cells(iRows, 12) = Session(iSession).strRoom
.Cells(iRows, 13) = Session(iSession).strTime
.Cells(iRows, 14) = Session(iSession).strGroup
iRows = iRows + 1
Next iSession
.Cells(1, 11) = "Сортировка по потокам, внутри группировки по времени."
End With
End If
' Всё повторяется, но для аудитории. Поскольку аудитория рассматривается как строка, алгоритм не изменится.
iSession = 0
Do
iLowBorder = iSession
Do
iSession = iSession + 1
Loop While Session(iSession).strTime = Session(iSession - 1).strTime
iHighBorder = iSession - 1
Do While iLowBorder < iHighBorder
For iSort = iLowBorder + 1 To iHighBorder
If Session(iSort).strRoom = Session(iLowBorder).strRoom Then
iLowBorder = iLowBorder + 1
Tmp = Session(iSort)
Session(iSort) = Session(iLowBorder)
Session(iLowBorder) = Tmp
End If
Next iSort
iLowBorder = iLowBorder + 1
Loop
Loop While iSession < nSession
' Отсортировав по потокам внутри временных групп - выявляем накладки - в одной временной группе каждая группа не может встречаться больше одного раза.
iSession = 0
Do
iLowBorder = iSession
Do
iSession = iSession + 1
Loop While Session(iSession).strTime = Session(iSession - 1).strTime
iHighBorder = iSession - 1
Do While iLowBorder < iHighBorder
iLowBorder = iLowBorder + 1
If Session(iLowBorder).strRoom = Session(iLowBorder - 1).strRoom Then
' Красим клетку
Worksheets(GLOBAL_TAB_NAME).Cells(Session(iLowBorder).iRow, iCollumn).Interior.Color = RGB(0, 255, 0)
End If
Loop
Loop While iSession < nSession
If bTab = True Then
' Демонстрируем на таблице правильность сортировки
iRows = GLOBAL_FIO_ROW
With Worksheets(TRACE_TAB_NAME)
For iSession = 0 To nSession - 1
.Cells(iRows, 16) = CStr(Session(iSession).iRow)
.Cells(iRows, 17) = Session(iSession).strRoom
.Cells(iRows, 18) = Session(iSession).strTime
.Cells(iRows, 19) = Session(iSession).strGroup
iRows = iRows + 1
Next iSession
.Cells(1, 16) = "Сортировка по аудиториям, внутри группировки по времени."
.Activate
End With
End If
If bTab Then
Application.StatusBar = "Проверка накладок завершена. Таблица отладки - доступна."
Else:
Application.StatusBar = "Проверка накладок завершена. Таблица отладки - недоступна."
End If
End Sub
ПРИЛОЖЕНИЕ В. АЛГОРИТМЫ
В.1 Алгоритм процедуры записи данных из текущей таблицы в главную, с выявлением накладок
Входные данные: |
||
Имя |
Тип |
Назначение |
LOCAL_SUBJECT_COLLUMN |
Целочисленный |
Обозначает столбец, в котором указаны дисциплины |
LOCAL_FIO _COLLUMN |
Обозначает столбец, в котором указаны фамилии преподавателей. |
|
LOCAL_DATE _COLLUMN |
Обозначает столбец, в котором указаны даты проведения сессий. |
|
LOCAL_TIME _COLLUMN |
Обозначает столбец, в котором указано время начала каждой сессии. |
|
LOCAL_ROOM _COLLUMN |
Обозначает столбец, в котором указаны аудитории, где проводятся сессии. |
|
LOCAL_TYPE _COLLUMN |
Обозначает столбец, в котором указан тип сессии. |
|
LOCAL_START _ROW |
Обозначает строку, в которой находятся первые записи в таблице. |
|
LOCAL_MAX _HOLES |
Обозначает максимальное количество пустых строк, между двумя группами. Если количество пустых строк, для некой группы, превышает указанное число, то алгоритм остановит работу на этой группе. Параметр введен в связи с тем, что в таблице могут существовать группы, для которых еще не заполнены сессии. |
|
Выходные данные: Процедура изменяет цвет ячеек, которые вызывают накладки.
Алгоритм:
Переменные: |
||
Имя |
Тип |
Назначение |
nLocalEmptyCount |
Целочисленный |
Счетчик пустых ячеек в текущей таблице. |
nGlobalEmptyCount |
Счетчик пустых ячеек в таблице по кафедрам. Делает цикл обхода по глобальной таблице гибким. |
|
iGlobalTabWorker |
Переменная запоминает строку, в которой находится нужный сотрудник, в глобальной таблице. |
|
nGlobalRows |
Kоличество строк в таблице "Всего по кафедрам". |
|
nGlobalCollumns |
Kоличество столбцов в таблице "Всего по кафедрам". |
|
iLocalTabWorker |
Переменная запоминает строку, в которой находится нужный сотрудник, в локальной таблице (таблица экзаменов). |
|
iCount |
Счетчик ячеек, выделенных преподавателю. |
|
GlobalTime |
Время из ячейки глобальной таблицы, для поиска коллизий. |
|
strTmpFio |
Строко-вый |
Временная строка, для хранения фио преподавателя. |
strTmpGroup |
Временная строка, для хранения группы (последней попавшейся в таблице, используя свойство, описанное выше). |
|
strTmp() |
Указа-тель строко-вого типа |
Временные строки, для получения времени, аудитории и группы, с помощью функции Split. |
bOccupied |
Логический |
Флаг, который указывает, что все ячейки преподавателя заняты. |
bCollision |
Флаг, который указывает, что произошла накладка. |
|
nGlobalEmptyCount = 0 nLocalEmptyCount = 0 nGlobalRows = GLOBAL_FIO_ROW nGlobalCollumns = 4 iGlobalTabWorker = GLOBAL_FIO_ROW iLocalTabWorker = LOCAL_FIO_ROW CurrentSheet = ActiveSheet.Name |
Пока |
nGlobalEmptyCount < 4 |
|||||||||
nGlobalRows = nGlobalRows + GLOBAL_STEP |
||||||||||
Для |
iGlobalRows = GLOBAL_FIO_ROW |
До |
nGlobalRows Шаг 4 |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
iLocalTabWorker = LOCAL_START_ROW nLocalEmptyCount = 0
iGlobalTabWorker++ |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Для |
iGlobalCollumns = GLOBAL_DATE_COLLUMN |
До |
nGlobalCollumns |
|
ПроверкаНакладок (iGlobalCollumns,nGlobalRows |
||
В.2 Алгоритм процедуры проверки накладок в главной таблице, в указанном столбце
Входные данные: |
||
Имя |
Тип |
Назначение |
iCollumn |
Целочисленный |
Указывает на столбец дат. |
nRows |
Вспомогательный параметр, который указывает на количество строк в таблице |
|
Вспомогательная функция – Парсер – возвращает числовой эквивалент строки, которая обозначает время.
nSession = 0
пер: iRows – Целочисленный
Для |
iRows = GLOBAL_FIO_ROW |
До |
nRows |
||||||
|
|
||||||||
Если |
nSession = 0 |
То |
|
Exit Процедура |
|
Выделение памяти для Session[nSession]
Для |
iRows = GLOBAL_FIO_ROW |
До |
nRows |
||||||
|
|
||||||||
Пер: Min,Max,iSort,iTime – Целочисленный
Для |
iSession =0 |
До |
iSession -1 |
||||||||||||
|
|
||||||||||||||
iSort = 0
Пер: Tmp – Tsession
Пер: iTime – Целочисл
Для |
iTime = Min |
До |
Max шаг 100 |
||||||||||||||
|
|
||||||||||||||||
Пер: iLowBorder, iHighBorder – целочисл
iSession = 0
iLowBorder = iSession
iSession ++
Пока
Session[iSession].strTime
= Session[iSession -1].strTime
iHighBorder = iSession -1
|
|||||||||||||||||||||||
Пока |
iSession<nSession |
||||||||||||||||||||||
iLowBorder = iSession
iSession ++
Пока
Session[iSession].strTime
= Session[iSession -1].strTime
iHighBorder = iSession -1
|
|||||||||||||||
Пока |
iSession<nSession |
||||||||||||||
iSession = 0
iLowBorder = iSession
iSession ++
Пока
Session[iSession].strTime
= Session[iSession -1].strTime
iHighBorder = iSession -1
|
|||||||||||||||||||||||
Пока |
iSession<nSession |
||||||||||||||||||||||
iLowBorder = iSession
iSession ++
Пока
Session[iSession].strTime
= Session[iSession -1].strTime
iHighBorder = iSession -1
|
|||||||||||||||
Пока |
iSession<nSession |
||||||||||||||
ВВЕДЕНИЕ 7