/**
* @param $objPHPExcel
* @param null $ID
*/
protected function Body()
{
$ID = $this->getDopparamID();
$this->setReportName('Акт передачи материалов сторонней организации №' . $ID);
$Recoverysendakt = Recoverysendakt::findOne($ID);
$Recoveryrecieveaktmat = Recoveryrecieveaktmat::findAll(['id_recoverysendakt' => $ID]);
$Mols = Recoveryrecieveaktmat::getMolsByRecoverysendakt($ID);
$objPHPExcel = $this->getObjPHPExcel();
$objPHPExcel->getActiveSheet()->setCellValue('A3', 'сторонней организации № ' . $Recoverysendakt->recoverysendakt_id . ' от ' . Yii::$app->formatter->asDate($Recoverysendakt->recoverysendakt_date));
$objPHPExcel->getActiveSheet()->setCellValue('C4', $Recoverysendakt->idOrgan->organ_name);
$material_tip = Material::VariablesValues('material_tip');
$num = 9;
foreach ($Recoveryrecieveaktmat as $ar) {
$objPHPExcel->getActiveSheet()->insertNewRowBefore($num);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $num, $num - 8);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, $num, $ar->idTrMatOsmotr->idTrMat->idMattraffic->idMaterial->idMatv->matvid_name);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, $num, $ar->idTrMatOsmotr->idTrMat->idMattraffic->idMaterial->material_name);
$objPHPExcel->getActiveSheet()->setCellValueExplicitByColumnAndRow(3, $num, $ar->idTrMatOsmotr->idTrMat->idMattraffic->idMaterial->material_inv, \PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(4, $num, $material_tip[$ar->idTrMatOsmotr->idTrMat->idMattraffic->idMaterial->material_tip]);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(5, $num, $ar->idTrMatOsmotr->tr_mat_osmotr_number);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(6, $num, $ar->idTrMatOsmotr->idTrMat->idMattraffic->idMaterial->idIzmer->izmer_name);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(7, $num, $ar->idTrMatOsmotr->idReason->reason_text . (empty($ar->idTrMatOsmotr->idReason->reason_text) ? '' : '. ') . $ar->idTrMatOsmotr->tr_mat_osmotr_comment);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(8, $num, $ar->idTrMatOsmotr->idTrMat->idMattraffic->idMol->idperson->auth_user_fullname . ', ' . $ar->idTrMatOsmotr->idTrMat->idMattraffic->idMol->iddolzh->dolzh_name);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(9, $num, TrMatOsmotr::getBuildandKabByTrMatOsmotr($ar->idTrMatOsmotr->tr_mat_osmotr_id));
$objPHPExcel->getActiveSheet()->getStyle('A' . $num . ':J' . $num)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
$num++;
}
$objPHPExcel->getActiveSheet()->removeRow($num);
$crows = count($Recoveryrecieveaktmat);
$num = 10;
foreach ($Mols as $ar) {
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $num + $crows, 'Материально ответственное лицо');
$objPHPExcel->getActiveSheet()->mergeCellsByColumnAndRow(0, $num + $crows, 1, $num + $crows);
$objPHPExcel->getActiveSheet()->mergeCellsByColumnAndRow(3, $num + $crows, 6, $num + $crows);
$objPHPExcel->getActiveSheet()->mergeCellsByColumnAndRow(7, $num + $crows, 9, $num + $crows);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(3, $num + $crows, $ar['dolzh_name']);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(7, $num + $crows, $ar['auth_user_fullname']);
$objPHPExcel->getActiveSheet()->insertNewRowBefore($num + $crows + 1);
$num++;
}
$objPHPExcel->getActiveSheet()->removeRow($num + $crows);
}
protected function Body()
{
$ID = $this->getDopparamID();
$this->setReportName('Акт снятия комплектующих с матер-ых цен-тей №' . $ID);
$Removeakt = Removeakt::findOne($ID);
$Trmat = TrMat::find()->innerJoinWith(['trRmMats'])->andWhere(['trRmMats.id_removeakt' => $ID])->GroupBy('id_parent')->all();
$objPHPExcel = $this->getObjPHPExcel();
$objPHPExcel->getActiveSheet()->setCellValue('A3', 'комплектующих № ' . $Removeakt->removeakt_id . ' от ' . Yii::$app->formatter->asDate($Removeakt->removeakt_date));
$num = 5;
$c_Trmat = count($Trmat);
if ($c_Trmat > 0) {
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $num, 'Снятие комплектующих');
$objPHPExcel->getActiveSheet()->mergeCellsByColumnAndRow(0, $num, 10, $num);
$this->setStyle(self::$TITLE, 'A' . $num . ':K' . $num);
$num++;
foreach ($Trmat as $arm) {
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $num, 'Материальная ценность');
$objPHPExcel->getActiveSheet()->mergeCellsByColumnAndRow(0, $num, 10, $num);
$this->setStyle(self::$TITLELEFT, 'A' . $num . ':K' . $num);
$num++;
$this->SetTitlebyArray(['№', 'Вид', 'Наименование', 'Инвентарный номер', 'Серийный номер', 'Год выпуска', 'Стоимость', 'Здание', 'Кабинет', 'Материально-ответственное лицо', 'Тип'], $num);
$this->setStyle(self::$CAPTION, 'A' . $num . ':K' . $num);
$this->CellsWrapAndTop('A' . $num . ':K' . $num);
$num++;
$this->setColumnNumbers($num, 11);
$this->setStyle(self::$NUMS, 'A' . $num . ':K' . $num);
$this->CellsWrapAndTop('A' . $num . ':K' . $num);
$num++;
$Matparent = TrOsnov::find()->joinWith(['idMattraffic', 'idInstallakt'])->andWhere(['idMattraffic.id_material' => $arm->id_parent])->orderBy(['idInstallakt.installakt_date' => SORT_DESC])->one();
$material_tip = Material::VariablesValues('material_tip');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $num, 1);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, $num, $arm->idParent->idMaterial->idMatv->matvid_name);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, $num, $arm->idParent->idMaterial->material_name);
$objPHPExcel->getActiveSheet()->setCellValueExplicitByColumnAndRow(3, $num, $arm->idParent->idMaterial->material_inv, \PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(4, $num, $arm->idParent->idMaterial->material_serial);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(5, $num, Yii::$app->formatter->asDate($arm->idParent->idMaterial->material_release, 'y'));
$objPHPExcel->getActiveSheet()->setCellValueExplicitByColumnAndRow(6, $num, $arm->idParent->idMaterial->material_price, \PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(7, $num, $arm->idParent->idMol->idbuild->build_name);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(8, $num, $arm->idParent->trOsnovs[0]->tr_osnov_kab);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(9, $num, $arm->idParent->idMol->idperson->auth_user_fullname . ', ' . $arm->idParent->idMol->iddolzh->dolzh_name);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(10, $num, $material_tip[$arm->idParent->idMaterial->material_tip]);
$this->setStyle(self::$DATA, 'A' . $num . ':K' . $num);
$this->CellsWrapAndTop('A' . $num . ':K' . $num);
$num++;
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $num, 'Снятые комплектующие');
$objPHPExcel->getActiveSheet()->mergeCellsByColumnAndRow(0, $num, 10, $num);
$this->setStyle(self::$TITLELEFT, 'A' . $num . ':K' . $num);
$num++;
$this->SetTitlebyArray(['№', 'Вид', 'Наименование', 'Инвентарный номер', 'Серийный номер', 'Кол-во', 'Единица измерения', 'Год выпуска', 'Стоимость', 'Материально-ответственное лицо', 'Тип'], $num);
$this->setStyle(self::$CAPTION, 'A' . $num . ':K' . $num);
$this->CellsWrapAndTop('A' . $num . ':K' . $num);
$num++;
$this->setColumnNumbers($num, 11);
$this->setStyle(self::$NUMS, 'A' . $num . ':K' . $num);
$this->CellsWrapAndTop('A' . $num . ':K' . $num);
$num++;
$MatbyParent = TrMat::find()->joinWith(['trRmMats'])->andWhere(['id_parent' => $arm->idParent->primaryKey, 'trRmMats.id_removeakt' => $Removeakt->removeakt_id])->all();
$startrow = $num;
foreach ($MatbyParent as $ar) {
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $num, $num - $startrow + 1);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, $num, $ar->idMattraffic->idMaterial->idMatv->matvid_name);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, $num, $ar->idMattraffic->idMaterial->material_name);
$objPHPExcel->getActiveSheet()->setCellValueExplicitByColumnAndRow(3, $num, $ar->idMattraffic->idMaterial->material_inv, \PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(4, $num, $ar->idMattraffic->idMaterial->material_serial);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(5, $num, $ar->idMattraffic->mattraffic_number);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(6, $num, $ar->idMattraffic->idMaterial->idIzmer->izmer_name);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(7, $num, Yii::$app->formatter->asDate($ar->idMattraffic->idMaterial->material_release, 'y'));
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(8, $num, $ar->idMattraffic->idMaterial->material_price);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(9, $num, $ar->idMattraffic->idMol->idperson->auth_user_fullname . ', ' . $ar->idMattraffic->idMol->iddolzh->dolzh_name);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(10, $num, $material_tip[$ar->idMattraffic->idMaterial->material_tip]);
$num++;
}
if (count($MatbyParent) > 0) {
$this->setStyle(self::$DATA, 'A' . $startrow . ':K' . ($num - 1));
}
$this->CellsWrapAndTop('A' . $startrow . ':K' . $num);
$num++;
}
}
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, $num, '(Подпись)');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(3, $num, '(Должность)');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(7, $num, '(Ф.И.О.)');
$objPHPExcel->getActiveSheet()->mergeCellsByColumnAndRow(3, $num, 6, $num);
$objPHPExcel->getActiveSheet()->mergeCellsByColumnAndRow(7, $num, 10, $num);
$this->setStyle(self::$SIGN, 'A' . $num . ':K' . $num);
$num++;
$Mols = Removeakt::getMolsByRemoveakt($Removeakt->removeakt_id);
$startrow = $num;
foreach ($Mols as $ar) {
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $num, 'Материально ответственное лицо');
$objPHPExcel->getActiveSheet()->mergeCellsByColumnAndRow(0, $num, 1, $num);
$objPHPExcel->getActiveSheet()->mergeCellsByColumnAndRow(3, $num, 6, $num);
$objPHPExcel->getActiveSheet()->mergeCellsByColumnAndRow(7, $num, 10, $num);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(3, $num, $ar->dolzh_name_tmp);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(7, $num, $ar->auth_user_fullname_tmp);
$this->CellsWrapAndTop('A' . $num . ':K' . $num);
$objPHPExcel->getActiveSheet()->getRowDimension($num)->setRowHeight(45.75);
$num++;
}
$this->setStyle(self::$TITLELEFT, 'A' . $startrow . ':B' . ($num - 1));
$this->setStyle(self::$SIGNDATA, 'C' . $startrow . ':K' . ($num - 1));
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $num, 'Демонтажник');
$this->setStyle(self::$TITLELEFT, 'A' . $num . ':B' . $num);
$objPHPExcel->getActiveSheet()->mergeCellsByColumnAndRow(0, $num, 1, $num);
$this->setStyle(self::$SIGNDATA, 'C' . $num . ':K' . $num);
$Remover = Employee::findOne($Removeakt->id_remover);
$objPHPExcel->getActiveSheet()->mergeCellsByColumnAndRow(3, $num, 6, $num);
$objPHPExcel->getActiveSheet()->mergeCellsByColumnAndRow(7, $num, 10, $num);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(3, $num, $Remover->iddolzh->dolzh_name);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(7, $num, $Remover->idperson->auth_user_fullname);
$this->CellsWrapAndTop('A' . $num . ':K' . $num);
$objPHPExcel->getActiveSheet()->getRowDimension($num)->setRowHeight(45.75);
}
/**
* @depends loadData
*/
public function destroyData()
{
Mattraffic::deleteAll();
Material::deleteAll();
Employee::deleteAll();
Matvid::deleteAll();
Izmer::deleteAll();
Schetuchet::deleteAll();
Authuser::deleteAll('auth_user_id <> 1');
Build::deleteAll();
Dolzh::deleteAll();
Podraz::deleteAll();
}
public static function Recoverysendakt_ExportExcel()
{
$objPHPExcel = new \PHPExcel();
$reportName = 'Выгрузка';
/* Границы таблицы */
$ramka = array('borders' => array('allborders' => ['style' => \PHPExcel_Style_Border::BORDER_THIN]));
/* Жирный шрифт для шапки таблицы */
$font = array('font' => array('bold' => true), 'alignment' => array('horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_CENTER));
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, 1, $reportName);
$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(0, 1)->applyFromArray(['font' => ['bold' => true, 'size' => 14]]);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, 2, 'Дата: ' . date('d.m.Y'));
$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(0, 2)->applyFromArray(['font' => ['italic' => true]]);
$params = Yii::$app->request->queryParams;
$inputdata = json_decode($params['inputdata']);
// $fields = Proc::GetArrayValuesByKeyName($modelName, $inputdata);
// $selectvalues = (array) $selectvalues;
$filter = 'Фильтр:';
/* foreach ($fields[$modelName] as $attr => $value) {
$val_result = $value;
if (!empty($value)) {
if (isset($selectvalues[$modelName . '[' . $attr . ']']))
$val_result = $selectvalues[$modelName . '[' . $attr . ']'][$fields[$modelName][$attr]];
$filter .= ' ' . $labels[$attr] . ': "' . $val_result . '";';
}
} */
/* if ($ModelFilter instanceof Model) {
$dopfilter = self::ConstructFilterOutput($ModelFilter);
if (!empty($dopfilter))
$filter .= ' ' . $dopfilter;
} */
$num = 5;
// $objPHPExcel->getActiveSheet()->mergeCellsByColumnAndRow(0, 1, 10, 1);
self::SetTitlebyArray($objPHPExcel, ['№', 'Тип материальной ценности', 'Вид материальной ценности', 'Наименование', 'Инвентарный номер', 'Серийный номер', 'Дата выпуска', 'Стоимость', 'Списание', 'Количество', 'Единица измерения', 'Материально-ответственное лицо', 'Здание', 'Кабинет', 'Укомплектовано в', 'Инвентарный номер мат-ой цен-ти в которую укомплектовано', 'Номер акта осмотра', 'Дата акта осмотра', 'Вид акта осмотра', 'Мастер', 'Причина неисправности', 'Пользователь', 'Организация', 'Дата отправки', 'Дата получения', 'Результат', 'Подлежит восстановлению'], $num);
for ($i = 0; $i <= 26; $i++) {
$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($i, $num)->applyFromArray($font);
}
$num++;
$Recoveryrecieveakt = Recoveryrecieveakt::find()->joinWith(['idRecoverysendakt.idOrgan', 'idOsmotrakt.idReason', 'idOsmotrakt.idUser idUser', 'idOsmotrakt.idMaster idMaster', 'idOsmotrakt.idUser.idperson idpersonuser', 'idOsmotrakt.idUser.iddolzh iddolzhuser', 'idOsmotrakt.idMaster.idperson idpersonmaster', 'idOsmotrakt.idMaster.iddolzh iddolzhmaster', 'idOsmotrakt.idTrosnov.idMattraffic.idMaterial.idMatv', 'idOsmotrakt.idTrosnov.idMattraffic.idMaterial.idIzmer', 'idOsmotrakt.idTrosnov.idMattraffic.idMol.idperson', 'idOsmotrakt.idTrosnov.idMattraffic.idMol.iddolzh', 'idOsmotrakt.idTrosnov.idMattraffic.idMol.idbuild'])->orderBy(['idOsmotrakt.osmotrakt_date' => SORT_ASC])->all();
$material_tip = Material::VariablesValues('material_tip');
$material_writeoff = Material::VariablesValues('material_writeoff');
$recoveryrecieveakt_repaired = Recoveryrecieveakt::VariablesValues('recoveryrecieveakt_repaired');
foreach ($Recoveryrecieveakt as $ar) {
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $num, $num - 5);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, $num, $material_tip[$ar->idOsmotrakt->idTrosnov->idMattraffic->idMaterial->material_tip]);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, $num, $ar->idOsmotrakt->idTrosnov->idMattraffic->idMaterial->idMatv->matvid_name);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(3, $num, $ar->idOsmotrakt->idTrosnov->idMattraffic->idMaterial->material_name);
$objPHPExcel->getActiveSheet()->setCellValueExplicitByColumnAndRow(4, $num, $ar->idOsmotrakt->idTrosnov->idMattraffic->idMaterial->material_inv, \PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->getActiveSheet()->setCellValueExplicitByColumnAndRow(5, $num, $ar->idOsmotrakt->idTrosnov->idMattraffic->idMaterial->material_serial, \PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(6, $num, Yii::$app->formatter->asDate($ar->idOsmotrakt->idTrosnov->idMattraffic->idMaterial->material_release));
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(7, $num, $ar->idOsmotrakt->idTrosnov->idMattraffic->idMaterial->material_price);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(8, $num, $material_writeoff[$ar->idOsmotrakt->idTrosnov->idMattraffic->idMaterial->material_writeoff]);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(9, $num, 1);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(10, $num, $ar->idOsmotrakt->idTrosnov->idMattraffic->idMaterial->idIzmer->izmer_name);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(11, $num, $ar->idOsmotrakt->idTrosnov->idMattraffic->idMol->idperson->auth_user_fullname . ', ' . $ar->idOsmotrakt->idTrosnov->idMattraffic->idMol->iddolzh->dolzh_name);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(12, $num, empty($ar->idOsmotrakt->idTrosnov->idMattraffic->idMol->idbuild->build_name) ? '' : $ar->idOsmotrakt->idTrosnov->idMattraffic->idMol->idbuild->build_name);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(13, $num, $ar->idOsmotrakt->idTrosnov->tr_osnov_kab);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(14, $num, '');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(15, $num, '');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(16, $num, $ar->idOsmotrakt->osmotrakt_id);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(17, $num, Yii::$app->formatter->asDate($ar->idOsmotrakt->osmotrakt_date));
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(18, $num, 'Материальная ценность');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(19, $num, $ar->idOsmotrakt->idMaster->idperson->auth_user_fullname . ', ' . $ar->idOsmotrakt->idMaster->iddolzh->dolzh_name);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(20, $num, (empty($ar->idOsmotrakt->idReason->reason_text) ? '' : $ar->idOsmotrakt->idReason->reason_text . ', ') . $ar->idOsmotrakt->osmotrakt_comment);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(21, $num, $ar->idOsmotrakt->idUser->idperson->auth_user_fullname . ', ' . $ar->idOsmotrakt->idUser->iddolzh->dolzh_name);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(22, $num, $ar->idRecoverysendakt->idOrgan->organ_name);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(23, $num, Yii::$app->formatter->asDate($ar->idRecoverysendakt->recoverysendakt_date));
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(24, $num, Yii::$app->formatter->asDate($ar->recoveryrecieveakt_date));
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(25, $num, $ar->recoveryrecieveakt_result);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(26, $num, empty($ar->recoveryrecieveakt_repaired) ? '' : $recoveryrecieveakt_repaired[$ar->recoveryrecieveakt_repaired]);
$num++;
}
$Recoveryrecieveaktmat = Recoveryrecieveaktmat::find()->joinWith(['idRecoverysendakt.idOrgan', 'idTrMatOsmotr.idReason', 'idTrMatOsmotr.idOsmotraktmat.idMaster idMaster', 'idTrMatOsmotr.idOsmotraktmat.idMaster.idperson idpersonmaster', 'idTrMatOsmotr.idOsmotraktmat.idMaster.iddolzh iddolzhmaster', 'idTrMatOsmotr.idTrMat.idMattraffic.idMaterial.idMatv', 'idTrMatOsmotr.idTrMat.idMattraffic.idMaterial.idIzmer', 'idTrMatOsmotr.idTrMat.idMattraffic.idMol.idperson', 'idTrMatOsmotr.idTrMat.idMattraffic.idMol.iddolzh', 'idTrMatOsmotr.idTrMat.idMattraffic.idMol.idbuild', 'idTrMatOsmotr.idTrMat.idParent idParent'])->orderBy(['idOsmotraktmat.osmotraktmat_date' => SORT_ASC])->all();
$recoveryrecieveaktmat_repaired = Recoveryrecieveaktmat::VariablesValues('recoveryrecieveaktmat_repaired');
foreach ($Recoveryrecieveaktmat as $ar) {
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $num, $num - 5);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, $num, $material_tip[$ar->idTrMatOsmotr->idTrMat->idMattraffic->idMaterial->material_tip]);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, $num, $ar->idTrMatOsmotr->idTrMat->idMattraffic->idMaterial->idMatv->matvid_name);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(3, $num, $ar->idTrMatOsmotr->idTrMat->idMattraffic->idMaterial->material_name);
$objPHPExcel->getActiveSheet()->setCellValueExplicitByColumnAndRow(4, $num, $ar->idTrMatOsmotr->idTrMat->idMattraffic->idMaterial->material_inv, \PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->getActiveSheet()->setCellValueExplicitByColumnAndRow(5, $num, $ar->idTrMatOsmotr->idTrMat->idMattraffic->idMaterial->material_serial, \PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(6, $num, Yii::$app->formatter->asDate($ar->idTrMatOsmotr->idTrMat->idMattraffic->idMaterial->material_release));
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(7, $num, $ar->idTrMatOsmotr->idTrMat->idMattraffic->idMaterial->material_price);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(8, $num, $material_writeoff[$ar->idTrMatOsmotr->idTrMat->idMattraffic->idMaterial->material_writeoff]);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(9, $num, $ar->idTrMatOsmotr->tr_mat_osmotr_number);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(10, $num, $ar->idTrMatOsmotr->idTrMat->idMattraffic->idMaterial->idIzmer->izmer_name);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(11, $num, $ar->idTrMatOsmotr->idTrMat->idMattraffic->idMol->idperson->auth_user_fullname . ', ' . $ar->idTrMatOsmotr->idTrMat->idMattraffic->idMol->iddolzh->dolzh_name);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(12, $num, empty($ar->idTrMatOsmotr->idTrMat->idMattraffic->idMol->idbuild->build_name) ? '' : $ar->idTrMatOsmotr->idTrMat->idMattraffic->idMol->idbuild->build_name);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(13, $num, TrMatOsmotr::getBuildandKabByTrMatOsmotr($ar->id_tr_mat_osmotr));
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(14, $num, $ar->idTrMatOsmotr->idTrMat->idParent->material_name);
$objPHPExcel->getActiveSheet()->setCellValueExplicitByColumnAndRow(15, $num, $ar->idTrMatOsmotr->idTrMat->idParent->material_inv, \PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(16, $num, $ar->idTrMatOsmotr->idOsmotraktmat->osmotraktmat_id);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(17, $num, Yii::$app->formatter->asDate($ar->idTrMatOsmotr->idOsmotraktmat->osmotraktmat_date));
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(18, $num, 'Материал');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(19, $num, $ar->idTrMatOsmotr->idOsmotraktmat->idMaster->idperson->auth_user_fullname . ', ' . $ar->idTrMatOsmotr->idOsmotraktmat->idMaster->iddolzh->dolzh_name);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(20, $num, (empty($ar->idTrMatOsmotr->idReason->reason_text) ? '' : $ar->idTrMatOsmotr->idReason->reason_text . ', ') . $ar->idTrMatOsmotr->tr_mat_osmotr_comment);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(21, $num, '');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(22, $num, $ar->idRecoverysendakt->idOrgan->organ_name);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(23, $num, Yii::$app->formatter->asDate($ar->idRecoverysendakt->recoverysendakt_date));
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(24, $num, Yii::$app->formatter->asDate($ar->recoveryrecieveaktmat_date));
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(25, $num, $ar->recoveryrecieveaktmat_result);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(26, $num, empty($ar->recoveryrecieveaktmat_repaired) ? '' : $recoveryrecieveaktmat_repaired[$ar->recoveryrecieveaktmat_repaired]);
$num++;
}
/* Авторазмер колонок Excel */
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(0)->setWidth(6);
$objPHPExcel->getActiveSheet()->getStyle('A5:AA' . ($num - 1))->applyFromArray($ramka);
for ($i = 1; $i <= 26; $i++) {
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn($i)->setAutoSize(true);
}
/* if ($filter !== 'Фильтр:') {
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, 3, $filter);
$objPHPExcel->getActiveSheet()->mergeCellsByColumnAndRow(0, 3, $i, 3);
$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(0, 3)->applyFromArray([
'font' => [
'italic' => true
]
]);
} */
// присваиваем имя файла от имени модели
$FileName = $reportName;
// Устанавливаем имя листа
$objPHPExcel->getActiveSheet()->setTitle($FileName);
// Выбираем первый лист
$objPHPExcel->setActiveSheetIndex(0);
// Формируем файл Excel
$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$FileName = DIRECTORY_SEPARATOR === '/' ? $FileName : mb_convert_encoding($FileName, 'Windows-1251', 'UTF-8');
// Proc::SaveFileIfExists() - Функция выводит подходящее имя файла, которое еще не существует. mb_convert_encoding() - Изменяем кодировку на кодировку Windows
$fileroot = Proc::SaveFileIfExists('files/' . $FileName . '.xlsx');
// Сохраняем файл в папку "files"
$objWriter->save('files/' . $fileroot);
// Возвращаем имя файла Excel
if (DIRECTORY_SEPARATOR === '/') {
echo $fileroot;
} else {
echo mb_convert_encoding($fileroot, 'UTF-8', 'Windows-1251');
}
}
/**
* @return \yii\db\ActiveQuery
*/
public function getMaterials()
{
return $this->hasMany(Material::className(), ['id_matvid' => 'matvid_id'])->from(['materials' => Material::tableName()]);
}
use app\models\Fregat\Material;
use app\models\Fregat\Employee;
/* @var $this yii\web\View */
/* @var $searchModel app\models\Fregat\MattrafficSearch */
/* @var $dataProvider yii\data\ActiveDataProvider */
$this->title = 'Движение материальных ценностей';
$this->params['breadcrumbs'] = Proc::Breadcrumbs($this);
?>
<div class="mattraffic-index">
<?php
$result = Proc::GetLastBreadcrumbsFromSession();
$foreign = isset($result['dopparams']['foreign']) ? $result['dopparams']['foreign'] : '';
$mattraffic_tip = Mattraffic::VariablesValues('mattraffic_tip');
$material_tip = Material::VariablesValues('material_tip');
$material_writeoff = Material::VariablesValues('material_writeoff');
$material_importdo = Material::VariablesValues('material_importdo');
$employee_importdo = Employee::VariablesValues('employee_importdo');
echo DynaGrid::widget(Proc::DGopts(['options' => ['id' => 'mattrafficgrid'], 'columns' => Proc::DGcols(['buttonsfirst' => true, 'buttons' => empty($foreign) ? [] : ['chooseajax' => ['Fregat/mattraffic/assign-to-select2']], 'buttonsfirst' => true, 'columns' => [['attribute' => 'mattraffic_date', 'format' => 'date'], ['attribute' => 'mattraffic_tip', 'filter' => $mattraffic_tip, 'value' => function ($model) use($mattraffic_tip) {
return isset($mattraffic_tip[$model->mattraffic_tip]) ? $mattraffic_tip[$model->mattraffic_tip] : '';
}], 'mattraffic_number', ['attribute' => 'idMaterial.material_tip', 'filter' => $material_tip, 'value' => function ($model) use($material_tip) {
return isset($material_tip[$model->idMaterial->material_tip]) ? $material_tip[$model->idMaterial->material_tip] : '';
}], ['attribute' => 'idMaterial.idMatv.matvid_name', 'visible' => false], 'idMaterial.material_name', 'idMaterial.material_inv', ['attribute' => 'idMaterial.material_serial', 'visible' => false], ['attribute' => 'idMaterial.material_release', 'format' => 'date', 'visible' => false], 'idMaterial.material_number', 'idMaterial.idIzmer.izmer_name', 'idMaterial.material_price', ['attribute' => 'idMol.employee_id', 'visible' => false], 'idMol.idperson.auth_user_fullname', 'idMol.iddolzh.dolzh_name', 'idMol.idpodraz.podraz_name', 'idMol.idbuild.build_name', ['attribute' => 'idMol.employee_dateinactive', 'format' => 'date', 'visible' => false], ['attribute' => 'idMaterial.material_writeoff', 'filter' => $material_writeoff, 'value' => function ($model) use($material_writeoff) {
return isset($material_writeoff[$model->idMaterial->material_writeoff]) ? $material_writeoff[$model->idMaterial->material_writeoff] : '';
}, 'visible' => false], ['attribute' => 'idMaterial.material_username', 'visible' => false], ['attribute' => 'idMaterial.material_lastchange', 'format' => 'datetime', 'visible' => false], ['attribute' => 'idMaterial.material_importdo', 'filter' => $material_importdo, 'value' => function ($model) use($material_importdo) {
return isset($material_importdo[$model->idMaterial->material_importdo]) ? $material_importdo[$model->idMaterial->material_importdo] : '';
}, 'visible' => false], ['attribute' => 'idMol.employee_username', 'visible' => false], ['attribute' => 'idMol.employee_lastchange', 'format' => 'datetime', 'visible' => false], ['attribute' => 'idMol.employee_importdo', 'filter' => $employee_importdo, 'value' => function ($model) use($employee_importdo) {
return isset($employee_importdo[$model->idMol->employee_importdo]) ? $employee_importdo[$model->idMol->employee_importdo] : '';
}, 'visible' => false], ['attribute' => 'mattraffic_username', 'visible' => false], ['attribute' => 'mattraffic_lastchange', 'format' => 'datetime', 'visible' => false]]]), 'gridOptions' => ['dataProvider' => $dataProvider, 'filterModel' => $searchModel, 'rowOptions' => function ($model, $index, $widget, $grid) {
$class = [];
/* if ($model->idMaterial->material_tip == 1) {
$class = ['class' => 'warning'];
public function actionFillnewinstallakt()
{
if (Yii::$app->request->isAjax) {
$id_mattraffic = Yii::$app->request->post('id_mattraffic');
if (!empty($id_mattraffic)) {
$query = Mattraffic::findOne($id_mattraffic);
if (!empty($query)) {
$material_writeoff = Material::VariablesValues('material_writeoff');
echo json_encode(['material_id' => $query->id_material, 'material_name' => $query->idMaterial->material_name, 'material_writeoff' => $material_writeoff[$query->idMaterial->material_writeoff], 'auth_user_fullname' => $query->idMol->idperson->auth_user_fullname, 'dolzh_name' => $query->idMol->iddolzh->dolzh_name, 'build_name' => $query->idMol->idbuild->build_name, 'mattraffic_number' => $query->mattraffic_number]);
}
}
}
}
public static function VariablesValues($attribute, $value = NULL)
{
$values = ['mat_id_material' => [$value => Material::getMaterialByID($value)], 'mol_id_person' => [$value => Authuser::getAuthuserByID($value)], 'mat_id_material_mat' => [$value => Material::getMaterialByID($value)], 'mol_id_person_mat' => [$value => Authuser::getAuthuserByID($value)], 'recoveryrecieveakt_repaired' => Recoveryrecieveakt::VariablesValues($attribute)];
return isset($values[$attribute]) ? $values[$attribute] : NULL;
}
static function ImportDo()
{
// Делаем запись в таблицу отчетов импорта
$logreport = new Logreport();
$Importconfig = self::GetRowsPDO('select * from importconfig where importconfig_id = 1');
if ($Importconfig['importconfig_do']) {
self::$os_start = $Importconfig['os_startrow'];
self::$mat_start = $Importconfig['mat_startrow'];
self::$gu_start = $Importconfig['gu_startrow'];
self::$Mishanya = 0;
self::$Debug = YII_DEBUG;
$starttime = microtime(true);
$logreport->logreport_date = date('Y-m-d');
$doreport = false;
self::$materialexists = Material::find()->count() > 0;
self::DeleteOldReports();
// Идем по файлам импорта из 1С (os.xls - Основные средства, mat.xls - Материалы)
foreach ([$Importconfig['emp_filename'] . '.txt', $Importconfig['os_filename'] . '.xlsx', $Importconfig['mat_filename'] . '.xlsx', $Importconfig['gu_filename'] . '.xlsx'] as $filename) {
self::$filename = dirname($_SERVER['SCRIPT_FILENAME']) . '/imp/' . $filename;
$FileType = self::$filename === dirname($_SERVER['SCRIPT_FILENAME']) . '/imp/' . $Importconfig['os_filename'] . '.xlsx' ? self::os : (self::$filename === dirname($_SERVER['SCRIPT_FILENAME']) . '/imp/' . $Importconfig['mat_filename'] . '.xlsx' ? self::mat : self::gu);
self::SetFileType($FileType);
self::Setxls();
if (file_exists(self::$filename)) {
self::$filelastdate = date("Y-m-d H:i:s", filemtime(self::$filename));
$filelastdateFromDB = self::GetMaxFileLastDate($Importconfig);
if (self::$Debug || empty($filelastdateFromDB) || strtotime(self::$filelastdate) > strtotime($filelastdateFromDB)) {
/* var_dump(self::$filename);
var_dump(self::$filelastdate);
var_dump($filelastdateFromDB);
var_dump($_SERVER);
var_dump($_ENV); */
ini_set('max_execution_time', $Importconfig['max_execution_time']);
// 1000 seconds
ini_set('memory_limit', $Importconfig['memory_limit']);
// 1Gbyte Max Memory
$logreport->save();
self::$logreport_id = $logreport->logreport_id;
$doreport = true;
// Определяем показатели импорта
self::$logreport_errors = 0;
// Не загружено записей из-за ошибок
self::$logreport_updates = 0;
// Записей изменено
self::$logreport_additions = 0;
// Записей добавлено
self::$logreport_missed = 0;
// Записей пропущено (исключены из обработки)
self::$logreport_amount = 0;
// Всего записей
if ($filename === $Importconfig['emp_filename'] . '.txt') {
self::$employee = true;
}
if (self::$employee) {
$i = 0;
$handle = @fopen(self::$filename, "r");
if ($handle) {
$UTF8deleteBOM = true;
while (($subject = fgets($handle, 4096)) !== false) {
if ($UTF8deleteBOM) {
$subject = str_replace("", '', $subject);
$UTF8deleteBOM = false;
}
$transaction = Yii::$app->db->beginTransaction();
$i++;
try {
$pattern = '/^(.*?)\\|(Поликлиника №\\s?[1,2,3] )?(.*?)\\|(.*?)\\|(.*?)\\|(.*?)\\|(.*?)\\|(.*?)\\|(.*?)\\|(.*?)\\|(.*?)\\|(.*?)\\|(.*?)\\|(.*?)\\|(.*?)\\|(.*?)\\|/ui';
preg_match($pattern, $subject, $matches);
if ($matches[0] !== NULL) {
$pattern = '/(^Поликлиника №)\\s?([1,2,3])\\s?$/ui';
$matches[2] = preg_replace($pattern, 'Взрослая $1$2', mb_strtolower($matches[2], 'UTF-8'));
if ($matches[3] === 'Поликлиника профилактических осмотров') {
$matches[2] = $matches[3];
}
$pattern = '/^(.+) БУ "Нижневартовская городская поликлиника"$/ui';
$matches[3] = preg_replace($pattern, '$1', $matches[3]);
$employee_fio = $matches[1];
$location = self::AssignLocationForEmployeeImport(trim($matches[3]), trim($matches[2]));
$id_dolzh = self::AssignDolzh(trim($matches[4]));
// $sqlstr = empty($location->id_build) ? ' and id_build is null' : ' and id_build = :id_build';
$Employee = Employee::find()->joinWith('idperson')->where(array_merge(['id_dolzh' => $id_dolzh, 'id_podraz' => $location->id_podraz], empty($location->id_build) ? [] : ['id_build' => $location->id_build]))->andFilterWhere(['like', 'auth_user_fullname', $employee_fio, false])->one();
/* $Employee = self::GetRowsPDO('select employee_id, auth_user_fullname, id_dolzh, id_podraz, id_build from employee inner join auth_user on employee.id_person = auth_user.auth_user_id where auth_user_fullname like :employee_fio and id_dolzh = :id_dolzh and id_podraz = :id_podraz' . $sqlstr, array_merge([
'employee_fio' => $employee_fio,
'id_dolzh' => $id_dolzh,
'id_podraz' => $location->id_podraz,
], empty($location->id_build) ? [] : ['id_build' => $location->id_build])); */
if (empty($Employee)) {
/* var_dump('ok');
var_dump($Employee);
var_dump($employee_fio);
var_dump($location);
var_dump($id_dolzh); */
$AuthuserCount = Authuser::find()->where(['like', 'auth_user_fullname', $employee_fio, false])->count();
$Authuser = $AuthuserCount == 1 ? Authuser::find()->where(['like', 'auth_user_fullname', $employee_fio, false])->one() : false;
$newEmployee = false;
if (empty($Authuser) || $AuthuserCount > 1) {
$Authuser = new Authuser();
$Authuser->auth_user_fullname = $employee_fio;
$Authuser->auth_user_login = Proc::CreateLogin($employee_fio);
$Authuser->auth_user_password = Yii::$app->getSecurity()->generatePasswordHash('11111111');
$newEmployee = true;
}
$Employee = new Employee();
$Employee->attributes = ['id_dolzh' => $id_dolzh, 'id_podraz' => $location->id_podraz, 'id_build' => $location->id_build, 'employee_forinactive' => 1];
$Employeelog = new Employeelog();
$Employeelog->id_logreport = self::$logreport_id;
$Employeelog->employeelog_type = 1;
$Employeelog->employeelog_filename = self::$filename;
$Employeelog->employeelog_filelastdate = self::$filelastdate;
$Employeelog->employeelog_rownum = $i;
$Employeelog->employeelog_message = 'Запись добавлена.';
if (isset($Employee->scenarios()['import1c'])) {
$Employee->scenario = 'import1c';
}
if (isset($Authuser->scenarios()['import1c'])) {
$Authuser->scenario = 'import1c';
}
if ($Authuser->validate()) {
$Authuser->save(false);
$Profile = Profile::findOne($Authuser->primaryKey);
$Profile = empty($Profile) ? new Profile() : $Profile;
$Profile->profile_id = $Authuser->primaryKey;
$Profile->profile_dr = $matches[16];
$Profile->profile_pol = $matches[15];
$Profile->profile_inn = $matches[11];
$Profile->profile_snils = $matches[12];
$Profile->profile_address = $matches[10];
$Profile->save();
if ($Profile->getErrors()) {
var_dump($Profile->getErrors());
}
$Employeelog->employeelog_message = $Profile->getErrors() ? 'Запись добавлена. Ошибка при создании профиля' : 'Запись добавлена.';
$Employee->id_person = $Authuser->getPrimaryKey();
if ($Employee->validate()) {
$newEmployee ? self::$logreport_additions++ : self::$logreport_updates++;
$Employee->save(false);
if ($newEmployee && !self::$Debug) {
self::Mishanya($Authuser, $Employee, $matches);
}
} else {
$Employeelog->employeelog_type = 3;
$Employeelog->employeelog_message = 'Ошибка при добавлении записи: ';
foreach ($Employee->getErrors() as $fields) {
$Employeelog->employeelog_message .= implode(' ', $fields) . ' ';
}
self::$logreport_errors++;
}
} else {
$Employeelog->employeelog_type = 3;
$Employeelog->employeelog_message = 'Ошибка при добавлении записи: ';
foreach ($Authuser->getErrors() as $fields) {
$Employeelog->employeelog_message .= implode(' ', $fields) . ' ';
}
self::$logreport_errors++;
}
$Employeelog->employee_fio = $Authuser->auth_user_fullname;
$Employeelog->dolzh_name = Dolzh::findOne($Employee->id_dolzh)->dolzh_name;
//self::GetNameByID('dolzh', 'dolzh_name', $Employee->id_dolzh);
$Employeelog->podraz_name = Podraz::findOne($Employee->id_podraz)->podraz_name;
//self::GetNameByID('podraz', 'podraz_name', $Employee->id_podraz);
if (!empty($Employee->id_build)) {
$Employeelog->build_name = Build::findOne($Employee->id_build)->build_name;
}
//self::GetNameByID('build', 'build_name', $Employee->id_build);
$Employeelog->save(false);
} else {
if ($Employee->employee_importdo === 1) {
if (isset($Employee->scenarios()['import1c'])) {
$Employee->scenario = 'import1c';
}
$inactivePerson = Employee::find()->andWhere(['id_person' => $Employee->id_person, 'employee_dateinactive' => NULL])->count();
if (empty($inactivePerson)) {
$Employee = Employee::find(['id_person' => $Employee->id_person])->andWhere(['id_person' => $Employee->id_person])->orderBy(['employee_id' => SORT_DESC])->one();
$Employeelog = new Employeelog();
$Employeelog->id_logreport = self::$logreport_id;
$Employeelog->employeelog_type = 2;
$Employeelog->employeelog_filename = self::$filename;
$Employeelog->employeelog_filelastdate = self::$filelastdate;
$Employeelog->employeelog_rownum = $i;
$Employeelog->employeelog_message = 'Запись изменена. Очищена дата неактивности специальности "' . Yii::$app->formatter->asDate($Employee->employee_dateinactive) . '"';
$Employeelog->employee_fio = Authuser::findOne($Employee->id_person)->auth_user_fullname;
$Employeelog->dolzh_name = Dolzh::findOne($Employee->id_dolzh)->dolzh_name;
$Employeelog->podraz_name = Podraz::findOne($Employee->id_podraz)->podraz_name;
if (!empty($Employee->id_build)) {
$Employeelog->build_name = Build::findOne($Employee->id_build)->build_name;
}
$Employeelog->save(false);
$Employee->employee_dateinactive = null;
self::$logreport_updates++;
}
$Employee->employee_forinactive = 1;
$Employee->save(false);
}
}
} elseif (trim($subject) !== '') {
$Employeelog = new Employeelog();
$Employeelog->id_logreport = self::$logreport_id;
$Employeelog->employeelog_type = 3;
$Employeelog->employeelog_filename = self::$filename;
$Employeelog->employeelog_filelastdate = self::$filelastdate;
$Employeelog->employeelog_rownum = $i;
$Employeelog->employeelog_message = 'Ошибка при добавлении записи: Не пройдено регулярное выражение /^(.*?)\\|(Поликлиника №\\s?[1,2,3] )?(.*?)\\|(.*?)\\|(.*?)\\|(.*?)\\|(.*?)\\|(.*?)\\|(.*?)\\|(.*?)\\|(.*?)\\|(.*?)\\|(.*?)\\|(.*?)\\|(.*?)\\|(.*?)\\|/ui';
$Employeelog->save(false);
self::$logreport_errors++;
}
$transaction->commit();
} catch (Exception $e) {
$transaction->rollBack();
throw new Exception($e->getMessage() . ' $i = ' . $i . '; $filename = ' . self::$filename);
}
}
fclose($handle);
if (!self::$Debug) {
self::InactiveEmployee();
}
}
$logreport->logreport_amount += $i;
$logreport->logreport_employeelastdate = self::$filelastdate;
self::$employee = false;
} else {
if (self::IsFileType(self::mat) || self::IsFileType(self::gu)) {
Mattraffic::updateAll(['mattraffic_forimport' => NULL], ['mattraffic_forimport' => 1]);
}
$startRow = self::IsFileType(self::os) ? self::$os_start : (self::IsFileType(self::mat) ? self::$mat_start : self::$gu_start);
//начинаем читать с определенной строки
$exit = false;
//флаг выхода
$empty_value = 0;
//счетчик пустых знаений
// Загружаем данные из файла Excel
// $inputFileType = 'Excel5';
// $inputFileName = self::$filename;
$chunkSize = 1000;
//размер считываемых строк за раз
//
$objReader = \PHPExcel_IOFactory::createReaderForFile(self::$filename);
// $objReader = \PHPExcel_IOFactory::createReader($inputFileType);
$chunkFilter = new chunkReadFilter();
$objReader->setReadFilter($chunkFilter);
$objReader->setReadDataOnly(true);
while (!$exit) {
// Инициализируем переменные
// $row = $sheetData[self::$rownum_xls];
$chunkFilter->setRows($startRow, $chunkSize);
//устанавливаем знаечние фильтра
$objPHPExcel = $objReader->load(self::$filename);
//открываем файл
$objPHPExcel->setActiveSheetIndex(0);
//устанавливаем индекс активной страницы
$objWorksheet = $objPHPExcel->getActiveSheet();
//делаем активной нужную страницу
// Идем по данных excel
for ($i = $startRow; $i < $startRow + $chunkSize; $i++) {
//внутренний цикл по строкам
self::$rownum_xls = $i;
$value = trim(htmlspecialchars($objWorksheet->getCellByColumnAndRow(0, $i)->getValue()));
//получаем первое знаение в строке
if (empty($value)) {
//проверяем значение на пустоту
$empty_value++;
}
if ($empty_value == 1) {
//после трех пустых значений, завершаем обработку файла, думая, что это конец
$exit = true;
break;
}
/* Манипуляции с данными каким Вам угодно способом, в PHPExcel их превеликое множество */
$row = $objWorksheet->rangeToArray('A' . $i . ':M' . $i, null, true, true, true);
$row = $row[key($row)];
$material = new Material();
$authuser = new Authuser();
$employee = new Employee();
$mattraffic = new Mattraffic();
$matlog = new Matlog();
$employeelog = new Employeelog();
$traflog = new Traflog();
$MaterialDo = false;
$EmployeeDo = false;
$MattrafficDo = false;
// Начинаем транзакцию
$transaction = Yii::$app->db->beginTransaction();
try {
// Применяем значения атрубутов Материальной ценности
$MaterialDo = self::MaterialDo($material, $matlog, $row);
if ($MaterialDo) {
// Применяем значения атрубутов Сотрудника
$EmployeeDo = self::EmployeeDo($employee, $employeelog, $row);
if ($EmployeeDo) {
// Применяем значения атрубутов "Операции над материальной ценностью"
$MattrafficDo = self::MattrafficDo($mattraffic, $traflog, $row, $material, $employee->employee_id);
}
}
// $matlog->matlog_type !== 5 - Если Запись не изменилась не пишем в лог
if ($matlog->matlog_type !== 5 && ($MaterialDo || count($material->getErrors()) > 0)) {
$matlog->save(false);
if ($matlog->matlog_type === 2) {
$material->save(false);
}
}
if ($MaterialDo) {
// $employeelog->employeelog_type !== 5 - Если Запись не изменилась не пишем в лог
if ($employeelog->employeelog_type !== 5 && ($EmployeeDo || count($employee->getErrors()) > 0)) {
$employeelog->save(false);
}
if ($EmployeeDo) {
if ($MattrafficDo || count($mattraffic->getErrors()) > 0) {
if ($matlog->IsNewRecord) {
$matlog->material_number = $material->material_number;
// Иначе пишется предыдущее значение количества материальной ценности
$matlog->save(false);
}
if ($employeelog->IsNewRecord) {
$employeelog->save(false);
}
$traflog->id_matlog = $matlog->matlog_id;
$traflog->id_employeelog = $employeelog->employeelog_id;
if ($traflog->validate()) {
$traflog->save(false);
}
}
if ($MattrafficDo) {
$material->save(false);
$employee->save(false);
$mattraffic->id_material = $material->material_id;
$mattraffic->id_mol = $employee->employee_id;
$mattraffic->save(false);
// Применяем значения атрубутов, если материальная ценность списна
self::WriteOffDo($material, $matlog, $mattraffic, $traflog, $row);
}
}
}
// if ($transaction->isActive)
$transaction->commit();
} catch (Exception $e) {
$transaction->rollBack();
throw new Exception($e->getMessage() . ' $rownum_xls = ' . self::$rownum_xls . '; $filename = ' . self::$filename);
}
}
$objPHPExcel->disconnectWorksheets();
//чистим
unset($objPHPExcel);
//память
unset($material);
unset($employee);
unset($mattraffic);
unset($matlog);
unset($employeelog);
unset($traflog);
unset($objWorksheet);
echo '<BR>Память использована с ' . $startRow . ' по ' . ($startRow + $chunkSize) . ' : ' . Yii::$app->formatter->asShortSize(memory_get_usage(true));
$startRow += $chunkSize;
//переходим на следующий шаг цикла, увеличивая строку, с которой будем читать файл
}
$logreport->logreport_amount += self::$rownum_xls - (self::IsFileType(self::os) ? self::$os_start : (self::IsFileType(self::mat) ? self::$mat_start : self::$gu_start));
if (self::IsFileType(self::os)) {
$logreport->logreport_oslastdate = self::$filelastdate;
} elseif (self::IsFileType(self::mat)) {
$logreport->logreport_matlastdate = self::$filelastdate;
} else {
$logreport->logreport_gulastdate = self::$filelastdate;
}
if (self::IsFileType(self::mat) || self::IsFileType(self::gu)) {
self::MaterialSpisanie();
}
}
$logreport->logreport_additions += self::$logreport_additions;
$logreport->logreport_updates += self::$logreport_updates;
$logreport->logreport_errors += self::$logreport_errors;
$logreport->logreport_missed += self::$logreport_missed;
$logreport->save();
} else {
if (self::IsFileType(self::os)) {
$logreport->logreport_oslastdate = self::$filelastdate;
} elseif (self::IsFileType(self::mat)) {
$logreport->logreport_matlastdate = self::$filelastdate;
} else {
$logreport->logreport_gulastdate = self::$filelastdate;
}
if ($filename === $Importconfig['emp_filename'] . '.txt') {
$logreport->logreport_employeelastdate = self::$filelastdate;
}
}
}
}
if ($doreport) {
self::MakeReport();
$endtime = microtime(true);
$logreport->logreport_executetime = gmdate('H:i:s', $endtime - $starttime);
$logreport->logreport_memoryused = memory_get_usage(true);
$logreport->save();
}
$endtime = microtime(true);
echo 'ImportDo success<BR>';
echo 'Использовано памяти: ' . Yii::$app->formatter->asShortSize(memory_get_usage(true)) . '; Время выполнения: ' . gmdate('H:i:s', $endtime - $starttime);
} else {
echo 'Импорт отключен';
}
}
public static function VariablesValues($attribute, $value = NULL)
{
$values = ['mol_id_build' => [$value => Build::getBuildByID($value)], 'mol_fullname_material' => [$value => Authuser::getAuthuserByID($value)], 'material_writeoff' => Material::VariablesValues($attribute), 'mat_id_grupa' => [$value => Grupa::getGrupaByID($value)]];
return isset($values[$attribute]) ? $values[$attribute] : NULL;
}
protected function Body()
{
$ID = $this->getDopparamID();
$this->setReportName('Акт получения материалов у сторонней организации №' . $ID);
$Recoverysendakt = Recoverysendakt::findOne($ID);
$Recoveryrecieveaktmat_ok = Recoveryrecieveaktmat::find()->andWhere(['id_recoverysendakt' => $ID, 'recoveryrecieveaktmat_repaired' => 2])->all();
$Recoveryrecieveaktmat_fail = Recoveryrecieveaktmat::find()->andWhere(['and', ['id_recoverysendakt' => $ID], ['or', ['recoveryrecieveaktmat_repaired' => 1], ['recoveryrecieveaktmat_repaired' => NULL]]])->all();
$Mols = Recoveryrecieveaktmat::getMolsByRecoverysendakt($ID);
$objPHPExcel = $this->getObjPHPExcel();
$objPHPExcel->getActiveSheet()->setCellValue('F4', $Recoverysendakt->idOrgan->organ_name);
$objPHPExcel->getActiveSheet()->setCellValue('F5', $Recoverysendakt->recoverysendakt_id . ' от ' . Yii::$app->formatter->asDate($Recoverysendakt->recoverysendakt_date));
$objPHPExcel->getActiveSheet()->setCellValue('F6', Yii::$app->formatter->asDate(date('Y-m-d')));
$material_tip = Material::VariablesValues('material_tip');
$num = 11;
foreach ($Recoveryrecieveaktmat_ok as $ar) {
$objPHPExcel->getActiveSheet()->insertNewRowBefore($num);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $num, $num - 10);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, $num, $ar->idTrMatOsmotr->idTrMat->idMattraffic->idMaterial->idMatv->matvid_name);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, $num, $ar->idTrMatOsmotr->idTrMat->idMattraffic->idMaterial->material_name);
$objPHPExcel->getActiveSheet()->setCellValueExplicitByColumnAndRow(3, $num, $ar->idTrMatOsmotr->idTrMat->idMattraffic->idMaterial->material_inv, \PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(4, $num, $material_tip[$ar->idTrMatOsmotr->idTrMat->idMattraffic->idMaterial->material_tip]);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(5, $num, $ar->idTrMatOsmotr->tr_mat_osmotr_number);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(6, $num, $ar->idTrMatOsmotr->idTrMat->idMattraffic->idMaterial->idIzmer->izmer_name);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(7, $num, $ar->recoveryrecieveaktmat_result);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(8, $num, $ar->idTrMatOsmotr->idTrMat->idMattraffic->idMol->idperson->auth_user_fullname . ', ' . $ar->idTrMatOsmotr->idTrMat->idMattraffic->idMol->iddolzh->dolzh_name);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(9, $num, TrMatOsmotr::getBuildandKabByTrMatOsmotr($ar->id_tr_mat_osmotr));
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(10, $num, Yii::$app->formatter->asDate($ar->recoveryrecieveaktmat_date));
$objPHPExcel->getActiveSheet()->getStyle('A' . $num . ':K' . $num)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
$num++;
}
$objPHPExcel->getActiveSheet()->removeRow($num);
$crows = count($Recoveryrecieveaktmat_ok);
$num = 15;
foreach ($Recoveryrecieveaktmat_fail as $ar) {
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $num + $crows, $num + $crows - 15);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, $num + $crows, $ar->idTrMatOsmotr->idTrMat->idMattraffic->idMaterial->idMatv->matvid_name);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, $num + $crows, $ar->idTrMatOsmotr->idTrMat->idMattraffic->idMaterial->material_name);
$objPHPExcel->getActiveSheet()->setCellValueExplicitByColumnAndRow(3, $num + $crows, $ar->idTrMatOsmotr->idTrMat->idMattraffic->idMaterial->material_inv, \PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(4, $num + $crows, $material_tip[$ar->idTrMatOsmotr->idTrMat->idMattraffic->idMaterial->material_tip]);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(5, $num + $crows, $ar->idTrMatOsmotr->tr_mat_osmotr_number);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(6, $num + $crows, $ar->idTrMatOsmotr->idTrMat->idMattraffic->idMaterial->idIzmer->izmer_name);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(7, $num + $crows, $ar->recoveryrecieveaktmat_result);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(8, $num + $crows, $ar->idTrMatOsmotr->idTrMat->idMattraffic->idMol->idperson->auth_user_fullname . ', ' . $ar->idTrMatOsmotr->idTrMat->idMattraffic->idMol->iddolzh->dolzh_name);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(9, $num + $crows, TrMatOsmotr::getBuildandKabByTrMatOsmotr($ar->id_tr_mat_osmotr));
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(10, $num + $crows, Yii::$app->formatter->asDate($ar->recoveryrecieveaktmat_date));
$objPHPExcel->getActiveSheet()->getStyle('A' . ($num + $crows) . ':K' . ($num + $crows))->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
$objPHPExcel->getActiveSheet()->insertNewRowBefore($num + $crows + 1);
$num++;
}
$objPHPExcel->getActiveSheet()->removeRow($num + $crows);
$crows = count($Recoveryrecieveaktmat_ok) + count($Recoveryrecieveaktmat_fail);
$num = 17;
foreach ($Mols as $ar) {
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $num + $crows, 'Материально ответственное лицо');
$objPHPExcel->getActiveSheet()->mergeCellsByColumnAndRow(0, $num + $crows, 1, $num + $crows);
$objPHPExcel->getActiveSheet()->mergeCellsByColumnAndRow(3, $num + $crows, 6, $num + $crows);
$objPHPExcel->getActiveSheet()->mergeCellsByColumnAndRow(7, $num + $crows, 10, $num + $crows);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(3, $num + $crows, $ar['dolzh_name']);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(7, $num + $crows, $ar['auth_user_fullname']);
$objPHPExcel->getActiveSheet()->insertNewRowBefore($num + $crows + 1);
$num++;
}
$objPHPExcel->getActiveSheet()->removeRow($num + $crows);
}
/**
* @return \yii\db\ActiveQuery
*/
public function getMaterials()
{
return $this->hasMany(Material::className(), ['id_schetuchet' => 'schetuchet_id'])->from(['spisosnovakts' => Material::tableName()]);
}
public function searchforinstallakt_mat($params)
{
$query = Material::find();
$dataProvider = new ActiveDataProvider(['query' => $query, 'sort' => ['defaultOrder' => ['material_name' => SORT_ASC]]]);
$query->joinWith(['idMatv', 'idIzmer']);
$this->load($params);
if (!$this->validate()) {
// uncomment the following line if you do not want to return any records when validation fails
// $query->where('0=1');
return $dataProvider;
}
$query->andFilterWhere(['material_id' => $this->material_id, 'material_tip' => $this->material_tip, 'material_writeoff' => $this->material_writeoff, 'id_matvid' => $this->id_matvid, 'id_izmer' => $this->id_izmer, 'material_importdo' => $this->material_importdo]);
$query->andFilterWhere(['like', 'material_name', $this->material_name])->andFilterWhere(['like', 'material_name1c', $this->material_name1c])->andFilterWhere(['like', 'material_1c', $this->material_1c])->andFilterWhere(['like', 'material_inv', $this->material_inv])->andFilterWhere(['like', 'material_serial', $this->material_serial]);
$query->andFilterWhere(Proc::WhereConstruct($this, 'material_release', Proc::Date));
// $query->andFilterWhere(Proc::WhereConstruct($this, 'material_number'));
$query->andWhere(['material_number' => 1]);
$query->andFilterWhere(Proc::WhereConstruct($this, 'material_price'));
$query->andFilterWhere(['LIKE', 'idMatv.matvid_name', $this->getAttribute('idMatv.matvid_name')]);
$query->andFilterWhere(['LIKE', 'idIzmer.izmer_name', $this->getAttribute('idIzmer.izmer_name')]);
$query->andFilterWhere(Proc::WhereConstruct($this, 'material_username'));
$query->andFilterWhere(Proc::WhereConstruct($this, 'material_lastchange', Proc::DateTime));
$query->andFilterWhere(Proc::WhereConstruct($this, 'material_number'));
Proc::AssignRelatedAttributes($dataProvider, ['idMatv.matvid_name', 'idIzmer.izmer_name']);
return $dataProvider;
}
public function actionCreate()
{
$model = new TrOsnov();
$Mattraffic = new Mattraffic();
$Material = new Material();
$Employee = new Employee();
$mattraffic_number_max = NULL;
// Если форма отправлена на сервер, получаем выбранную материальную ценность
$id_mattraffic = isset(Yii::$app->request->post('TrOsnov')['id_mattraffic']) ? Yii::$app->request->post('TrOsnov')['id_mattraffic'] : '';
$transaction = Yii::$app->db->beginTransaction();
try {
// Если форма отправлена на сервер, то создать запись перемещения мат цен-ти в mattraffic
if (!empty($id_mattraffic)) {
$Mattrafficcurrent = Mattraffic::findOne($id_mattraffic);
$Mattraffic->attributes = $Mattrafficcurrent->attributes;
$Mattraffic->mattraffic_date = date('Y-m-d');
$Mattraffic->mattraffic_number = isset(Yii::$app->request->post('Mattraffic')['mattraffic_number']) ? Yii::$app->request->post('Mattraffic')['mattraffic_number'] : NULL;
$Mattraffic->mattraffic_tip = 3;
if (isset($Mattraffic->scenarios()['traffic'])) {
$Mattraffic->scenario = 'traffic';
}
if ($Mattraffic->validate()) {
$Mattraffic->save(false);
$model->id_mattraffic = $Mattraffic->mattraffic_id;
}
//Акт установки уже создан и берется из URL параметра
$model->id_installakt = (string) filter_input(INPUT_GET, 'idinstallakt');
//Сохраняем кабинет в модель из отправленной формы
$model->tr_osnov_kab = isset(Yii::$app->request->post('TrOsnov')['tr_osnov_kab']) ? Yii::$app->request->post('TrOsnov')['tr_osnov_kab'] : NULL;
}
// Сохраняем модель с отправленными данными и сохраненным mattraffic
if (!$Mattraffic->isNewRecord && $model->save()) {
$transaction->commit();
return $this->redirect(Proc::GetPreviousURLBreadcrumbsFromSession());
} else {
// иначе
// Берет значение сначала из справочника (посредством перехода на страницу выбора), если нет, то вытаскивает из сессии (для простого обновления страницы)
// выводит $PreviusBC - для последующей передачи в функцию Proc::SetSessionValuesFromAR, т.е. установить в последнюю сессию или предыдущую (хлебных крошек)
$PreviusBC = Proc::GetValueForFillARs($id_mattraffic, 'TrOsnov', 'id_mattraffic');
// Очистить ошибку id_mattraffic, если есть ошибка по mattraffic_number (Превышено допустимое кол-во для перемещения матер. цен-ти)
//if (isset($Mattraffic->errors['mattraffic_number']))
// $model->clearErrors('id_mattraffic');
// Если выбрана мат. цен-ть, то заполнить информацию для отображения на форме по мат. цен-ти и МОЛ
if (!empty($id_mattraffic)) {
$Material = Material::find()->joinWith('mattraffics')->where(['mattraffic_id' => $id_mattraffic])->one();
$Employee = Employee::find()->joinWith('mattraffics')->where(['mattraffic_id' => $id_mattraffic])->one();
// GetMaxNumberMattrafficForInstallAkt - Определяем максимально допустимое кол-во материала для перемещения (Общее кол-во материала минус уже перемещенное кол-во)
$mattraffic_number_max = 'Не более ' . doubleval(Mattraffic::GetMaxNumberMattrafficForInstallAkt($id_mattraffic));
// Сохраняем модель мат. цен-ти и МОЛ'а в сессию (т.к. эти модели только для отображения)
Proc::SetSessionValuesFromAR($Material, $PreviusBC);
Proc::SetSessionValuesFromAR($Employee, $PreviusBC);
}
// Откатываем транзакцию
$transaction->rollBack();
return $this->render('create', ['model' => $model, 'Mattraffic' => $Mattraffic, 'Material' => $Material, 'Employee' => $Employee, 'mattraffic_number_max' => $mattraffic_number_max]);
}
} catch (Exception $e) {
$transaction->rollBack();
throw new Exception($e->getMessage());
}
}
protected function Body()
{
$ID = $this->getDopparamID();
$this->setReportName('Акт перемещения матер-ых цен-тей №' . $ID);
$Installakt = Installakt::findOne($ID);
$Trosnov = TrOsnov::findAll(['id_installakt' => $ID]);
$Trmat = TrMat::find()->andWhere(['id_installakt' => $ID])->GroupBy('id_parent')->all();
$objPHPExcel = $this->getObjPHPExcel();
$objPHPExcel->getActiveSheet()->setCellValue('A3', 'материальных ценностей № ' . $Installakt->installakt_id . ' от ' . Yii::$app->formatter->asDate($Installakt->installakt_date));
$num = 5;
$c_Trosnov = count($Trosnov);
if ($c_Trosnov > 0) {
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $num, 'Перемещение материальных ценностей');
$objPHPExcel->getActiveSheet()->mergeCellsByColumnAndRow(0, $num, 10, $num);
$this->setStyle(self::$TITLE, 'A' . $num . ':K' . $num);
$num++;
$this->SetTitlebyArray(['№', 'Вид', 'Наименование', 'Инвентарный номер', 'Серийный номер', 'Кол-во', 'Единица измерения', 'Лицо отправитель', 'Здание, кабинет, откуда перемещено', 'Лицо получатель', 'Здание, кабинет, куда перемещено'], $num);
$this->setStyle(self::$CAPTION, 'A' . $num . ':K' . $num);
$this->CellsWrapAndTop('A' . $num . ':K' . $num);
$num++;
$this->setColumnNumbers($num, 11);
$this->setStyle(self::$NUMS, 'A' . $num . ':K' . $num);
$this->CellsWrapAndTop('A' . $num . ':K' . $num);
$num++;
$startrow = $num;
foreach ($Trosnov as $ar) {
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $num, $num - $startrow + 1);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, $num, $ar->idMattraffic->idMaterial->idMatv->matvid_name);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, $num, $ar->idMattraffic->idMaterial->material_name);
$objPHPExcel->getActiveSheet()->setCellValueExplicitByColumnAndRow(3, $num, $ar->idMattraffic->idMaterial->material_inv, \PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(4, $num, $ar->idMattraffic->idMaterial->material_serial);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(5, $num, $ar->idMattraffic->mattraffic_number);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(6, $num, $ar->idMattraffic->idMaterial->idIzmer->izmer_name);
$mattraffic_previous = Mattraffic::GetPreviousMattrafficByInstallaktMaterial($ID, $ar->idMattraffic->id_material);
if (!empty($mattraffic_previous)) {
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(7, $num, $mattraffic_previous->idMol->idperson->auth_user_fullname . ', ' . $mattraffic_previous->idMol->iddolzh->dolzh_name);
if ($mattraffic_previous->mattraffic_tip == 1 && empty($mattraffic_previous->trOsnovs[0]->tr_osnov_kab)) {
$kab = ', Приход';
} elseif ($mattraffic_previous->mattraffic_tip == 2 && empty($mattraffic_previous->trOsnovs[0]->tr_osnov_kab)) {
$kab = ', Списание';
} else {
$kab = ', ' . $mattraffic_previous->trOsnovs[0]->tr_osnov_kab;
}
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(8, $num, $mattraffic_previous->idMol->idbuild->build_name . $kab);
}
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(9, $num, $ar->idMattraffic->idMol->idperson->auth_user_fullname . ', ' . $ar->idMattraffic->idMol->iddolzh->dolzh_name);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(10, $num, $ar->idMattraffic->idMol->idbuild->build_name . ', ' . $ar->tr_osnov_kab);
$num++;
}
if (count($Trosnov) > 0) {
$this->setStyle(self::$DATA, 'A' . $startrow . ':K' . ($num - 1));
}
$this->CellsWrapAndTop('A' . $startrow . ':K' . $num);
$num++;
}
$c_Trmat = count($Trmat);
if ($c_Trmat > 0) {
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $num, 'Установка комплектующих');
$objPHPExcel->getActiveSheet()->mergeCellsByColumnAndRow(0, $num, 10, $num);
$this->setStyle(self::$TITLE, 'A' . $num . ':K' . $num);
$num++;
foreach ($Trmat as $arm) {
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $num, 'Материальная ценность');
$objPHPExcel->getActiveSheet()->mergeCellsByColumnAndRow(0, $num, 10, $num);
$this->setStyle(self::$TITLELEFT, 'A' . $num . ':K' . $num);
$num++;
$this->SetTitlebyArray(['№', 'Вид', 'Наименование', 'Инвентарный номер', 'Серийный номер', 'Год выпуска', 'Стоимость', 'Здание', 'Кабинет', 'Материально-ответственное лицо', 'Тип'], $num);
$this->setStyle(self::$CAPTION, 'A' . $num . ':K' . $num);
$this->CellsWrapAndTop('A' . $num . ':K' . $num);
$num++;
$this->setColumnNumbers($num, 11);
$this->setStyle(self::$NUMS, 'A' . $num . ':K' . $num);
$this->CellsWrapAndTop('A' . $num . ':K' . $num);
$num++;
$material_tip = Material::VariablesValues('material_tip');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $num, 1);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, $num, $arm->idParent->idMaterial->idMatv->matvid_name);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, $num, $arm->idParent->idMaterial->material_name);
$objPHPExcel->getActiveSheet()->setCellValueExplicitByColumnAndRow(3, $num, $arm->idParent->idMaterial->material_inv, \PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(4, $num, $arm->idParent->idMaterial->material_serial);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(5, $num, Yii::$app->formatter->asDate($arm->idParent->idMaterial->material_release, 'y'));
$objPHPExcel->getActiveSheet()->setCellValueExplicitByColumnAndRow(6, $num, $arm->idParent->idMaterial->material_price, \PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(7, $num, $arm->idParent->idMol->idbuild->build_name);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(8, $num, $arm->idParent->trOsnovs[0]->tr_osnov_kab);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(9, $num, $arm->idParent->idMol->idperson->auth_user_fullname . ', ' . $arm->idParent->idMol->iddolzh->dolzh_name);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(10, $num, $material_tip[$arm->idParent->idMaterial->material_tip]);
$this->setStyle(self::$DATA, 'A' . $num . ':K' . $num);
$this->CellsWrapAndTop('A' . $num . ':K' . $num);
$num++;
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $num, 'Установленные комплектующие');
$objPHPExcel->getActiveSheet()->mergeCellsByColumnAndRow(0, $num, 10, $num);
$this->setStyle(self::$TITLELEFT, 'A' . $num . ':K' . $num);
$num++;
$this->SetTitlebyArray(['№', 'Вид', 'Наименование', 'Инвентарный номер', 'Серийный номер', 'Кол-во', 'Единица измерения', 'Год выпуска', 'Стоимость', 'Материально-ответственное лицо', 'Тип'], $num);
$this->setStyle(self::$CAPTION, 'A' . $num . ':K' . $num);
$this->CellsWrapAndTop('A' . $num . ':K' . $num);
$num++;
$this->setColumnNumbers($num, 11);
$this->setStyle(self::$NUMS, 'A' . $num . ':K' . $num);
$this->CellsWrapAndTop('A' . $num . ':K' . $num);
$num++;
$MatbyParent = TrMat::find()->andWhere(['id_parent' => $arm->idParent->primaryKey, 'id_installakt' => $Installakt->installakt_id])->all();
$startrow = $num;
foreach ($MatbyParent as $ar) {
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $num, $num - $startrow + 1);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, $num, $ar->idMattraffic->idMaterial->idMatv->matvid_name);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, $num, $ar->idMattraffic->idMaterial->material_name);
$objPHPExcel->getActiveSheet()->setCellValueExplicitByColumnAndRow(3, $num, $ar->idMattraffic->idMaterial->material_inv, \PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(4, $num, $ar->idMattraffic->idMaterial->material_serial);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(5, $num, $ar->idMattraffic->mattraffic_number);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(6, $num, $ar->idMattraffic->idMaterial->idIzmer->izmer_name);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(7, $num, Yii::$app->formatter->asDate($ar->idMattraffic->idMaterial->material_release, 'y'));
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(8, $num, $ar->idMattraffic->idMaterial->material_price);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(9, $num, $ar->idMattraffic->idMol->idperson->auth_user_fullname . ', ' . $ar->idMattraffic->idMol->iddolzh->dolzh_name);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(10, $num, $material_tip[$ar->idMattraffic->idMaterial->material_tip]);
$num++;
}
if (count($MatbyParent) > 0) {
$this->setStyle(self::$DATA, 'A' . $startrow . ':K' . ($num - 1));
}
$this->CellsWrapAndTop('A' . $startrow . ':K' . $num);
$num++;
}
}
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, $num, '(Подпись)');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(3, $num, '(Должность)');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(7, $num, '(Ф.И.О.)');
$objPHPExcel->getActiveSheet()->mergeCellsByColumnAndRow(3, $num, 6, $num);
$objPHPExcel->getActiveSheet()->mergeCellsByColumnAndRow(7, $num, 10, $num);
$this->setStyle(self::$SIGN, 'A' . $num . ':K' . $num);
$num++;
$Mols = Installakt::getMolsByInstallakt($Installakt->installakt_id);
$startrow = $num;
foreach ($Mols as $ar) {
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $num, 'Материально ответственное лицо');
$objPHPExcel->getActiveSheet()->mergeCellsByColumnAndRow(0, $num, 1, $num);
$objPHPExcel->getActiveSheet()->mergeCellsByColumnAndRow(3, $num, 6, $num);
$objPHPExcel->getActiveSheet()->mergeCellsByColumnAndRow(7, $num, 10, $num);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(3, $num, $ar->dolzh_name_tmp);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(7, $num, $ar->auth_user_fullname_tmp);
$this->CellsWrapAndTop('A' . $num . ':K' . $num);
$objPHPExcel->getActiveSheet()->getRowDimension($num)->setRowHeight(45.75);
$num++;
}
$this->setStyle(self::$TITLELEFT, 'A' . $startrow . ':B' . ($num - 1));
$this->setStyle(self::$SIGNDATA, 'C' . $startrow . ':K' . ($num - 1));
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $num, 'Мастер');
$this->setStyle(self::$TITLELEFT, 'A' . $num . ':B' . $num);
$objPHPExcel->getActiveSheet()->mergeCellsByColumnAndRow(0, $num, 1, $num);
$this->setStyle(self::$SIGNDATA, 'C' . $num . ':K' . $num);
$Master = Employee::findOne($Installakt->id_installer);
$objPHPExcel->getActiveSheet()->mergeCellsByColumnAndRow(3, $num, 6, $num);
$objPHPExcel->getActiveSheet()->mergeCellsByColumnAndRow(7, $num, 10, $num);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(3, $num, $Master->iddolzh->dolzh_name);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(7, $num, $Master->idperson->auth_user_fullname);
$this->CellsWrapAndTop('A' . $num . ':K' . $num);
$objPHPExcel->getActiveSheet()->getRowDimension($num)->setRowHeight(45.75);
}
/**
* Finds the Material model based on its primary key value.
* If the model is not found, a 404 HTTP exception will be thrown.
* @param string $id
* @return Material the loaded model
* @throws NotFoundHttpException if the model cannot be found
*/
protected function findModel($id)
{
if (($model = Material::findOne($id)) !== null) {
return $model;
} else {
throw new NotFoundHttpException('The requested page does not exist.');
}
}
/**
* @return \yii\db\ActiveQuery
*/
public function getIdMaterial()
{
return $this->hasOne(Material::className(), ['material_id' => 'id_material'])->from(['idMaterial' => Material::tableName()]);
}