332 lines
13 KiB
PHP
332 lines
13 KiB
PHP
|
<?php
|
||
|
|
session_start();
|
||
|
|
header('Content-Type: text/html; charset=utf-8');
|
||
|
|
date_default_timezone_set('Europe/Moscow');
|
||
|
|
include 'inc/_db.php';
|
||
|
|
include 'config.php';
|
||
|
|
|
||
|
|
|
||
|
|
{$sql = 'CREATE TABLE IF NOT EXISTS `'.$ST['dbpf'].'_platniki_stud` (
|
||
|
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
||
|
|
`data_c` int(11) NOT NULL,
|
||
|
|
`fio` varchar(255) NOT NULL,
|
||
|
|
`num_prikaz` varchar(255) NOT NULL,
|
||
|
|
`dogovor` varchar(255) NOT NULL,
|
||
|
|
`foobuch` varchar(255) NOT NULL,
|
||
|
|
`otdel` varchar(255) NOT NULL,
|
||
|
|
PRIMARY KEY (`id`)
|
||
|
|
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;';
|
||
|
|
$rez = $DB->QUR($sql);}
|
||
|
|
{$sql = 'CREATE TABLE IF NOT EXISTS `'.$ST['dbpf'].'_platniki_period` (
|
||
|
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
||
|
|
`data_c` int(11) NOT NULL,
|
||
|
|
`id_stud` int(11) NOT NULL,
|
||
|
|
`block` int(11) NOT NULL,
|
||
|
|
`zadolgen` varchar(255) NOT NULL,
|
||
|
|
PRIMARY KEY (`id`)
|
||
|
|
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;';
|
||
|
|
$rez = $DB->QUR($sql);}
|
||
|
|
|
||
|
|
$dir = 'upload/platniki/';
|
||
|
|
$step=''; if(isset($_GET['step'])) $step = $_GET['step'];
|
||
|
|
|
||
|
|
if($step=='view'){
|
||
|
|
$file = $dir.'json/all.json';
|
||
|
|
$users = json_decode(file_get_contents($file),1);
|
||
|
|
echo '<table style="border-collapse:collapse;">';
|
||
|
|
|
||
|
|
foreach($users as $k => $v){
|
||
|
|
$f=explode(' ',$v[0]);
|
||
|
|
if($f[0]!=''){
|
||
|
|
$sql = 'SELECT fio,email FROM '.$ST['dbpf'].'_users WHERE fio LIKE "'.$f[0].' '.$f[1].'%"';
|
||
|
|
$rez = $DB->QUR_SEL($sql);
|
||
|
|
$iu=array();
|
||
|
|
if($rez){
|
||
|
|
foreach($rez as $key => $val)if($key){
|
||
|
|
$iu[]=$val['fio'].':'.$val['email'];
|
||
|
|
}
|
||
|
|
}
|
||
|
|
echo '<tr>
|
||
|
|
<td style="padding:0px;">'.$v[0].'</td>
|
||
|
|
<td style="padding:0px;">'.$v[1].'</td>
|
||
|
|
<td style="padding:0px;">'.$v[2].'</td>
|
||
|
|
<td style="padding:0px;">'.implode('<br>',$iu).'</td>
|
||
|
|
</tr>';
|
||
|
|
}
|
||
|
|
}
|
||
|
|
echo '</table>';
|
||
|
|
}
|
||
|
|
if($step=='add'){
|
||
|
|
/* ОЧНИКИ
|
||
|
|
sheet1.xml Абди Хассан Нурадин 5
|
||
|
|
sheet2.xml Агаев Шатлык 5
|
||
|
|
sheet3.xml Абдулаев 5
|
||
|
|
sheet4.xml Экономики и сервиса 5
|
||
|
|
sheet5.xml ОФО Лечебный ф-тет 3
|
||
|
|
sheet6.xml ОФО Экологичский ф-тет 5 - 3 блока
|
||
|
|
sheet7.xml ОФО Фак-тет аграрных технологий 5
|
||
|
|
sheet8.xml ОФО Фармацевтический ф-тет 3
|
||
|
|
sheet9.xml ОФО Фак-тет информационных систем 5
|
||
|
|
sheet10.xml Абдельмоним Махмуд Ашраф Мохамед 4 - должно быть 5 и 3 блока всего
|
||
|
|
sheet11.xml ОРДИНАТУРА 5 - 3 блока
|
||
|
|
sheet12.xml МАГИСТРАТУРА Абдул Водуд Зиаурахман 5
|
||
|
|
sheet13.xml Абилова Джамила Бейтулла кызы 5 - 3 блока
|
||
|
|
sheet14.xml Гриднева Инна Александровна 5 - 1 блок
|
||
|
|
sheet15.xml Стоматология очная фомра 3
|
||
|
|
sheet16.xml Абдулкарим Мохамад 3
|
||
|
|
*/
|
||
|
|
/* ЗАОЧНИКИ
|
||
|
|
sheet1.xml Инженерно-экономический 5
|
||
|
|
sheet2.xml технологический 5
|
||
|
|
sheet3.xml Абдрафикова 5
|
||
|
|
sheet4.xml управление 5
|
||
|
|
sheet5.xml Абдуллоев 5
|
||
|
|
sheet6.xml Фармацевтический 3
|
||
|
|
sheet7.xml аграрных 5
|
||
|
|
sheet8.xml Экологический 5
|
||
|
|
sheet9.xml Аскералиев 5
|
||
|
|
*/
|
||
|
|
$file = $dir.'/2018och.xlsx'; $fo='очники';
|
||
|
|
$t = parse_xlsx($file);
|
||
|
|
$users=array(); $blok_save=4;
|
||
|
|
|
||
|
|
foreach($t as $k => $v){
|
||
|
|
$num_st=-1; $kol=0;
|
||
|
|
foreach($v as $r => $c){
|
||
|
|
foreach($c as $r1 => $c1) if(trim(mb_strtolower($c1['text']))=='договор') {//определяем столбец договора
|
||
|
|
$kol++; //количество блоков = кол-во месяцев
|
||
|
|
$num_st=$r1;
|
||
|
|
//echo $k.'-'.$num_st.'<hr>';
|
||
|
|
break;
|
||
|
|
}
|
||
|
|
if($num_st!=-1){
|
||
|
|
$fio = trim($c[1]['text']);
|
||
|
|
$ndo = trim($c[$num_st]['text']);
|
||
|
|
$npr = '';//номер приказа
|
||
|
|
if($fio!=''&&$ndo!=''){
|
||
|
|
$zad = trim($c[17]['text']);
|
||
|
|
$data = array('fio'=>$fio,'npr'=>$npr,'ndo'=>$ndo,'zad'=>$zad,'kol'=>$kol,'fo'=>$fo,'otdel'=>$k);
|
||
|
|
insert_in_table($data);
|
||
|
|
}
|
||
|
|
}
|
||
|
|
}
|
||
|
|
}
|
||
|
|
|
||
|
|
/*foreach($t as $k => $v){
|
||
|
|
file_put_contents($dir.'/json/'.$k,json_encode($v));
|
||
|
|
//echo '<pre>'.print_r($v,1).'</pre>';
|
||
|
|
$num_st=-1; $kol=0;
|
||
|
|
foreach($v as $r => $c){
|
||
|
|
foreach($c as $r1 => $c1) if(trim(mb_strtolower($c1['text']))=='договор') {//определяем столбец договора
|
||
|
|
$kol++; //количество блоков = кол-во месяцев
|
||
|
|
$num_st=$r1;
|
||
|
|
//echo $k.'-'.$num_st.'<hr>';
|
||
|
|
break;
|
||
|
|
}
|
||
|
|
if($num_st!=-1){
|
||
|
|
$fio = trim($c[1]['text']);
|
||
|
|
$ndo = trim($c[$num_st]['text']);
|
||
|
|
if($fio!=''&&$ndo!=''){
|
||
|
|
$zad = trim($c[17]['text']);
|
||
|
|
$users[$kol][]=array($fio,$ndo,$zad);
|
||
|
|
//$key = cha($fio,$users);
|
||
|
|
//if($key!=-1) $users[$key]=array($fio,$ndo,$zad);
|
||
|
|
//else $users[]=array($fio,$ndo,$zad);
|
||
|
|
}
|
||
|
|
}
|
||
|
|
}
|
||
|
|
}
|
||
|
|
//echo '<pre>'.print_r($users,1).'</pre>';
|
||
|
|
file_put_contents($dir.'/json/all.json',json_encode($users[$blok_save]));*/
|
||
|
|
}
|
||
|
|
if($step=='add1'){
|
||
|
|
$file = $dir.'/2018zaoch.xlsx'; $fo='заочники';
|
||
|
|
$t = parse_xlsx($file);
|
||
|
|
$users=array(); $blok_save=4;
|
||
|
|
|
||
|
|
foreach($t as $k => $v){
|
||
|
|
$num_st=-1; $kol=0;
|
||
|
|
foreach($v as $r => $c){
|
||
|
|
foreach($c as $r1 => $c1) if(trim(mb_strtolower($c1['text']))=='договор') {//определяем столбец договора
|
||
|
|
$kol++; //количество блоков = кол-во месяцев
|
||
|
|
$num_st=$r1;
|
||
|
|
break;
|
||
|
|
}
|
||
|
|
if($num_st!=-1){
|
||
|
|
$fio = trim($c[1]['text']);
|
||
|
|
$ndo = trim($c[$num_st]['text']);
|
||
|
|
$npr = '';//номер приказа
|
||
|
|
if($fio!=''&&$ndo!=''){
|
||
|
|
$zad = trim($c[17]['text']);
|
||
|
|
$data = array('fio'=>$fio,'npr'=>$npr,'ndo'=>$ndo,'zad'=>$zad,'kol'=>$kol,'fo'=>$fo,'otdel'=>$k);
|
||
|
|
insert_in_table($data);
|
||
|
|
}
|
||
|
|
}
|
||
|
|
}
|
||
|
|
}
|
||
|
|
}
|
||
|
|
|
||
|
|
if($step==''){
|
||
|
|
$fio = ''; if(isset($_POST['fio'])) $fio = $_POST['fio'];
|
||
|
|
|
||
|
|
echo '<form method="POST">';
|
||
|
|
echo 'Введите ФИО <input type="text" name="fio" placeholder="Иванов" value="'.$fio.'">';
|
||
|
|
echo '<input type="submit" name="search" value="Найти">';
|
||
|
|
echo '</form>';
|
||
|
|
|
||
|
|
if(isset($_POST['search'])){
|
||
|
|
$sql = 'SELECT ps.fio,ps.dogovor,ps.foobuch,ps.otdel,pp.block,pp.zadolgen FROM '.$ST['dbpf'].'_platniki_stud as ps, '.$ST['dbpf'].'_platniki_period as pp WHERE ps.fio LIKE "%'.$fio.'%" AND ps.id=pp.id_stud ORDER BY pp.data_c';
|
||
|
|
$rez = $DB->QUR_SEL($sql);
|
||
|
|
if($rez){
|
||
|
|
echo '<style>td{border:1px solid black;}</style>';
|
||
|
|
echo '<table style="width:70%;border-collapse:collapse;margin:0 auto;">';
|
||
|
|
echo '<thead><tr><th>ФИО</th><th>Договор</th><th>ФО</th><th>Фак</th><th>Кв</th><th>Задолж</th></tr></thead>';
|
||
|
|
echo '<tbody>';
|
||
|
|
foreach($rez as $k => $v)if($k){
|
||
|
|
if($v['foobuch']=='очники'){
|
||
|
|
if($v['otdel']=='sheet1.xml') $fak='инженерно-эконом';
|
||
|
|
if($v['otdel']=='sheet2.xml') $fak='технологический';
|
||
|
|
if($v['otdel']=='sheet3.xml') $fak='управления';
|
||
|
|
if($v['otdel']=='sheet4.xml') $fak='Экономики и сервиса';
|
||
|
|
if($v['otdel']=='sheet5.xml') $fak='Лечебный ф-тет';
|
||
|
|
if($v['otdel']=='sheet6.xml') $fak='Экологичский ф-тет';
|
||
|
|
if($v['otdel']=='sheet7.xml') $fak='Фак-тет аграрных технологий';
|
||
|
|
if($v['otdel']=='sheet8.xml') $fak='Фармацевтический ф-тет';
|
||
|
|
if($v['otdel']=='sheet9.xml') $fak='Фак-тет информационных систем';
|
||
|
|
if($v['otdel']=='sheet10.xml') $fak='Международный';
|
||
|
|
if($v['otdel']=='sheet11.xml') $fak='ОРДИНАТУРА';
|
||
|
|
if($v['otdel']=='sheet12.xml') $fak='МАГИСТРАТУРА';
|
||
|
|
if($v['otdel']=='sheet13.xml') $fak='ЦЕЛЕВИКИ ЛД';
|
||
|
|
if($v['otdel']=='sheet14.xml') $fak='ЦЕЛЕВИКИ ФА';
|
||
|
|
if($v['otdel']=='sheet15.xml') $fak='Стоматология';
|
||
|
|
if($v['otdel']=='sheet16.xml') $fak='Педиатрия';
|
||
|
|
}
|
||
|
|
if($v['foobuch']=='заочники'){
|
||
|
|
if($v['otdel']=='sheet1.xml') $fak='инженерно-эконом';
|
||
|
|
if($v['otdel']=='sheet2.xml') $fak='технологический';
|
||
|
|
if($v['otdel']=='sheet3.xml') $fak='финансы';
|
||
|
|
if($v['otdel']=='sheet4.xml') $fak='управление';
|
||
|
|
if($v['otdel']=='sheet5.xml') $fak='информац. системы';
|
||
|
|
if($v['otdel']=='sheet6.xml') $fak='Фармацевтический ф-тет';
|
||
|
|
if($v['otdel']=='sheet7.xml') $fak='Фак-тет аграрных технологий';
|
||
|
|
if($v['otdel']=='sheet8.xml') $fak='Экологический';
|
||
|
|
if($v['otdel']=='sheet9.xml') $fak='МАГИСТРАТУРА';
|
||
|
|
}
|
||
|
|
echo '<tr>
|
||
|
|
<td>'.$v['fio'].'</td>
|
||
|
|
<td>'.$v['dogovor'].'</td>
|
||
|
|
<td>'.$v['foobuch'].'</td>
|
||
|
|
<td>'.$fak.'</td>
|
||
|
|
<td>'.$v['block'].'</td>
|
||
|
|
<td>'.$v['zadolgen'].'</td>
|
||
|
|
</tr>';
|
||
|
|
}
|
||
|
|
echo '</tbody>';
|
||
|
|
echo '</table>';
|
||
|
|
}
|
||
|
|
}
|
||
|
|
}
|
||
|
|
|
||
|
|
function insert_in_table($data){
|
||
|
|
GLOBAL $ST,$DB; $out=array();
|
||
|
|
$sql = 'SELECT id FROM '.$ST['dbpf'].'_platniki_stud WHERE fio="'.$data['fio'].'" AND foobuch="'.$data['fo'].'" AND otdel="'.$data['otdel'].'"';
|
||
|
|
$rez = $DB->QUR_SEL($sql); //echo $sql.'<pre>'.print_r($rez,1).'</pre>';
|
||
|
|
//if($rez){
|
||
|
|
if($rez[0]){
|
||
|
|
$id_stud=$rez[1]['id'];
|
||
|
|
$sql1 = 'SELECT id FROM '.$ST['dbpf'].'_platniki_period WHERE id_stud='.$id_stud.' AND block='.$data['kol'].'';
|
||
|
|
$rez1 = $DB->QUR_SEL($sql1); //echo $sql1.'<pre>'.print_r($rez1,1).'</pre>';
|
||
|
|
//if($rez1){
|
||
|
|
if($rez1[0]){
|
||
|
|
$sql = 'UPDATE '.$ST['dbpf'].'_platniki_period SET zad = "'.$data['zad'].'" WHERE id = '.$rez1[1]['id'].'';
|
||
|
|
$rez = $DB->QUR($sql); //echo $sql.'<pre>'.print_r($rez,1).'</pre>';
|
||
|
|
}else{
|
||
|
|
$sql = 'INSERT INTO '.$ST['dbpf'].'_platniki_period VALUES (0,'.time().','.$id_stud.','.$data['kol'].',"'.$data['zad'].'")';
|
||
|
|
$rez = $DB->QUR($sql); //echo $sql.'<pre>'.print_r($rez,1).'</pre>';
|
||
|
|
}
|
||
|
|
//}
|
||
|
|
}else{
|
||
|
|
$sql = 'INSERT INTO '.$ST['dbpf'].'_platniki_stud VALUES (0,'.time().',"'.$data['fio'].'","","'.$data['ndo'].'","'.$data['fo'].'","'.$data['otdel'].'")';
|
||
|
|
$rez = $DB->QUR($sql); //echo $sql.'<pre>'.print_r($rez,1).'</pre>';
|
||
|
|
$id_stud = mysql_insert_id();
|
||
|
|
$sql = 'INSERT INTO '.$ST['dbpf'].'_platniki_period VALUES (0,'.time().','.$id_stud.','.$data['kol'].',"'.$data['zad'].'")';
|
||
|
|
$rez = $DB->QUR($sql); //echo $sql.'<pre>'.print_r($rez,1).'</pre>';
|
||
|
|
}
|
||
|
|
//}
|
||
|
|
return $out;
|
||
|
|
}
|
||
|
|
|
||
|
|
function cha($fio,$users){//получение ключа в массиве фамилии
|
||
|
|
foreach($users as $k => $v){
|
||
|
|
if($fio==$v[0]) {
|
||
|
|
echo $fio.'--'.$v[0].'<br>';
|
||
|
|
return $k;
|
||
|
|
}
|
||
|
|
}
|
||
|
|
return -1;
|
||
|
|
}
|
||
|
|
|
||
|
|
function parse_xlsx($file){
|
||
|
|
include_once 'inc/pclzip.lib.php'; $out=array();
|
||
|
|
$archive = new PclZip($file);
|
||
|
|
$t=explode('.',$file);unset($t[count($t)-1]); $folder = implode('.',$t); //echo $folder.'; ';
|
||
|
|
if(!file_exists($folder)) mkdir($folder);
|
||
|
|
if ($archive->extract(PCLZIP_OPT_PATH, $folder) == 0) {
|
||
|
|
//die("Error : ".$archive->errorInfo(true));
|
||
|
|
}else{
|
||
|
|
$xml = simplexml_load_file($folder . '/xl/sharedStrings.xml');
|
||
|
|
$sharedStringsArr = array();
|
||
|
|
foreach ($xml->children() as $item) {
|
||
|
|
$sharedStringsArr[] = (string)$item->t;
|
||
|
|
}
|
||
|
|
$handle = @opendir($folder . '/xl/worksheets');
|
||
|
|
while ($file = @readdir($handle)) {
|
||
|
|
//проходим по всем файлам из директории /xl/worksheets/
|
||
|
|
if ($file != "." && $file != ".." && $file != '_rels') {
|
||
|
|
//if ($file == 'sheet1.xml') {
|
||
|
|
$xml = simplexml_load_file($folder . '/xl/worksheets/' . $file);
|
||
|
|
|
||
|
|
$mergeCells=array();
|
||
|
|
foreach ($xml->mergeCells->mergeCell as $item) {
|
||
|
|
$attr = $item->attributes();
|
||
|
|
$t=explode(':',$attr['ref']);
|
||
|
|
$t1=array(0=>$t[0],1=>$t[1]);
|
||
|
|
$mergeCells[]=$t1;
|
||
|
|
}
|
||
|
|
$ab=array('A'=>1,'B'=>2,'C'=>3,'D'=>4,'E'=>5,'F'=>6,'G'=>7,'H'=>8,'I'=>9,'J'=>10,'K'=>11,'L'=>12,'M'=>13,'N'=>14,'O'=>15);
|
||
|
|
//по каждой строке
|
||
|
|
$row = 0;
|
||
|
|
foreach ($xml->sheetData->row as $item) {
|
||
|
|
$out[$file][$row] = array();
|
||
|
|
//по каждой ячейке строки
|
||
|
|
$cell = 0;
|
||
|
|
foreach ($item as $child) {
|
||
|
|
$attr = $child->attributes();
|
||
|
|
$value = isset($child->v)? (string)$child->v:false;
|
||
|
|
$out[$file][$row][$cell]['text'] = isset($attr['t']) ? $sharedStringsArr[$value] : $value;
|
||
|
|
if(isset($attr['r'])){//проверим на объединение
|
||
|
|
foreach($mergeCells as $k1 => $v1){
|
||
|
|
if($attr['r'] == $v1[0]){//есть в объединениях
|
||
|
|
if($v1[0]{0}!=$v1[1]{0}&&$v1[0]{1}==$v1[1]{1}){//горизонтальное объединение
|
||
|
|
$out[$file][$row][$cell]['mcgor'] = $ab[$v1[1]{0}]-$ab[$v1[0]{0}]+1;
|
||
|
|
}
|
||
|
|
if($v1[0]{0}==$v1[1]{0}&&$v1[0]{1}!=$v1[1]{1}){//вертикальное объединение
|
||
|
|
$out[$file][$row][$cell]['mcver'] = $v1[1]{1}-$v1[0]{1}+1;
|
||
|
|
}
|
||
|
|
if($v1[0]{0}!=$v1[1]{0}&&$v1[0]{1}!=$v1[1]{1}){//херовое объединение
|
||
|
|
$out[$file][$row][$cell]['mcver'] = $v1[1]{1}-$v1[0]{1}+1;
|
||
|
|
$out[$file][$row][$cell]['mcgor'] = $ab[$v1[1]{0}]-$ab[$v1[0]{0}]+1;
|
||
|
|
}
|
||
|
|
}
|
||
|
|
}
|
||
|
|
}
|
||
|
|
$cell++;
|
||
|
|
}
|
||
|
|
$row++;
|
||
|
|
}
|
||
|
|
}
|
||
|
|
}
|
||
|
|
}
|
||
|
|
return $out;
|
||
|
|
}
|