Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- //timetable.php
- <?php
- function get_from_excel($patch)//возвращает первый лист книги в виде двумерного размера массива. [n][m], n - столбец, m - строка. $patch - путь к файлу
- {
- require_once "Classes/PHPExcel.php";
- $ar=array();
- $inputFileType = PHPExcel_IOFactory::identify($patch);
- $objReader = PHPExcel_IOFactory::createReader($inputFileType);
- $objPHPExcel = $objReader->load($patch);
- $ar = $objPHPExcel->getActiveSheet()->toArray();
- array_unshift($ar, null);
- $ar = call_user_func_array("array_map", $ar);
- return $ar;
- }
- function add_table($del, $name, $week, $groups, $days, $num, $sub_1, $sub_2, $teach_1, $teach_2, $hall_1, $hall_2)
- {
- include("Classes/sql.php");
- if($del)
- {
- mysqli_query($sql, "DROP TABLE `$db.$name`;");
- }
- mysqli_query($sql, "
- create table $db.$name(
- `week` int (1) NOT NULL,
- `group` int (2) NOT NULL,
- `day` int (1) NOT NULL,
- `num` int (1) NOT NULL,
- `subj_1` text NOT NULL,
- `subj_2` text NOT NULL,
- `teach_1` text NOT NULL,
- `teach_2` text NOT NULL,
- `hall_1` text NOT NULL,
- `hall_2` text NOT NULL);
- ");
- for($i = 0; $i<count($num); $i++)
- {
- mysqli_query($sql, "INSERT INTO `$db`.`$name` (`week`, `group`, `day`, `num`, `subj_1`, `subj_2`, `teach_1`, `teach_2`, `hall_1`, `hall_2`)
- VALUES ('$week', '$groups[$i]', '$days[$i]', '$num[$i]', '$sub_1[$i]', '$sub_2[$i]', '$teach_1[$i]', '$teach_2[$i]', '$hall_1[$i]', '$hall_2[$i]');");
- }
- }
- function add_changetable($name, $week, $day, $date, $num, $groups, $subgroups, $removesubs, $lessons, $teachers, $hall)
- {
- include("Classes/sql.php");
- mysqli_query($sql, "DROP TABLE `$db`.`$name`;");
- mysqli_query($sql, "
- create table $db.$name(
- `week` int (1) NOT NULL,
- `day` int (1) NOT NULL,
- `date` text NOT NULL,
- `num` int (1) NOT NULL,
- `groups` text NOT NULL,
- `subgroups` int (1) NOT NULL,
- `removesubs` int (1) NOT NULL,
- `lessons` text NOT NULL,
- `teachers` text NOT NULL,
- `hall` text NOT NULL);
- ");
- for($i = 0; $i<count($num); $i++)
- {
- mysqli_query($sql, "INSERT INTO `$db`.`$name` (`week`, `day`, `date`, `num`, `groups`, `subgroups`, `removesubs`, `lessons`, `teachers`, `hall`)
- VALUES ('$week', '$day', '$date', '$num[$i]', '$groups[$i]', '$subgroups[$i]', '$removesubs[$i]', '$lessons[$i]', '$teachers[$i]', '$hall[$i]');");
- }
- }
- function gen_groups($patch)//генерация списка групп из расписания на неделю
- {
- $arr = get_from_excel($patch);
- $y_def = 9;
- $y = $y_def;
- $x_step = 4;
- $y_offset = 52;
- $dbname="news";//Имя БД
- include("Classes/sql.php");
- mysqli_query($sql, "DROP TABLE `groups`;");
- mysqli_query($sql, "
- CREATE TABLE `groups` (
- id INT PRIMARY KEY NOT NULL AUTO_INCREMENT ,
- name TEXT NOT NULL
- );
- ");
- for($y; $y<=$y_def+$y_offset; $y+=$y_offset)//семещение на 52 строки для прохода по нижнему блоку
- {
- $end = false;
- $x = 4;
- while ($group=(empty($arr[$x][$y])) ? FALSE : $arr[$x][$y])//проверка выхода из массива по x
- {
- mysqli_query($sql, "INSERT INTO `groups` (`name`) VALUE('$group');");
- $x+=$x_step;
- }
- }
- }
- function gen_days($patch)
- {
- $arr = get_from_excel($patch);
- $dbname="u4730742_kgdt";//Имя БД
- include("Classes/sql.php");
- mysqli_query($sql, "DROP TABLE `days`;");
- mysqli_query($sql, "
- CREATE TABLE `days` (
- id INT PRIMARY KEY NOT NULL AUTO_INCREMENT ,
- name TEXT NOT NULL
- );
- ");
- for($y=11; $y<=51; $y+=8)
- {
- $name= $arr[0][$y];
- mysqli_query($sql, "INSERT INTO `days` (`name`) VALUE('$name');");
- }
- }
- function get_group_id($name)
- {
- include("Classes/sql.php");
- $id = mysqli_query($sql, "SELECT id FROM `groups` WHERE name='$name'");
- $row = $id->fetch_row();
- return $row[0];
- }
- function get_day($name)
- {
- include("Classes/sql.php");
- $id = mysqli_query($sql, "SELECT id FROM `days` WHERE name='$name'");
- $row = $id->fetch_row();
- return $row[0];
- }
- function create_timetable($patch, $week, $del) //Генерирует таблицу недельного расписания(путь_к_расписанию, неделя(1 2), удалять_ли_существующую)
- {
- //include("PHPDebug.php");
- //$dbg = new PHPDebug();
- $arr = get_from_excel($patch);
- $day = 11;
- $day_offset = 8;
- $line = 9;
- $line_offset = 52;
- $y=11;
- $x=4;
- //структура
- $groups=Array();
- $days=Array();
- $num=Array();
- $group=Array();
- $sub_1=Array();
- $sub_2=Array();
- $teach_1=Array();
- $teach_2=Array();
- $hall_1=Array();
- $hall_2=Array();
- //
- $name='const_table';
- for($j=0; $j<2; $j++)
- {
- while(!empty($arr[0][$y]) && $arr[0][$y] != '')
- {
- $x=4;
- while (!empty($arr[$x][$line]))
- {
- $counter=1;
- $day=$y;
- for($i=$y; $i<=$y+6; $i+=2)
- {
- $groups[]=get_group_id($arr[$x][$line]);
- $days[]=get_day($arr[0][$day]);
- $num[]=$counter;
- $sub_1[]=(empty($arr[$x][$i])) ? '' : $arr[$x][$i];
- $sub_2[]=(empty($arr[$x+2][$i])) ? $arr[$x][$i] : $arr[$x+2][$i];
- $teach_1[]=(empty($arr[$x][$i+1])) ? '' : $arr[$x][$i+1];
- $teach_2[]=(empty($arr[$x+2][$i+1])) ? $arr[$x][$i+1] : $arr[$x+2][$i+1];
- $hall_1[]=(empty($arr[$x+3][$i])) ? '' : $arr[$x+3][$i];
- $hall_2[]=(empty($arr[$x+1][$i])) ? $arr[$x+3][$i] : $arr[$x+1][$i];
- $counter++;
- }
- $x+=4;
- }
- $y+=$day_offset;
- }
- $line+=$line_offset;
- $y=$line+2;
- }
- add_table($del, $name, $week, $groups, $days, $num, $sub_1, $sub_2, $teach_1, $teach_2, $hall_1, $hall_2);
- }
- function gen_changes($patch)
- {
- $name='changes';
- include('Classes/StrLevel.php');
- $arr = get_from_excel($patch);
- $title = $arr[0][1];
- preg_match_all('/\((.+)\)/', $title, $day);
- $day=get_day(_strtoupper($day[1][0]));//номер дня недели
- preg_match_all('/(Неделя )(?:([0-9]{1}))/', $title, $week);
- $week=$week[2][0];//номер недели
- preg_match_all('/\S+[.\]\.[0-9]{2}\.[0-9]{4}/', $title, $date);
- $date=$date[0][0];
- $y=5;
- $groups=Array();//группа
- $num=Array();//номер пары
- $lessons=Array();//предмет(замененный)
- $teachers=Array();//преподаватель(замененный)
- $removesubs=Array();//снятая подгруппы
- $subgroups=Array();//добавленная подгруппа
- $hall=Array();//дабавленная аудитория
- while(!empty($arr[0][$y]) && $arr[0][$y] != '')
- {
- $groups[]=get_group_id($arr[0][$y]);
- $num[]=$arr[1][$y];
- $removesubs[]=$arr[2][$y];
- $subgroups[]=$arr[5][$y];
- $lessons[]=$arr[6][$y];
- $teachers[]=$arr[7][$y];
- $hall[]=$arr[8][$y];
- $y++;
- }
- /*print_r($groups);
- print_r($num);
- print_r($removesubs);
- print_r($subgroups);
- print_r($lessons);
- print_r($teachers);
- print_r($hall);*/
- add_changetable($name, $week, $day, $date, $num, $groups, $subgroups, $removesubs, $lessons, $teachers, $hall);
- }
- function generate_table($group)
- {
- //`week`, `group`, `day`, `num`, `subj_1`, `subj_2`, `teach_1`, `teach_2`, `hall_1`, `hall_2`
- include("Classes/sql.php");
- $group_id=get_group_id($group);
- $tmp = mysqli_query($sql, "SELECT `week`, `day`, `date` FROM `changes` WHERE 1");
- $row = $tmp->fetch_row();
- $week = $row[0][0];
- $day = $row[0][1];
- $date = $row[0][2];
- $tmp = mysqli_query($sql, "DROP TABLE `daychanges`;
- CREATE TABLE `daychanges` SELECT * FROM `const_table` WHERE `week` = '$week', `group` = '$group_id', `day` = '$day'; INSERT INTO ");
- return $row[0];
- }
- function gen_changed()
- {
- include("Classes/sql.php");
- mysqli_query($sql, "
- DROP TABLE `changed`;
- ");
- $tmp = mysqli_query($sql, "
- SELECT `week`
- FROM `changes`
- LIMIT 0 , 1");
- $week = $tmp->fetch_row()[0];
- $tmp = mysqli_query($sql, "
- SELECT `day`
- FROM `changes`
- LIMIT 0 , 1");
- $day = $tmp->fetch_row()[0];
- $q="
- CREATE TABLE `changed` AS SELECT `group`, `num` , `subj_1` , `subj_2` , `teach_1` , `teach_2` , `hall_1` , `hall_2`
- FROM `const_table`
- WHERE `day` = '$day' AND `week` = '$week'
- ";
- mysqli_query($sql, $q);
- $tmp = mysqli_query($sql, "SELECT `num`, `groups` FROM `changes` WHERE 1");
- $num = Array();
- $group = Array();
- while($row = $tmp->fetch_row())
- {
- $num[] = $row[0];
- $group[] = $row[1];
- }
- for ($i=0; $i<count($num); $i++)
- {
- $tmp = mysqli_query($sql, "
- SELECT `subgroups`, `removesubs`, `lessons`, `teachers`, `hall`
- FROM `changes`
- WHERE `num`='$num[$i]' AND `groups`='$group[$i]'");
- $row = $tmp->fetch_row();
- $sub = $row[0];
- $resub = $row[1];
- $lesson = $row[2];
- $teacher = $row[3];
- $hall = $row[4];
- if($resub = 0)
- {
- mysqli_query($sql, "
- UPDATE `changed`
- SET `subj_1` = '', `subj_2` = '', `teach_1` = '', `teach_2` = '', `hall_1` = '', `hall_2` = ''
- WHERE `num`='$num[$i]' AND `group`='$group[$i]';");
- }
- if($resub = 1)
- {
- mysqli_query($sql, "
- UPDATE `changed`
- SET `subj_1` = '', `teach_1` = '', `hall_1` = ''
- WHERE `num`='$num[$i]' AND `group`='$group[$i]';");
- }
- if($resub = 2)
- {
- mysqli_query($sql, "
- UPDATE `changed`
- SET `subj_2` = '', `teach_2` = '', `hall_2` = ''
- WHERE `num`='$num[$i]' AND `group`='$group[$i]';");
- }
- if($sub == 0)
- {
- mysqli_query($sql, "
- UPDATE `changed`
- SET `subj_1` = '$lesson', `subj_2` = '$lesson', `teach_1` = '$teacher', `teach_2` = '$teacher', `hall_1` = '$hall', `hall_2` = '$hall'
- WHERE `num`='$num[$i]' AND `group`='$group[$i]';");
- }
- if($sub == 1)
- {
- mysqli_query($sql, "
- UPDATE `changed`
- SET `subj_1` = '$lesson', `subj_2` = '', `teach_1` = '$teacher', `teach_2` = '', `hall_1` = '$hall', `hall_2` = ''
- WHERE `num`='$num[$i]' AND `group`='$group[$i]';");
- }
- if($sub == 2)
- {
- mysqli_query($sql, "
- UPDATE `changed`
- SET `subj_1` = '', `subj_2` = '$lesson', `teach_1` = '', `teach_2` = '$teacher', `hall_1` = '', `hall_2` = '$hall'
- WHERE `num`='$num[$i]' AND `group`='$group[$i]';");
- }
- }
- }
- function get_groups()
- {
- include("Classes/sql.php");
- return mysqli_query($sql, "SELECT * FROM groups WHERE 1");
- }
- function get_timetable($group, $subgroup)
- {
- include("Classes/sql.php");
- $tmp = mysqli_query($sql, "SELECT `date` FROM `changes` LIMIT 0, 1;");
- $date = $tmp->fetch_row()[0];
- $tmp = mysqli_query($sql, "SELECT subj_$subgroup, teach_$subgroup, hall_$subgroup FROM changed WHERE `group`=$group");
- echo '
- <div align="center">
- <div align="center">Расписание на '.$date.'</div>
- ';
- while($row = $tmp->fetch_row())
- {
- echo '<p>'.$row[0].'('.$row[1].') ауд. №'.$row[2];
- }
- echo '
- </div>
- ';
- }
- /*gen_days('1week.xls');
- gen_groups('1week.xls');
- create_timetable('1week.xls', 1, true);
- create_timetable('2week.xls', 2, false);*/
- function static_table($name1, $name2)
- {
- gen_days($name1);
- gen_groups($name1);
- create_timetable($name1, 1, true);
- create_timetable($name2, 2, false);
- }
- function change_table($name)
- {
- gen_changes($name);
- gen_changed();
- }
- ?>
- //load.php
- <?php
- require_once('timetable.php');
- if(isset($_FILES['first']) && isset($_FILES['second']))
- {
- static_table($_FILES['first']['tmp_name'], $_FILES['second']['tmp_name']);
- }
- else if(isset($_FILES['changes']))
- {
- change_table($_FILES['changes']['tmp_name']);
- }
- echo '
- <div>
- <form action="load.php" method="post" enctype="multipart/form-data">
- <p>Основное расписание:
- <p><input type="file" name="first" />
- <p><input type="file" name="second" />
- <p><input type="submit" value="Отправить" />
- </form>
- </div>
- <p>
- <div>
- <form action="load.php" method="post" enctype="multipart/form-data">
- <p>Лист замен:
- <p><input type="file" name="changes" />
- <p><input type="submit" value="Отправить" />
- </form>
- </div>
- ';
- ?>
- //get.php
- <?php
- include('timetable.php');
- $groups = get_groups();
- echo '
- <div>
- <form action="get.php" method="post" enctype="multipart/form-data">
- Расписание:
- <p><select name="group">
- <option selected disabled>Выберите группу</option>
- ';
- while($row = $groups->fetch_row())
- {
- echo '<option value="'.$row[0].'">'.$row[1].'</option>';
- }
- echo ' </select>
- <select name="subgroup">
- <option selected disabled>Выберите подгруппу</option>
- <option value="1">1</option>
- <option value="2">2</option>
- </select>
- <input type="submit" value="Получить" />
- </form>
- </div>
- ';
- if (isset($_POST['group']) && isset($_POST['subgroup']))
- {
- get_timetable($_POST['group'], $_POST['subgroup']);
- }
- ?>
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement