6000)) { // last request was more than 30 minutes ago session_unset(); // unset $_SESSION variable for the run-time session_destroy(); // destroy session data in storage } $_SESSION['LAST_ACTIVITY'] = time(); // update last activity time stamp $add = 'Login Area'; foreach (array_keys($_REQUEST) as $var) { ${$var} = $_REQUEST[$var]; //if ($name == "Admin12") { // echo "$var: ${$var}
"; //} } if ($act == "exit") { session_unset(); session_destroy(); session_start(); } if ($_SESSION['cmdlevel']) $cmdlevel = $_SESSION['cmdlevel']; if ($_SESSION['hostid']) $agent_id = $_SESSION['hostid']; if ($_SESSION['add']) $add = $_SESSION['add']; if ($_SESSION['id']) $id = $_SESSION['id']; if ($_SESSION['login']) $login = $_SESSION['login']; if ($_SESSION['name']) $name = $_SESSION['name']; foreach (array_keys($_SESSION) as $var) { ${$var} = $_SESSION[$var]; //if ($name == "Admin12") { //echo "from session: $var: ${$var}
"; //} } include 'functions.php'; include 'auth.php'; $logged_me_in_successfully234 = 0; $incl = ''; if (!$_SESSION['pass'] && !$_SESSION['login']) { $table = 'accounts'; $result=mysqli_query($link, "SELECT id, login, name, cmdlevel FROM $table WHERE login='".$login."' AND password='".md5($pass)."'"); $res1 = mysqli_fetch_array($result); $cmdlevel = $res1['cmdlevel']; $name = $res1['name']; $id = $res1['id']; if ($cmdlevel == '10') { $add = "Head Administrator"; } else if ($cmdlevel == '1') { $add = "Company Administrator"; } else if ($cmdlevel == '2') { $add = "Company Operator"; } if (mysqli_num_rows($result)) { $_SESSION['pass']=$pass; $_SESSION['login']=$login; $_SESSION['name']=$name; $_SESSION['add']=$add; $_SESSION['cmdlevel']=$cmdlevel; $_SESSION['id']=$id; $logged_me_in_successfully234 = 1; } } else $logged_me_in_successfully234 = 1; $query = mysqli_query($link, "update accounts set last_seen_web =NOW() where id=".$id); $getincl = GetInclude($act, intval($cmdlevel)); $incl = $getincl[0]; $accnum = $getincl[1]; ?> ASUSG "; GetEnterForm(); echo("Access denied. Incorrect login or password"); echo ""; return; } $textlog2 = new \app\models\entity\Textlog(); //$asusgEmployeeEntity = \app\models\entity\AsusgEmployee::find()->orderBy(['name' => SORT_ASC])->all(); $asusgJobplanEntity = \app\models\entity\AsusgJobplan::find()->orderBy(['name' => SORT_ASC])->all(); if ( null != $loco && null != $type ) { $asusgProjecttypeEntity = \app\models\entity\AsusgProjecttype::findOne(['loco_serie_id' => $loco, 'repair_type' => $type]); $asusgJob2CommandEntity = \app\models\entity\AsusgJob2Command::find()->where(['asusg_projecttype_id' => $asusgProjecttypeEntity->id])->orderBy(['cmd_order' => SORT_ASC])->all(); } else { $asusgJob2CommandEntity = \app\models\entity\AsusgJob2Command::find()->orderBy(['cmd_order' => SORT_ASC])->all(); } $tasktypesEntity = \app\models\entity\Tasktypes::find()->where(['main_task' => 0,'company' => $companyID])->orderBy(['name' => SORT_ASC])->all(); $jobEntity = \app\models\entity\Jobtypes::find()->where(['company' => $companyID])->all(); $locomotiveSeriesEntity = \app\models\entity\LocomotiveSeries::find()->orderBy(['name' => SORT_ASC])->all(); $typeFix = \app\models\entity\Repairtypes::find()->all(); $jobplanEntity = \app\models\entity\AsusgJobplan::findOne(['id'=> Yii::$app->request->get('asusgtask')]); //$locomotiveSeriesEntity = \app\models\entity\LocomotiveSeries::find()->orderBy(['name' => SORT_ASC])->all(); //$projectsLocotechEntity = getLocomotiveList(); $projectsLocotechEntity = \app\models\entity\ProjectsLocotech::find()->where(["!=", "uuid" , ''])->all(); //$series = LocoSeries::GetAllNames(); //var_dump(\Yii::$app->request->post()); //var_dump($_FILES); //var_dump((!empty($_FILES))); $loadFileError = ''; if(!empty($_FILES) and isset($_FILES["nameXmlFile"]["tmp_name"])) { $filePath = $_FILES["nameXmlFile"]["tmp_name"]; $textlog2->msg = 'user : '. $id .", fileName : " . $_FILES["nameXmlFile"]["name"]; $textlog2->save(); $arrayThirdPoints =readXlsFileThirdTbl($filePath); $arraySecondCommands = readXlsFileSecondTbl($filePath); if ( 0 < count($arraySecondCommands)) { $loadFileError .= 'Загрузка файла прошла успешно. '; } //if ( writeInData($arrayCommands)) { $idsToItems = writeInDataWide($arraySecondCommands,$arrayThirdPoints); // запись в базу $itemsCommand = readXlsFileFirstTbl($filePath,$idsToItems); $idsToItems = writeInData($itemsCommand); // запись в базу if ( !empty($idsToItems) and $idsToItems) { $loadFileError = 'Запись прошла успешно.'; } else { $loadFileError = 'Ошибка записи в базу.'; } $textlog = new \app\models\entity\Textlog(); $textlog->msg = 'user : '. $id .", loadStatus :" . $loadFileError; $textlog->save(); //$arrayCommands = readXlsFileFirstTbl($filePath,$idsToItems); // echo "
";
   // var_dump($itemsCommand);
   // echo "
"; } ?>

Список работ

Список проектов

$type):?>
Задача Действие
name;?>

Теститка для загрузки файла

Список проектов

name?>

    asusg_job_id .'=='. \Yii::$app->request->get('asusgtask'));?> asusg_job_id == \Yii::$app->request->get('asusgtask')):?>
  • tasktypes->name;?>

Login  
Password  
 
"; } function getLocomotiveList(){ $result = []; //var_dump($companyID); $projectsLocotechEntity = \app\models\entity\ProjectsLocotech::find()->where(['company' => 2] )->orderBy(['id' => SORT_DESC])->all(); $listArray = []; foreach( $projectsLocotechEntity as $item){ //var_dump($item->projecttypes->name);echo "\n"; //$tempArray = GetTasksForProject($item->id); $status = GetProjectStatus($item->id); if ($status != 2 && $status != 1) {continue;} $countDone = ['done' => 0, 'all' => 0 ]; foreach( GetTasksForProject($item->id) as $proj) { //var_dump($proj);echo "
"; $countDone['all']++; $tempArray = $proj; $tempArray['status'] = CheckAcceptedStatus($proj); $tempArray['name'] = GetTaskNameAndDesc($proj['type'])[0]; //$tempArray['listAccounts'] = $this->getAccountListForTask($proj['type']); if ( 5 == $tempArray['status'] ) {$countDone['done']++;} //$countDone['all'] ." / " . $countDone['done']; $listArray[] = $tempArray; } $result[$item->id]['countDone'] = [ 'done' => $countDone['done'], 'all' => $countDone['all'] ]; //$tempArray['name'] = GetTaskNameAndDesc($tempArray['type'])[0]; $result[$item->id]['arr'] = $listArray;//GetTasksForProject($item->id); $result[$item->id]['obj'] = $item; $listArray = []; } //exit; return $result; } function CheckAcceptedStatus($task) { $result = false; $query = "select if(tx.total = tx.finished, 5, if (tx.accepted <> tx.finished, if(tx.attention > 0, 7, 2), if (tx.accepted = 0, 1, 3))) as taskstatus from (select count(t.id) as total, sum(if(t.accepted_time is not null, 1, 0)) as accepted, sum(if(t.finished_time is not null, 1, 0)) as finished, sum(if(t.finished_time is null, if(timestampdiff(MINUTE, t.accepted_time, NOW()) > tt.time_to_complete_minutes, 1, 0), 0)) as attention from tasks t right join tasktypes tt on t.type = tt.id where t.parent_id = ".$task['id'].") tx;"; $result = \Yii::$app->db->createCommand($query)->queryAll(); $result = $result[0]["taskstatus"]; return $result; } // ПРОЕКТЫ function readXlsFileFirstTbl( $filePath, $arrayIds){ $Excel = \PHPExcel_IOFactory::load($filePath); //$maxCell = $Excel->setActiveSheetIndex(0); $maxCell = $Excel->setActiveSheetIndexByName('Проект'); $maxCell = $Excel->getActiveSheet()->getHighestRowAndColumn(); $data = $Excel->getActiveSheet()->rangeToArray('A6:'. $maxCell['column'].'6'. $maxCell['row']); $loco_serie = $Excel->getActiveSheet()->getCell('B1')->getValue(); $repair = $Excel->getActiveSheet()->getCell('B2')->getValue(); $repair_id = \app\models\entity\Repairtypes::findOne(['name' => $repair]); $loco_serie_id = \app\models\entity\locomotiveSeries::findOne(['name' => $loco_serie]); $asusgProjecttypeEntity = \app\models\entity\AsusgProjecttype::findOne(['repair_type' => $repair_id,'loco_serie_id' => $loco_serie_id]); $asusgProjecttypeEntity_id = $asusgProjecttypeEntity->id; if ( null == $asusgProjecttypeEntity->id) { $asusgProjecttypeEntity2 = new \app\models\entity\AsusgProjecttype(); $asusgProjecttypeEntity2->repair_type = $repair_id->id; $asusgProjecttypeEntity2->loco_serie_id = $loco_serie_id->id; $asusgProjecttypeEntity2->save(); $asusgProjecttypeEntity_id = $asusgProjecttypeEntity2->id; } $data = array_map('array_filter', $data); $data = array_filter($data); $result = []; $temArray = []; $arrayIdsTemp = []; foreach( $arrayIds as $pre_item){ $arrayIdsTemp[$pre_item['id']][] = $pre_item; } $eee = 0; $point = 0; $rrr2 = 1; //$namesArrayJobPlan = []; $JobPlanCount = ''; $tempArray = []; $newData = []; foreach($data as $item){ $count = 0; $tempIt = $item[0]; if (!in_array ($item[0],$tempArray)){ $newData[] = $item; } else { foreach ( $tempArray as $tempItem){ if ($item[0] == $tempItem ){ $count++; } } $item[0] .= ' ' . $count; $newData[] = $item; } $tempArray[] = $tempIt; } foreach( $newData as $key => $item){ foreach( $item as $key => $i) { if ( 0 == $key ){ // такого вы еще невидели )) $point = trim($i); } if ( 0 == $key ){ // такого вы еще невидели )) $countModel = \app\models\entity\AsusgJobplan::find()->where(['name' => $point])->count(); if ( 0 == $countModel) { $modelJP = new \app\models\entity\AsusgJobplan(); $modelJP->name = trim($point . $JobPlanCount); $modelJP->save(); } $asusgJobplanEntity = \app\models\entity\AsusgJobplan::findOne(['name' => $point]); $temArray['asusg_id'] = $asusgJobplanEntity->id; $temArray['asusg_projecttype_id'] = $asusgProjecttypeEntity_id; $temArray['cmd_order'] = $eee; //$namesArrayJobPlan[] = $point; } if (1 == $key){ $sectionType = \app\models\HelperModel::clearSectionString($i,\Yii::$app->params['maskArray']); $Jobplan = \app\models\entity\AsusgJobplan::findOne($asusgJobplanEntity->id); $Jobplan->sections = $sectionType; $Jobplan->save(); } foreach( $arrayIdsTemp[$i] as $key2 => $itemId) { $rrr2++; $temArray['smopp_ids'][] = ['id'=>$itemId['id'], 'smop_id' => $itemId['cmd_id'],'job_type_id' => $itemId['job_type_id']]; } $eee++; } $result[] = $temArray; $temArray = []; } //echo "
";
    //var_dump($itemId);
   // var_dump($result);
    //echo "
"; //echo "
"; //exit; return $result; } /* предыдущая версия function readXlsFileFirstTbl( $filePath, $arrayIds){ $Excel = \PHPExcel_IOFactory::load($filePath); $maxCell = $Excel->getActiveSheet()->getHighestRowAndColumn(); $data = $Excel->getActiveSheet()->rangeToArray('A6:E6'. $maxCell['row']); $loco_serie = $Excel->getActiveSheet()->getCell('B1')->getValue(); $repair = $Excel->getActiveSheet()->getCell('B2')->getValue(); $repair_id = \app\models\entity\Repairtypes::findOne(['name' => $repair]); $loco_serie_id = \app\models\entity\locomotiveSeries::findOne(['name' => $loco_serie]); $asusgProjecttypeEntity = \app\models\entity\AsusgProjecttype::findOne(['repair_type' => $repair_id,'loco_serie_id' => $loco_serie_id]); $asusgProjecttypeEntity_id = $asusgProjecttypeEntity->id; if ( null == $asusgProjecttypeEntity->id) { $asusgProjecttypeEntity2 = new \app\models\entity\AsusgProjecttype(); $asusgProjecttypeEntity2->repair_type = $repair_id->id; $asusgProjecttypeEntity2->loco_serie_id = $loco_serie_id->id; $asusgProjecttypeEntity2->save(); $asusgProjecttypeEntity_id = $asusgProjecttypeEntity2->id; } $data = array_map('array_filter', $data); $data = array_filter($data); $result = []; $temArray = []; $arrayIdsTemp = []; foreach( $arrayIds as $pre_item){ $arrayIdsTemp[$pre_item['id']][] = $pre_item; } $eee = 0; $point = 0; $rrr2 = 1; $namesArrayJobPlan = []; $JobPlanCount = ''; $tempArray = []; $newData = []; foreach($data as $item){ $count = 0; $tempIt = $item[0]; if (!in_array ($item[0],$tempArray)){ $newData[] = $item; } else { foreach ( $tempArray as $tempItem){ if ($item[0] == $tempItem ){ $count++; } } $item[0] .= ' ' . $count; $newData[] = $item; } $tempArray[] = $tempIt; } foreach( $newData as $key => $item){ foreach( $item as $key => $i) { if ( 0 == $key ){ // такого вы еще невидели )) $point = trim($i); } if ( 0 == $key ){ // такого вы еще невидели )) $countModel = \app\models\entity\AsusgJobplan::find()->where(['name' => $point])->count(); if ( 0 == $countModel) { $modelJP = new \app\models\entity\AsusgJobplan(); $modelJP->name = trim($point . $JobPlanCount); $modelJP->save(); } $asusgJobplanEntity = \app\models\entity\AsusgJobplan::findOne(['name' => $point]); $temArray['asusg_id'] = $asusgJobplanEntity->id; $temArray['asusg_projecttype_id'] = $asusgProjecttypeEntity_id; $temArray['cmd_order'] = $eee; $namesArrayJobPlan[] = $point; } foreach( $arrayIdsTemp[$i] as $key2 => $itemId) { // if ( 0 == $key2 ){ //echo $key2; echo "
"; //$temArray['smopp_ids'][] = $arrayIds[$i]; //} else { $rrr2++; //$temArray['smopp_ids'][] = ['smop_id' => $arrayIds['cmd_id'],'job_type_id' => $arrayIds['job_type_id']]; $temArray['smopp_ids'][] = ['smop_id' => $itemId['cmd_id'],'job_type_id' => $itemId['job_type_id']]; //$temArray['job_type_id'][] = $arrayIds[$i]['job_type_id']; //echo "
@";
                        //var_dump($result);
                        //echo "
"; //var_dump($eee); //echo "
"; //} } $eee++; } $result[] = $temArray; $temArray = []; } //echo "
";
    //var_dump($result);
    //echo "
"; // var_dump($result); //echo "
"; return $result; } */ // КОНТРОЛЬНЫЕ КАРТЫ function readXlsFileThirdTbl($filePath){ $param = ['Множественный выбор' => 2,'Выбор' => 1,'Ввод значения' => 3 ]; $Excel = \PHPExcel_IOFactory::load($filePath); //$maxCell = $Excel->setActiveSheetIndex(2); $maxCell = $Excel->setActiveSheetIndexByName('Контрольные карты'); $maxCell = $Excel->getActiveSheet()->getHighestRowAndColumn(); $data = $Excel->getActiveSheet()->rangeToArray('A3:H3' . $maxCell['row']); $result = []; foreach( $data as $item){ $tempArray = []; foreach($item as $key => $points){ if ( $key > 3 && null != $points){ $tempArray[] = $points; } } $result[$item[0]][] = [$item[0],$item[1],$item[2],$param[$item[3]],$tempArray]; } //echo "
";
    //    var_dump($result);
    //echo "
"; //exit; return $result; } // СОСТАВ КОМАНД function readXlsFileSecondTbl($filePath){ $Excel = \PHPExcel_IOFactory::load($filePath); //$maxCell = $Excel->setActiveSheetIndex(1); $maxCell = $Excel->setActiveSheetIndexByName('Команды'); $maxCell = $Excel->getActiveSheet()->getHighestRowAndColumn(); $data = $Excel->getActiveSheet()->rangeToArray('A3:F3' . $maxCell['row']); $paramArray = ['0' => 'initiator','1' => 'command_name', '2' =>'jobtype','3' => 'time_to_finish','4' =>'confirm_type','5' =>'control_map_id']; $data = array_map('array_filter', $data); $data = array_filter($data); $result = []; $temArray = []; $l = 1; foreach( $data as $k => $item){ foreach( $item as $key => $i) { if( 0 == $key) { global $id; $temArray['control_map_id'] = ''; $temArray[$paramArray[$key]] = $id; continue; } $temArray['control_map_id'] = ''; $temArray[$paramArray[$key]] = $i; } $arrTemp = explode(',',$temArray[jobtype]); if ( 1 < count($arrTemp) ){ foreach($arrTemp as $it){ $temArray['jobtype'] = trim($it); $temArray['id'] = $l; $result[]= $temArray; } continue; } $temArray['id'] = $l; $l++; $result[]= $temArray; } //echo "
";
    //    var_dump($result);
    //echo "
"; //exit; return $result; //exit; } /* первоначальная версия function readXlsFileSecondTbl($filePath){ $Excel = \PHPExcel_IOFactory::load($filePath); $maxCell = $Excel->getActiveSheet()->getHighestRowAndColumn(); $data = $Excel->getActiveSheet()->rangeToArray('G6:L6' . $maxCell['row']); $paramArray = ['0' => 'initiator','1' => 'command_name', '2' =>'jobtype','3' => 'time_to_finish','4' =>'confirm_type','5' =>'control_map_id']; $data = array_map('array_filter', $data); $data = array_filter($data); $result = []; $temArray = []; $l = 1; foreach( $data as $k => $item){ foreach( $item as $key => $i) { if( 0 == $key) { global $id; $temArray['control_map_id'] = ''; $temArray[$paramArray[$key]] = $id; continue; } $temArray['control_map_id'] = ''; $temArray[$paramArray[$key]] = $i; } $arrTemp = explode(',',$temArray[jobtype]); if ( 1 < count($arrTemp) ){ foreach($arrTemp as $it){ $temArray['jobtype'] = trim($it); $temArray['id'] = $l; $result[]= $temArray; } continue; } $temArray['id'] = $l; $l++; $result[]= $temArray; } //echo "
";
    //    var_dump($result);
    //echo "
"; return $result; //exit; } */ function writeInData($arrayCommands) { //echo '
';
    //var_dump($arrayCommands);
//echo '
'; //return true; $result = false; if (!empty($arrayCommands)){ foreach( $arrayCommands as $command){ \app\models\entity\AsusgJob2Command::deleteAll(['asusg_projecttype_id' => $command['asusg_projecttype_id']]); //\app\models\entity\AsusgJob2Project::deleteAll(['asusg_projecttype_id' => $command['asusg_projecttype_id']]); break; } $k = 0; foreach( $arrayCommands as $command){ //var_dump($command); echo "
"; /* $asusgJob2ProjectEntity = new \app\models\entity\AsusgJob2Project(); $asusgJob2ProjectEntity->asusg_jobplan_id = $command['asusg_id']; $asusgJob2ProjectEntity->asusg_projecttype_id = $command['asusg_projecttype_id']; $asusgJob2ProjectEntity->job_order = $k; $k++; try { $asusgJob2ProjectEntity->save(); } catch (\yii\db\Exception $exception) { $loadFileError .= 'Повторная запись.'; //return false; } */ //echo "
";
            //    var_dump($command);
            //echo "
"; foreach( $command['smopp_ids'] as $key => $com){ $asusgJob2CommandEntity = new \app\models\entity\AsusgJob2Command(); $asusgJob2CommandEntity->asusg_job_id = $command['asusg_id']; $asusgJob2CommandEntity->asusg_projecttype_id = $command['asusg_projecttype_id']; $asusgJob2CommandEntity->smopp_cmd_id = $com["smop_id"]; $asusgJob2CommandEntity->jobtype_id = $com["job_type_id"]; $asusgJob2CommandEntity->cmd_order = $command["cmd_order"];//$key; $asusgJob2CommandEntity->job_order = $k; $asusgJob2CommandEntity->original_order = $com["id"]; //echo "
";
            //    var_dump($com["id"]);
            //echo "
"; try { $asusgJob2CommandEntity->save(); } catch (\yii\db\Exception $exception) { $loadFileError .= 'Повторная запись.'; //return false; } } $k++; $result = true; } } return $result; } function writeInDataWide($arrayCommands,$arrayThirdPoints) { global $link; $result = false; $tempArr = []; /* if (!empty($arrayCommands)){ foreach( $arrayCommands as $command){ foreach( $command['smopp_ids'] as $key => $com){ $asusgJob2CommandEntity = new \app\models\entity\AsusgJob2Command(); $asusgJob2CommandEntity->asusg_job_id = $command['asusg_id']; $asusgJob2CommandEntity->smopp_cmd_id = $com; $asusgJob2CommandEntity->cmd_order = $key; try { $asusgJob2CommandEntity->save(); } catch (\yii\db\Exception $exception) { $loadFileError .= 'Повторная запись.'; return false; } //var_dump($command['asusg_id'],$com,$key); echo "
"; } $result = true; } }*/ $i = 1; $controlMapTempArray = []; foreach( $arrayCommands as $command){ $command['time_to_finish'] = round($command['time_to_finish']); $command['jobtype'] = \app\models\HelperModel::stripWhitespaces($command['jobtype']); $query = "call `asusg_add_command_type`( " . $command['initiator'] . ",'" . $command['command_name'] . "','" . $command['jobtype'] . "'," . $command['time_to_finish'] . ",'" . $command['confirm_type'] . "'," . (int)$command['control_map_id'].", @cmd_id, @job_type_id)"; mysqli_query($link, "SET @cmd_id = '', @job_type_id = ''"); $res = mysqli_query($link, $query); $res = mysqli_query($link, "SELECT @cmd_id as _asusg_add_command_type_out, @job_type_id as _asusg_add_command_type_out2"); $row = $res->fetch_assoc(); $tempArr[$i] = [ 'cmd_id' => $row['_asusg_add_command_type_out'] , 'job_type_id' => $row['_asusg_add_command_type_out2'],'id' => $command['id']]; $querydel = "delete from checkpoint_types_for_tasks where tasktype_id = " . $row['_asusg_add_command_type_out'] . " and cp_type_id > 0"; mysqli_query($link, $querydel); // CHECKPOINTS if ( isset($command['control_map_id']) and null != $command['control_map_id']) { foreach ($arrayThirdPoints[$command['control_map_id']] as $checpoint){ mysqli_query($link, "SET @checkoipt_id = ''"); $query2 = "call asusg_add_checkpoint_type(".$row['_asusg_add_command_type_out'].",'".$checpoint[2]."',".$checpoint[3].", @checkpoint_id)"; //$query2 = "call asusg_add_checkpoint_type(".$row['_asusg_add_command_type_out'].",'" . $checpoint[0] . ". ".$checpoint[2]."',".$checpoint[3].", @checkpoint_id)"; $res2 = mysqli_query($link, $query2); $res2 = mysqli_query($link, "SELECT @checkpoint_id as checkpoint"); $row2 = $res2->fetch_assoc(); foreach($checpoint[4] as $p) { $query3 = "call asusg_add_checkbox_to_checkpoint(" . $row2['checkpoint'] . ",'" . $p . "')"; mysqli_query($link, $query3); //echo "
";
                    //var_dump("call asusg_add_checkbox_to_checkpoint(" . $row2['checkpoint'] . ",'" . $p . "')");
                    //echo "
"; } } } // CHECKPOINTS $i++; //\Yii::$app->db->createCommand($query)->queryAll(); } //var_dump(gettype($res)); //echo "
";
     //var_dump($tempArr);
     //echo "
"; //exit; return $tempArr; } ?>